SQLAlchemy - eager load for many to many secondary relation not working as expected

user_8982784 0
Viewed 167

In our system, we have entities Item and Store, and they are related to the Stock entity. An item can be stocked in multiple stores, and also multiple items can be stocked in one store, so a simple many to many relation.

However, when describing this relation with a secondary reference:

    stores = relationship(
        'Store',
        secondary='stock',
        backref='items'
    )

SQLAlchemy loads all socks for that related Store, not just those that are related to the referenced item.

eg. when we specify a relation that generates following sql:

SELECT item.id AS item_id, store.id AS store_id, stock.id AS stock_id, stock.store_id AS stock_store_id, stock.item_id AS stock_item_id
FROM item 
LEFT OUTER JOIN (stock AS stock_1 JOIN store ON store.id = stock_1.store_id) ON item.id = stock_1.item_id 
LEFT OUTER JOIN stock ON store.id = stock.store_id AND stock.item_id = item.id 
WHERE stock.item_id = item.id

That returns following data:

item_id, store_id, stock_id, stock_store_id, stock_item_id,
      1,        1,        1,              1,             1
      2,        1,        2,              1,             2
      1,        2,        3,              2,             1
      2,        2,        4,              2,             2

The actual loaded data is following:

items = [{
  id: 1,
  stores: [
    {
      id: 1,
      stocks: [
        { id: 1, item_id: 1 },
        { id: 2, item_id: 2 } <- should not be loaded items[0].id != 2
      ] 
    },
    {
      id: 2,
      stocks: [
        { id: 3, item_id: 1 },
        { id: 4, item_id: 2 } <- should not be loaded items[0].id != 2
      ] 
    }
  ]
},
{
  id: 2,
  stores: [
    {
      id: 1,
      stocks: [
        { id: 2, item_id: 2 },
        { id: 1, item_id: 1 } <- should not be loaded items[1].id != 1
      ] 
    },
    {
      id: 2,
      stocks: [
        { id: 4, item_id: 2 },
        { id: 3, item_id: 1 } <- should not be loaded items[1].id != 1
      ] 
    }
  ]
}]

For reference, take a look at the declaration of the entities and their relationships, as well as the query object:

Base = declarative_base()

class Item(Base):
    __tablename__ = 'item'
    id = Column(Integer, primary_key=True)

    stores = relationship(
        'Store',
        secondary='stock',
        backref='items'
    )

class Store(Base):
    __tablename__ = 'store'
    id = Column(Integer, primary_key=True)

class Stock(Base):
    __tablename__ = 'stock'
    id = Column(Integer, primary_key=True)
    store_id = Column(Integer, ForeignKey(Store.id), nullable=False)
    item_id = Column(Integer, ForeignKey(Item.id), nullable=False)

    item = relationship(Item, backref='stocks')
    store = relationship(Store, backref='stocks')

items = session.query(
    Item
).outerjoin(
    Item.stores,
    (Stock, and_(Store.id == Stock.store_id, Stock.item_id == Item.id))
).filter(
    Stock.item_id == Item.id,
).options(
    contains_eager(
        Item.stores
    ).contains_eager(
        Store.stocks
    )
).all()
flask-sqlalchemy eager-loading sqlalchemy postgresql python
1 Answers

That's because the stores with the same id are the same Store instance.

It's probably better to do explicit filtering when serializing/displaying the results.

That said, it's possible to override Item's __getattribute__ to intercept Item.stores to return _ItemStore wrappers, that only return stocks with the same item_id as the parent Item.id.

class Item(Base):
    # ...

    class _ItemStore:
        def __init__(self, store, item_id):
            self.id = store.id
            self._item_id = item_id
            self._store = store

        @property
        def stocks(self):
            return [stock for stock in self._store.stocks if stock.item_id == self._item_id]

    def __getattribute__(self, item):
        value = super().__getattribute__(item)
        if item == 'stores':
            value = [self._ItemStore(store, self.id) for store in value]
        return value

Adding a simple cache so that item.stores == item.stores:

def __getattribute__(self, item):
    value = super().__getattribute__(item)
    if item == 'stores':
        cache = getattr(self, '_stores', None)
        if cache is None:
            cache = self._stores = {}
        item_id = self.id
        item_store_cls = self._ItemStore
        value = [cache.setdefault(id(store), item_store_cls(store, item_id)) for store in value]
    return value
user_86017600
Related