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

Answer 1

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