Include Reverse Foreign Key of Foreign Key in Query

Using Django Using the ORM
1

Here are the models for reference:

class Listing(models.Model):
    lister = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.SET(get_sentinel_user), related_name="listing")
    title = models.CharField(max_length=80)
    description = models.CharField(max_length=255)
    image = models.ImageField(upload_to=user_directory_path, null=True, blank=True)
    created = models.DateTimeField(auto_now_add=True)
    class Category(models.TextChoices):
        ANTIQUE = "ANT", _("Antiques")
        BOOK = "BOO", _("Books")
        CLOTH = "CLO", _("Clothing, Shoes & Accessories")
        COLLECT = "COL", _("Collectibles")
        CRAFT = "CRA", _("Crafts")
        ELECTRONIC = "ELE", _("Consumer Electronics")
        INDUSTRIAL = "IND", _("Business & Industrial")
        MOTOR = "MOT", _("Motors")
        PET = "PET", _("Pet Supplies")
        SPORT = "SPO", _("Sporting Goods")
        TOY = "TOY", _("Toys & Hobbies")

    category = models.CharField(max_length=3, choices=Category.choices)
    active = models.BooleanField(default=True)
    winner = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.SET(get_sentinel_user), related_name="won",
                               null=True,
                               default=None)
    def __str__(self):
        return f"{self.id}:{self.title}"

class Bid(models.Model):
    bidder = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.SET(get_sentinel_user), related_name="bid")
    listing = models.ForeignKey(Listing, on_delete=models.CASCADE, related_name="bid")
    bid_price = models.DecimalField(max_digits=11, decimal_places=2)
    start_bid = models.BooleanField(default=False)
    current_bid = models.BooleanField(default=True)

    def __str__(self):
        return f"{self.listing}: {self.bid_price}"

class Watchlist(models.Model):
    user = models.ForeignKey(settings.AUTH_USER_MODEL, on_delete=models.CASCADE, related_name="watching")
    listing = models.ForeignKey(Listing, on_delete=models.CASCADE, related_name="watchers")

I managed to cobble together a solution using a loop in the view:

@login_required
def watchlist(request):
    watch_list = Watchlist.objects.filter(user=request.user)
    for i in watch_list:
        i.current_price = Bid.objects.get(listing=i.listing, current_bid=True).bid_price
    return render(request, "auctions/watchlist.html", {
        "listings" : watch_list
    })

How would i achieve the same output using only queries without iterating over the QuerySet ? I tried using annotate and prefetch_related but didn’t manage to find a solution.

Essentially i want to get all watchlist objects for the current user, then i want to get data from a model two relations away:
Watchlist > Listing (FK) > Bid (Reverse FK of Listing)

the query i ultimately want to add to each item in this queryset (Watchlist.objects.filter(user=request.user)) is for each listing get the bid_price WHERE current_bid=True

Thanks.

2

Just winging this, my first attempt would be along the lines of:

Watchlist.objects.filter(
    user=request.user,
    listing__bid__current_bid=True
)

The issue here, however, is that your models have nothing structurally defined to define that there’s only one “current_bid” for a listing. This means that the expression watchlist.listing.bid (for an instance of Watchlist named watchlist) will return a list and not a single element, even if there’s only one element in the list.

1 Like
3

Any suggestions on how to structurally define one “current_bid” for a listing by changing the models?

would a one to one relationship work?

4

It seems to me that there could be a link in the other direction. (A one-to-one relationship defined in Listing and referencing Bid.)

Now, ordinarily, this wouldn’t be something I recommend - it does create the possibility of a mismatch - having that link referencing a Bid that isn’t a bid for that item, but I think in this case it’s an issue that can be appropriately managed.

1 Like