canni canni - 5 months ago 12
Python Question

Multiple rows returned with unique key?

Given those 3 simple SQLAlchemy models:

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


I don't understand why this query:

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


Throws
multiple rows found
execption, when there are multiple
Channel
s associated with the
handset_id
, but with distinct
customer_id
while this query, works OK:

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


How can I load channel with eager loading of customer & handset?

Answer

The 1st query produces an implicit cross join between handsets, customers and channels (which is the source of multiple rows for one) + 2 left joins (from customers and handsets for eager load), which is probably not what you want:

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 = ?

The second works as you explicitly define the joins and disable eager load left joins. The 1st query would work using 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 = ?))

In essence this separately checks that there exists both handset and customer for this channel with the given predicates and then left joins the customer and handset to this channel.

You could also instruct SQLAlchemy that you're using an explicit join in the second query and to eager load using that with 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 = ?

which is in my opinion the cleaner solution.