SQLAlchemy - an example of a complex many to many selection

Sample sample through SQLAlchemywhen you need to pick up data linked in a chain with other tables, where in the last of them you need to select data by a certain value.

We have a class CategoryGlobal:

class CategoryGlobal(db.Model, RoleMixin):
__tablename__ = ‘categories_global’
id = db.Column(db.Integer, primary_key=True, unique=True)
name = db.Column(db.String(255))

Inside this class there is a connection with two other tables by the method many to many:

items = db.relationship(
‘Item’,
secondary=‘categories_item’,
backref=db.backref(‘categories_global’),
)

The property is also indicated there:

@property
def serialize_for_favorites(self):
return {
‘id’: self…id,
‘name’: self…name,
‘items’: [x.serialize if x.favorite == False else ” for x in self.items],
}

Let’s take a look at the last piece of code:

‘items’: [x.serialize if x.favorite == False else ” for x in self.items]

It usually looks like this:

‘items’: [x.serialize for x in self.items]

And in this case all will be collected items for all categories_global…

We only need to collect selected records in which item there is a value in the column favorite and it is equal True… Otherwise, return an empty array. Therefore, the comparison is used:

if x.favorite == False else ”

Now let’s form the request itself in python to pick up the data:

query = db.session…query(CategoryGlobal)…all()
categories_global = [x.serialize_for_favorites for x in query]

If item at categories_global exists but True at favorite No:

{‘items’: [], ‘id’: eleven, ‘name’: ‘cow’},
{‘items’: [”], ‘id’: 4, ‘name’: ‘bull’},

From the example above, you can see that where there was item appeared [одинарные кавычки]if item had Truethen the result would be like this:

{‘items’: [], ‘id’: eleven, ‘name’: ‘cow’},
{‘items’: [{‘cats’: [{‘hidden’: False,
‘id’: 263,
‘name’: ‘слон’}],
‘date’: ‘2021-09-11’,
‘favorite’: True,
‘id’: 283,
‘name’: ‘went’,
‘note’: None,
‘time_passed’: [{‘cat_inner_id’: 263,
‘item_id’: 283,
‘time_passed’: 32426}],
‘timestamp’: 1631349025.560444,
‘weekday’: ‘sat’}], ‘id’: 4, ‘name’: ‘bull’},