Combine information from two tables automatically

Question

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

Pratik149 · Accepted Answer · 2020-06-02T08:33:12.660

1

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

edited Jun 02 '20 at 08:33

answered May 27 '20 at 18:37

Pratik149

1,109
1
9
16

Hi @Boris kindly consider accepting an answer if it solves your problem, or if you solved your problem using some other approach then feel free to answer and accept it. If your problem still persists then let us know, we will try to come up with some other solution. – Pratik149 May 28 '20 at 14:56
Hi @Pratik149. Thanks a lot, your answer partially solved my problem, as it does indeed changes the returned objec. However I still get the same dict after the convertion by Marshmallow. So I think the problem is in how I define the MaterialsSchema. – Boris Jun 01 '20 at 09:59
Together with `lazy='joined'` I changed the MaterialsSchema like this to get the result: `class MaterialsSchema(ma.ModelSchema): class Meta: model = models.Materials msub_family = fields.Nested("MaterialSubFamiliesSchema")` – Boris Jun 01 '20 at 10:27
Aha yes, you are right @Boris. I have updated my answer accordingly, but feel free to edit the answer, if you want to add anything more to it. – Pratik149 Jun 02 '20 at 08:38

Combine information from two tables automatically

1 Answers1