用唯一键返回的多行？

Question

考虑到这3个简单的SQLAlchemy模型：

class Customer(ModelBase):
    __tablename__ = 'customers'

    id = sa.Column(sa.Integer, primary_key=True)
    uid = sa.Column(sa.Unicode, nullable=False, unique=True)


class Handset(ModelBase):
    __tablename__ = 'handsets'

    id = sa.Column(sa.Integer, primary_key=True)
    imei = sa.Column(sa.Unicode(15), nullable=False, unique=True)


class Channel(ModelBase):
    __tablename__ = 'channels'

    id = sa.Column(sa.Integer, primary_key=True)

    customer_id = sa.Column(sa.ForeignKey(Customer.id), nullable=False)
    handset_id = sa.Column(sa.ForeignKey(Handset.id), nullable=False)

    customer = relationship(Customer, backref='channels', lazy='joined')
    handset = relationship(Handset, backref='channels', lazy='joined')


    __table_args__ = (
        sa.Index('uk_channels_customer_handset',
                 customer_id, handset_id,
                 unique=True),
    )

我不明白为什么这个问题：

session.query(Channel).filter(Handset.imei == '1234', Customer.uid == 'test').one_or_none()

当有多个与multiple rows found相关联的Channel，但在此查询中使用不同的customer_id时，抛出handset_id退出，工作正常：

c = aliased(Customer)
h = aliased(Handset)
session.query(Channel).enable_eagerloads(False).join(c).join(h).filter(
    c.uid == 'test', h.imei == '1234',
).one_or_none()

如何在客户和手机的殷切加载下加载频道？

Answer 1

第一个查询会在手机、客户和通道之间产生一个隐式交叉连接(这是一个多行的来源)+2个左联接(来自客户和听筒)，这可能不是您想要的：

In [7]: print(session.query(Channel).filter(Handset.imei == '1234', Customer.uid == 'test'))
SELECT channels.id AS channels_id, channels.customer_id AS channels_customer_id, channels.handset_id AS channels_handset_id, customers_1.id AS customers_1_id, customers_1.uid AS customers_1_uid, handsets_1.id AS handsets_1_id, handsets_1.imei AS handsets_1_imei 
FROM handsets, customers, channels LEFT OUTER JOIN customers AS customers_1 ON customers_1.id = channels.customer_id LEFT OUTER JOIN handsets AS handsets_1 ON handsets_1.id = channels.handset_id 
WHERE handsets.imei = ? AND customers.uid = ?

第二种方法的工作方式是显式定义联接，并禁用急切的加载左联接。第一个查询将使用has()

In [17]: print(session.query(Channel).filter(Channel.handset.has(imei='1234'), 
                                             Channel.customer.has(uid='test')))
SELECT channels.id AS channels_id, channels.customer_id AS channels_customer_id, channels.handset_id AS channels_handset_id, customers_1.id AS customers_1_id, customers_1.uid AS customers_1_uid, handsets_1.id AS handsets_1_id, handsets_1.imei AS handsets_1_imei 
FROM channels LEFT OUTER JOIN customers AS customers_1 ON customers_1.id = channels.customer_id LEFT OUTER JOIN handsets AS handsets_1 ON handsets_1.id = channels.handset_id 
WHERE (EXISTS (SELECT 1 
FROM handsets 
WHERE handsets.id = channels.handset_id AND handsets.imei = ?)) AND (EXISTS (SELECT 1 
FROM customers 
WHERE customers.id = channels.customer_id AND customers.uid = ?))

本质上，这分别检查给定谓词是否存在此通道的手机和客户，然后将客户和手机连接到此通道。

您还可以告诉SQLAlchemy，您使用的是第二个查询中的显式联接，并使用该连接来急切地加载和contains_eager()

In [28]: print(session.query(Channel).\
    join(c).join(h).\
    options(contains_eager(Channel.handset, alias=h),
            contains_eager(Channel.customer, alias=c)).\
    filter(
        c.uid == 'test', h.imei == '1234',
    ))
SELECT customers_1.id AS customers_1_id, customers_1.uid AS customers_1_uid, handsets_1.id AS handsets_1_id, handsets_1.imei AS handsets_1_imei, channels.id AS channels_id, channels.customer_id AS channels_customer_id, channels.handset_id AS channels_handset_id 
FROM channels JOIN customers AS customers_1 ON customers_1.id = channels.customer_id JOIN handsets AS handsets_1 ON handsets_1.id = channels.handset_id 
WHERE customers_1.uid = ? AND handsets_1.imei = ?

在我看来，这是更清洁的解决方案。