如何在SQLAlchemy ORM中查询多个表

Question

我刚接触SQLAlchemy ORM，正在努力处理多个表之间的复杂查询，而这些在Doctrine DQL中对我来说相对简单。

我有一些城市的数据对象，这些城市属于不同的国家。有些城市还设置了县的ID，但并不是所有城市都有。每条记录除了必要的主键和外键外，还有一个text_string_id，这个ID链接到一个TextStrings表，存储城市、县和国家的不同语言名称。TextStrings的MySQL表大概是这样的：

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`)
)

我想为每个城市构建一个面包屑导航，格式如下：

country_en_name > city_en_name 或者

country_en_name > county_en_name > city_en_name，

这取决于这个城市是否设置了县的属性。在Doctrine中，这个操作相对简单：

    $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');

但在SQLAlchemy ORM中，我在实现同样的功能时遇到了困难。我相信我已经正确设置了对象，比如：

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))

我的问题出在查询上——我尝试了各种方法来生成面包屑，但似乎都不奏效。有几点观察：

也许在查询中使用像CONCAT和IF这样的东西并不是很符合Python的风格（在ORM中真的可以吗？）——所以我尝试在SQLAlchemy之外，在一个Python循环中执行这些操作。然而，在这里我很难访问到单个字段——例如，模型的访问器似乎无法深入到多层级，比如City.counties.text_strings.language并不存在。

我还尝试使用元组——我最接近成功的方式是将其拆分成两个查询：

# 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

我将其拆分成两个查询，因为我不知道如何在一个查询中让Suit的连接变为可选。但这种方法当然很糟糕，更糟的是第二个查询并没有100%成功——它没有连接所有不同的city.text_strings以进行后续过滤。

所以我现在很困惑！如果你能给我一些建议，让我在SQLAlchemy ORM中进行这些复杂查询的方向上走得更顺利，我将非常感激。

mysql orm sqlalchemy 数据模型外键复杂查询面包屑导航多表查询

如何在SQLAlchemy ORM中查询多个表

2 个回答

撰写回答