What is an Efficient Way to do an AND/OR Search Django-Postgres App?

Question

In my Django app I have a Publication model and a Tag model which have a many to many relationship.

Let's say I have four tags: men, women, fashion, design. I want to find all publications that have a men OR women tag associated with it, AND a fashion OR design tag associated with it. For example, in a search field I might enter: (men || women) && (fashion || design). In other words, the results should yield publications that have at least one tag from each 'or' pair.

I have figured out how to do just an 'or' search (i.e. find all publications with a tag of men or women or fashion or design) and I have figured out how to do just an and search (i.e. find all publications that have all the tags, men and women and fashion and design), but I do not know the most efficient way to do the and-or combo.

Thus far I have split up the query string to get a list of 'or' pairs, and I started to create a search predicate, but then I thought, what will I do with these results? For example...

if 'qTag' in request.GET:
        or_queries = request.GET['qTag'].split('&&')

        for or_query in or_queries:
            or_query_set = or_query.split()
            for sub_query in or_query_set:
                if pub_predicate:
                    pub_predicate = pub_predicate | Q(tags__title__istartswith=sub_query)
                else:
                    pub_predicate = Q(tags__title__istartswith=sub_query)
            pubs_for_set = Publication.objects.filter(pub_predicate).distinct()
            pubs_for_tags.append(pubs_for_set)
            pub_predicate = None
            pubs_for_set = None

Doing the above gives me a list (pubs_for_tags) of lists (pubs_for_set) that gives all results for each "or"pair." But now what? I don't know how to proceed with an and search. Can anyone suggest a way to do this?

what is pub_predicate ? Also, your query doesnt sync up with your explanation. can you please be more specific ? — karthikr, Jun 06 '13 at 15:31
pub_predicate is the predicate that I am searching the db with. I build the predicate in a for loop so that an undefined number of tags can be searched. I edited the above code just now to be a bit more accurate but no, it doesn't line up with what I want, I just wanted to show what I have so far. All the above does is search the or pairs and combine them into a list. I'm still trying to figure out how to do the 'and' component. — jac300, Jun 06 '13 at 16:03

score 0 · Accepted Answer · edited May 23 '17 at 12:18

0

I'm not sure if there is a way to do this. I wanted to say try combining filter with Q but then chanced upon these:

Combining AND OR in Django Queries

and

AND OR in Django queries II

edited May 23 '17 at 12:18

Community

1
1

answered Jun 06 '13 at 15:33

Sid

7,511
2
28
41

score 0 · Answer 2 · edited May 23 '17 at 12:05

I think I may have found a solution to my own question. If I have the following tags:

        query1 = "men"
        query2 = "women"
        query3 = "fashion"
        query4 = "design"

And I want to find all publications that have men OR women AND fashion OR design tags, it seems to work if I do:

pubs = Publication.objects.filter(Q(tags__title__iexact=query1) | Q(tags__title__iexact=query2) and (Q(tags__title__iexact=query3) | Q(tags__title__iexact=query4)))

After looking at this question: Django query filter combining AND and OR with Q objects don't return the expected results, I changed one of the suggested solutions (which had not been accepted) to use 'and' instead of '&' and it seems to be working although I have no idea why.

What is an Efficient Way to do an AND/OR Search Django-Postgres App?

2 Answers2

Linked