How to chain filters / filtering two object lists in Django?

Question

Need some help in writing a filter query.

I have the following Models:

class Product:
    pid
    name

class WishList:
    user
    items = models.ManyToManyFields(Product)

class Stock:
    storeid
    product = models.ForeignKey(Product)
    aisle
    segment

I need to :

1. Get the Current Logged in USER

2. Get the Products in the User's WishList

3. Search for those Products in the 'Stock' for a Particular StoreID passed as argument and display them along with their Aisle

This is what I did:

user = request.user
wish_list = get_object_or_404(WishList, user=user)

list_items = wish_list.items.all()
store_stocks = Stock.objects.filter(storeid=(passed StoreID))
list_stocks store_stocks.filter(store_stocks.pid=list_items.pid)

But it doesn't work and I need some help.

I get the Error as:

list_stocks = store_stocks.filter(store_stocks.pid=list_items.pid)
                                 |
SyntaxError: keyword can't be an expression

Answer 1

You can not write:

filter(store_stocks.pid=list_items.pid)

since the parameter name store_stocks.pid is invalid.

We can do this in a single query with:

from django.db.models import F

products = Product.objects.filter(
    wishlist__user=request.user,
    stock__storeid=store_id
).annotate(
    aisle=F('stock__aisle')
)

This will result in a queryset that contains Products with an extra attribute aisle that is the aisle of the Stock for the given store_id. This will thus make it more convenient (likely), to render the Products along with the corresponding aisle.

Note that a user can have multiple wishlists, and thus in that case we thus get all the products of all the wishlists. In case an item occurs on multiple wishlists, it will be listed multiple times.

This query will thus make two JOINs: one with the wishlist table, and one with the stock table.

Answer 2

You can achieve this by:

list_items = wish_list.items.all()
list_stocks = Stock.objects.filter(storeid=(passed StoreID), product__in=list_items)