[SOLVED] Combine information from two tables automatically

Combine information from two tables automatically

I have two tables Materials and MaterialSubFamilies which are defined like this:

class MaterialSubFamilies(db.Model):
  sub_family = db.Column(db.String(64), index=True)
  materials = db.relationship('Materials', backref='msub_family', lazy='dynamic')

class Materials(db.Model):
  sub_family = db.Column(db.Integer, db.ForeignKey('material_sub_families.id'))

After I query the Materials table I convert it to dict using Marshmallow using this schema:

class MaterialsSchema(ma.ModelSchema):
  class Meta:
    model = models.Materials

And what I get is something like this:

"materials": [
 {"id": 1,
  "name": "bla",
  "msub_family": 2, 
 }]

However what I would like is to have not the id of the SubFamily but it's name directly (the string in the sub_family field) when I get the Materials dictionary. So it's a sort of join that I want to have automatically every time I query the Materials table. Is something like this possible ? Thanks

Solution

In every modern ORM there is a concept called Eager Loading. It is the process whereby a query for one type of entity also loads related entities as part of the query.

In SQLAchemy the same concept is provided as Joined Eager Loading, and to use it, all you have to do is set lazy='joined' in your MaterialSubFamilies model.

class MaterialSubFamilies(db.Model):
  sub_family = db.Column(db.String(64), index=True)
  materials = db.relationship('Materials', backref='msub_family', lazy='joined')

and..

class MaterialsSchema(ma.ModelSchema):
  class Meta:
    model = models.Materials
    msub_family = fields.Nested("MaterialSubFamiliesSchema")

Refer below documentation for more detailed explanation on this topic: https://docs.sqlalchemy.org/en/13/orm/loading_relationships.html#joined-eager-loading

Or watch this video explanation by PrettyPrintted:
https://www.youtube.com/watch?v=g1oFlq7D_nQ&t=336s