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’},
Then we can delete everything where there are no values:
new_list = categories_global[:]
for cat in new_list:
if not cat[‘items’]:
categories_global.remove(cat)
Now in categories_global only one value.