SQLAlchemy - Searching Three Tables at Once

Question

EDIT: Please excuse me, as I have just realised I've made an error with the example below. Here's what I want to achieve:

Say I have the three tables as described below. When a user enters a query, it will search all three tables for results where name is LIKE %query%, but only return unique results.

Here's some example data and output:

Data:

**Grandchild:**
id: 1
name: John
child_id: 1

**Grandchild:**
id: 2
name: Jesse
child_id: 2

**Child:**
id: 1
name: Joshua
parent_id: 1

**Child:**
id: 2
name: Jackson
parent_id: 1

**Parent:**
id: 1
name: Josie

If a user searches for "j" it will return the two Grandchild entries: John and Jesse. If a user searches for "j, Joshua", it will return only the Grandchildren who's child is Joshua - in this case, only John.

Essentially, I want to search for all the Grandchild entries, and then if the user types in more key words, it will filter those Grandchildren down based on their related Child entry's name. "j" will return all grandchildren starting with "j", "j, Josh" will return all grandchildren starting with "j" and whom have their Child LIKE %Josh%.

So, I have a setup like this:

Grandchild{
   id
   name
   child_id
}

Child{
   id
   name
   parent_id
}

Parent{
   id
   name
}

Grandchild is linked/mapped to child. Child is mapped to Parent.

What I want to do, is something like below, where I search all three databases at once:

return Grandchild.query.filter(or_(
  Grandchild.name.like("%" + query + "%"),
  Grandchild.child.name.like("%" + query + "%"),
  Grandchild.child.parent.name.like("%" + query + "%")
)).all()

Obviously the query above is incorrect, and returns an error:

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object has an attribute 'name'

What would the correct way to go about what I'm attempting be?

I am running MySQL, Flask-SQLAlchemy (which I believe extends SQLAlchemy), Flask.

Shortest path to getting the best answer back for sqlalchemy questions similar to this one is to give the SQL query you were hoping to produce. It's understandable that not everyone is going to have that 100% clear, nor do they necessarily have to in order to use sqlalchemy, but answering the "what sql do you want to see" question often ends up answering your primary question. — cdaddr
Even if you can't phrase your request in terms of textbook SQL, it would help to describe in sentences exactly what you want to get back from the query. Describing it with a non-working code snippet plus a "something like this" leaves it so vague that we can't answer specifically. I don't mean to criticize, only encourage. — cdaddr
@cdaddr I've updated the question. I hope it's not too complicated. — user1492385

Maksym Polshcha Maksym Polshcha · Accepted Answer · 2012-07-01T11:01:22

As for me, it is better to modify your data model (if it is possible). You can create a self-referenced table 'People' like that:

People
{ 
    id,
    name,
    parent_id,
    grandparent_id,
 } 

class People(Base):
    __tablename__ = "people"

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(Unicode(255), nullable=False)
    parent_id = Column(Integer, ForeignKey('people.id'))       # parent in hierarchy
    grandparent_id = Column(Integer, ForeignKey('people.id'))  # grandparent in hierarchy

    # relationships
    parent = relationship("People", primaryjoin="People.parent_id==People.id", 
                          remote_side=[id])
    grandparent = relationship("People", primaryjoin="People.grandparent_id==People.id", 
                               remote_side=[id])

Then the things get more obvious:

session.query(People).filter(People.name.like("%" + query + "%"))

SQLAlchemy - Searching Three Tables at Once

1 Answers