Django search : query multiple table, join them then return the queryset

Question

I'm building a search for my product price tracking app and there're 2 models products and scans

class products(models.Model):
    enter code hereproduct_id = models.IntegerField(primary_key=True)
    product_name = models.CharField(max_length=100)
    product_link = models.CharField(max_length=255)
    image_link = models.CharField(max_length=100)
    store_name = models.CharField(max_length=100)

    class Meta:
        verbose_name_plural = "products"

    def __str__(self):
        return str(self.product_name)

class scans(models.Model):
    scan_id = models.IntegerField(primary_key=True)
    prod_id = models.IntegerField(models.ForeignKey("scrape.Model", on_delete=models.CASCADE))
    price = models.IntegerField()
    scanned_time = models.DateTimeField(default=timezone.now)

    class Meta:
      verbose_name_plural = "scans"

    def __repr__(self):
        return str(self.prod_id)

the search is supposed to take a name(string) input and query for a store name in the same table and query for price which is in another table and return the query set with the product_name, store_name and price.

I've tried this

class searchResults(ListView):
model = 'products', 'scans'
template_name = 'result.html'

# let query = GET request from form (in home.html)
def get_queryset(self):
    queryset = []
    rawquery = self.request.GET.get('q')
    queries = rawquery.split(" ")
    for q in queries:
        prodresults = products.objects.filter(
            Q(product_name__icontains=q)
        ).distinct()

        for prodresult in prodresults:
            prod_id = products.objects.get(product_name=prodresult).product_id
            prod_price = scans.objects.get(prod_id=prod_id).price

        for result in prodresults:
            #joins results toegther
            queryset.append(result)
            queryset.append(prod_price)

    return list(set(queryset))

The code broke at

prod_price = scans.objects.get(prod_id=prod_id).price

with scans matching query does not exist.

Answer 1

Your models are really not correctly defined, in terms of naming, coding and Django conventions. You should change them to this:

class Product(models.Model):  # Capital P, singular
    id = models.IntegerField(primary_key=True)  # id, not product_id
    name = models.CharField(max_length=100)  # name, not product_name
    link = models.CharField(max_length=255)
    image_link = models.CharField(max_length=100)
    store_name = models.CharField(max_length=100)

    # ...

class Scan(models.Model):
    id = models.IntegerField(primary_key=True)
    product = models.ForeignKey("Product", on_delete=models.CASCADE, related_name="scans")  # FK to Product
    price = models.IntegerField()
    scanned_time = models.DateTimeField(default=timezone.now)

    # ...

Then when you have a queryset of products:

results = Product.objects.filter(product_name__icontains=q).distinct()

you can loop through them and retrieve the corresponding scans:

for product in results:
    scans = Scan.objects.filter(product=product)  # You can have more than one Scan for the same product, since this is a one-to-many relationship!

Better, you can do all of this in one query:

scans = Scan.objects.filter(product__in=Product.objects.filter(
    product_name__icontains=q))