Django - Query values based on the result of an existing query

Question

I have a query that matches some foreign key properties buy_book__entity and sell_book__entity to a parameter company_group. I need to further add onto this query to filter the results only on the results that match a predicate. Is this possible to do in one query?

return (Q(buy_book__entity__company_groups__alias__iexact=company_group) & \ 
        Q(sell_book__entity__company_groups__alias__iexact=company_group)) & \ 
        # I only want to run the below query on objects which 
        # the book entity types are internal from the above queries output
        (Q(buy_book__entity__primary_company_group__alias__iexact=Case(
            When(buy_book__entity__type=ENTITY.INTERNAL, then=Value(company_group)),
            default=Value(None))) | \
        Q(sell_book__entity__primary_company_group__alias__iexact=Case(
            When(sell_book__entity__type=ENTITY.INTERNAL, then=Value(company_group)),
            default=Value(None))))

I tried the above query utilizing Case and When but this doesn't exactly work because it will give the value as None to the primary_company_group whereas I need it not to query by that at all, almost like an ignore. Since primary_company_group is a nullable foreign key this skews my results heavily. Just FYI, I don't want to filter all entity__type to internal. Just the ones that are internal, I need to run the further query on those.

I come from a C# background so using Linq I would do something like this on the output of the first query:

alreadyFilteredList.Where(x =>
{
    if (x.BuyBook.Entity.Type == EntityType.Internal && 
        x.SellBook.Entity.Type == EntityType.Internal)
        return x.BuyBook.Entity.PrimaryCompanyGroup == companyGroup ||
               x.SellBook.Entity.PrimaryCompanyGroup == companyGroup;

    return true;
});

Ben J. · Accepted Answer · 2021-06-08 18:58:12Z

2

+50

You can accomplish this without using Case or When. I've added an additional conditional that requires that the type either !=ENTITY.INTERNAL or that it passes the final primary_company test.

The ~ operator negates the query.

return (
    Q(buy_book__entity__company_groups__alias__iexact=company_group) &
    Q(sell_book__entity__company_groups__alias__iexact=company_group) &
    (
        ~Q(buy_book__entity__type=ENTITY.INTERNAL) | 
        (
            Q(buy_book__entity__primary_company_group__alias__iexact=company_group) |
            Q(sell_book__entity__primary_company_group__alias__iexact=company_group)
        )
)

edited Jun 8, 2021 at 18:58

answered Jun 8, 2021 at 16:34

Ben J.

8061 gold badge8 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Usman Khan Over a year ago

I just noticed this doesn't seem to check for sell_book__entity__type=ENTITY.INTERNAL. I'm assuming I just need to add something like (~Q(buy_book__entity__type=ENTITY.INTERNAL) & ~Q(sell_book__entity__type=ENTITY.INTERNAL)) ?

Ben J. Over a year ago

That looks right. I'm not sure 100% what your business logic is supposed to look like, but you should be able to do it by chaining your queries like that.

Collectives™ on Stack Overflow

Django - Query values based on the result of an existing query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related