CODEWITHSUNDEEP
pythonsqldjango
sinθ
sinθ

Reputation: 11493

Check if any database entries with foreign keys to an entry have a certain value (Django)

I'm running into a very complex situation, so I'm going to give a simplified version with only the information that pertains to my issue:

I have a People table and a Pets table, which has a foreign key called Owner. A person can have multiple pets. I need to search the people table based on their pets. Users specify something like "Get all people who don't have any pets named Philip". What I was doing was using a Q object for the filter (just searching for pets without that name) and then looping through to get the Owners. The issue is that if one person had two pets, and one pet is named Philip and one is named Yogifnia, the owner will still show up.

What's the best way to do this. Note that I need a solution that can easily handle requests such as:

"Get all people who don't have any pets named Philip AND don't have any pets that are poodles."

"Get all people who don't have any pets named Philip OR have a pet named Jasper"

"Get all people who don't have any pets named Philip OR have a pet named Jasper AND a pet named Andrew"

I can think of awkward and inefficient solutions, but no good ones.

Upvotes: 0

Views: 62

Answers (1)

sergzach
sergzach

Reputation: 6754

Try something like the next:

People.objects.filter( ~Q( pet__name = 'Philip' ) & ~Q( pet__type = 'poodle' ) ) # *
People.objects.filter( ~Q( pet__name = 'Philip' ) | Q( pet__name = 'Jasper' ) )
People.objects.filter( ~Q( pet__name = 'Philip' ) | Q( pet__name = 'Jasper' ) | Q( pet__name = 'Andrew' ) )

Do we have any people which have pet 'Philip' and all which pets are not poodles?

People.objects.filter( ~Q( pet__name = 'Philip' ) & ~Q( pet__type = 'poodle' ) ).exists()

For * we'll have a query like the next:

SELECT people.id, people.name FROM people 
WHERE 
(
    NOT 
    (
        (
            people.id IN 
            (
                SELECT U1.owner_id FROM pet U1 
                WHERE 
                (
                    U1.name = E\'Philip\'  AND U1.owner_id IS NOT NULL
                )
            ) 
            AND people.id IS NOT NULL
        )
    ) 
    AND NOT 
    (
        (
            people.id IN 
            (
                SELECT U1.owner_id FROM pet U1 WHERE 
                (
                    U1.type = E\'poodle\'  AND U1.owner_id IS NOT NULL
                )
            ) 
            AND people.id IS NOT NULL
        )
    )
);

Upvotes: 2

Related Questions