SQLAlchemy distinct() doesn't return unique records with a keyword search

Question

I have a search bar which searches for users with the following query:

db.session.query(User).filter(or_(User.name.contains(keyword), User.location.contains(keyword))).limit(16).all()

As you can see, it searches using keywords (which comes from a list formed by the search query). If I search for 'John Smith', the search will find a user with a name containing 'John' and then the same user with a name containing 'Smith', so it will return the User twice.

To solve this issue I tried using distinct() like so:

db.session.query(User).distinct().filter(or_(User.name.contains(keyword), User.location.contains(keyword))).limit(16).all()

but it didn't work so I tried using distinct(User.id):

db.session.query(User).distinct(User.id).filter(or_(User.name.contains(keyword), User.location.contains(keyword))).limit(16).all()

though that did not work either. So what is the problem?

Edit:

One bit I forgot to mention the code looks like this:

query = 'John Smith'
for keyword in query.split():
    db.session.query(User).distinct().filter(or_(User.name.contains(keyword), User.location.contains(keyword))).limit(16).all()

This is why it returns 2 of the same user. I use the for loop because there are other field to the database, so if I searched for John Smith Mexico it would search by each word.

lrnzcig lrnzcig · Accepted Answer · 2015-09-02T17:05:56

One way to solve your problem is having one unique query for all the keywords that you would like to search for. (That is, instead of the distinct, which does not know that you are actually running queries in a loop; the distinct will be applied independently to each of the queries, and that's why you get 2 results).

Something like this would work:

In [17]:

from sqlalchemy import or_
query = 'John Smith'
keyword_c = [User.name.contains(keyword) for keyword in query] + [User.location.contains(keyword) for keyword in query]
q = session.query(User).filter(or_(*keyword_c))
for x in q.limit(16).all():
    print x.name

John Smith

Hope it helps.

SQLAlchemy distinct() doesn't return unique records with a keyword search

1 Answers