CODEWITHSUNDEEP
pythonsqldatabasesqlalchemy
Zach Ward
Zach Ward

Reputation: 127

SQLAlchemy query where one row may not exist

I'm implementing some search functionality for a database of companies and their subsidiaries. For each company there may be multiple subsidiaries or none. Each subsidiary has a company_id column that links it to the id column of the parent company table.

Here is an example of how searching for a company by name:

def search_by_company_name(name):
    company_search = session.query(Company.name).filter(Company.name.like('%{}%'.format(name)))
    return company_search.all()

What I want is to be able to query a company by searching for not only its name but also the names of any subsidiaries that might share a similar name. What I have right now is:

def search_by_company_and_subsidiary_name(name):
    company_search = session.query(Company.name).filter(Company.name.like('%{}%'.format(name)) | Subsidiary.name.like('%{}%'.format(name)))
    search_results = company_search.filter(Company.id == Subsidiary.company_id)
    return search_results.all()

The problem with my approach is that if there are no subsidiaries for a certain parent company, then the query returns nothing, even if the name search matches to a row in the Company table.

For example:

search_by_company_name('accenture')
>>> [('Accenture',)]

However

search_by_company_and_subsidiary_name('accenture')
>>> []

I'm sure that the second function is returning [] because there are no subsidiaries for Accenture in my database, so the predicate .filter(Subsidiary.company_id == Company.id) is essentially removing any results already found in the Company table. I'm thinking the solution might be to do something like a left join, but I haven't had any luck after troubleshooting for a while.

Is there a good way to do a query like this in SQLAlchemy that will still return all the rows found in a certain table when there are no matching rows in the Subsidiary table for a given company?

Upvotes: 2

Views: 1100

Answers (1)

TheArchitect
TheArchitect

Reputation: 2173

You might consider implementing an outerjoin rather than a strict filter condition, e.g. something along the lines of:

def search_by_company_and_subsidiary_name(name):
    return session.query(Company)
                  .outerjoin(Subsidiary, Company.id == Subsidiary.company_id)
                  .filter(Company.name.like('%{}%'.format(name)) | Subsidiary.name.like('%{}%'.format(name)))
                  .all()

Upvotes: 3

Related Questions