时间戳

首页 > 解决方案 > Django 搜索：查询多个表，加入它们然后返回查询集

python - Django 搜索：查询多个表，加入它们然后返回查询集

问题描述

我正在搜索我的产品价格跟踪应用程序，并且有 2 种型号的产品和扫描

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)

搜索应该采用名称（字符串）输入并查询同一个表中的商店名称，并查询另一个表中的价格，并返回带有产品名称、商店名称和价格的查询集。

我试过这个

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))

代码在

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

和scans matching query does not exist.

标签： pythondjangodjango-models

解决方案

就命名、编码和 Django 约定而言，您的模型确实没有正确定义。您应该将它们更改为：

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)

    # ...

然后，当您有一个产品查询集时：

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

您可以遍历它们并检索相应的扫描：

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!

更好的是，您可以在一个查询中完成所有这些操作：

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

推荐阅读