How to query multiple tables in SQLAlchemy ORM

Question

I'm a newcomer to SQLAlchemy ORM and I'm struggling to accomplish complex-ish queries on multiple tables - queries which I find relatively straightforward to do in Doctrine DQL.

I have data objects of Cities, which belong to Countries. Some Cities also have a County ID set, but not all. As well as the necessary primary and foreign keys, each record also has a text_string_id, which links to a TextStrings table which stores the name of the City/County/Country in different languages. The TextStrings MySQL table looks like this:

CREATE TABLE IF NOT EXISTS `text_strings` (
    `id` INT UNSIGNED NOT NULL,
    `language` VARCHAR(2) NOT NULL,
    `text_string` varchar(255) NOT NULL,
    PRIMARY KEY (`id`, `language`)
)

I want to construct a breadcrumb for each city, of the form:

country_en_name > city_en_name OR

country_en_name > county_en_name > city_en_name,

depending on whether or not a County attribute is set for this city. In Doctrine this would be relatively straightforward:

    $query = Doctrine_Query::create()
                ->select('ci.id, CONCAT(cyts.text_string, \'> \', IF(cots.text_string is not null, CONCAT(cots.text_string, \'> \', \'\'), cits.text_string) as city_breadcrumb')
                ->from('City ci')
                ->leftJoin('ci.TextString cits')
                ->leftJoin('ci.Country cy')
                ->leftJoin('cy.TextString cyts')
                ->leftJoin('ci.County co')
                ->leftJoin('co.TextString cots')
                ->where('cits.language = ?', 'en')
                ->andWhere('cyts.language = ?', 'en')
                ->andWhere('(cots.language = ? OR cots.language is null)', 'en');

With SQLAlchemy ORM, I'm struggling to achieve the same thing. I believe I've setup the objects correctly - in the form eg:

class City(Base):
    __tablename__ = "cities"

    id = Column(Integer, primary_key=True)
    country_id = Column(Integer, ForeignKey('countries.id'))
    text_string_id = Column(Integer, ForeignKey('text_strings.id'))
    county_id = Column(Integer, ForeignKey('counties.id'))

    text_strings = relation(TextString, backref=backref('cards', order_by=id))
    country = relation(Country, backref=backref('countries', order_by=id))
    county = relation(County, backref=backref('counties', order_by=id))

My problem is in the querying - I've tried various approaches to generating the breadcrumb but nothing seems to work. Some observations:

Perhaps using things like CONCAT and IF inline in the query is not very pythonic (is it even possible with the ORM?) - so I've tried performing these operations outside SQLAlchemy, in a Python loop of the records. However here I've struggled to access the individual fields - for example the model accessors don't seem to go n-levels deep, e.g. City.counties.text_strings.language doesn't exist.

I've also experimented with using tuples - the closest I've got to it working was by splitting it out into two queries:

# For cities without a county
for city, country in session.query(City, Country).\
    filter(Country.id == City.country_id).\
    filter(City.county_id == None).all():

    if city.text_strings.language == 'en':
    # etc

# For cities with a county
for city, county, country in session.query(City, County, Country).\
    filter(and_(City.county_id == County.id, City.country_id == Country.id)).all():

    if city.text_strings.language == 'en':
    # etc

I split it out into two queries because I couldn't figure out how to make the Suit join optional in just the one query. But this approach is of course terrible and worse the second query didn't work 100% - it wasn't joining all of the different city.text_strings for subsequent filtering.

So I'm stumped! Any help you can give me setting me on the right path for performing these sorts of complex-ish queries in SQLAlchemy ORM would be much appreciated.

Answer 1

The mapping for Suit is not present but based on the propel query I would assume it has a text_strings attribute.

The relevant portion of SQLAlchemy documentation describing aliases with joins is at:

http://www.sqlalchemy.org/docs/orm/tutorial.html#using-aliases

generation of functions is at:

http://www.sqlalchemy.org/docs/core/tutorial.html#functions

cyts = aliased(TextString)
cits = aliased(TextString)
cots = aliased(TextString)
cy = aliased(Suit)
co = aliased(Suit)

session.query(
            City.id, 
            (
                cyts.text_string + \
                '> ' + \
                func.if_(cots.text_string!=None, cots.text_string + '> ', cits.text_string)
            ).label('city_breadcrumb')
            ).\
            outerjoin((cits, City.text_strings)).\
            outerjoin((cy, City.country)).\
            outerjoin((cyts, cy.text_strings)).\
            outerjoin((co, City.county))\
            outerjoin((cots, co.text_string)).\
            filter(cits.langauge=='en').\
            filter(cyts.langauge=='en').\
            filter(or_(cots.langauge=='en', cots.language==None))

though I would think its a heck of a lot simpler to just say:

city.text_strings.text_string + " > " + city.country.text_strings.text_string + " > " city.county.text_strings.text_string

If you put a descriptor on City, Suit:

class City(object):
   # ...
   @property
   def text_string(self):
      return self.text_strings.text_string

then you could say city.text_string.