0

This works fine

protocols = db.session.query(Protocol.id, Protocol.customer_id).all()

results in

[(1, 1), (2, 1)]

If I try to include relationship column (Protokol.zakaznik.objednatel) instead of Protocol.customer_id in query it returns: AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Protocol.zakaznik has an attribute 'objednatel'.

protocols = db.session.query(Protocol.id, Protocol.zakaznik.objednatel).all()

If I iterate through protocols, the relationship works well

protocols = db.session.query(Protocol).all()
for protocol in protocols:
    print(protocol.zakaznik.objednatel)
    print(protocol.zakaznik.id)

So what is the problem in using relationship in query? Is it possible to return [(1, CustomerObjednatel1), (2, CustomerObjednatel1)]? I can of course do join query which works fine. I thought that using relationship would be shorter way of doing this.

models.py

class Customer(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    objednatel = db.Column(db.String(120), unique=True, nullable=False)
    protocols = db.relationship('Protocol', back_populates='zakaznik')
    

class Protocol(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    customer_id = db.Column(db.Integer, db.ForeignKey('customer.id'), nullable=False)
    zakaznik =  db.relationship('Customer', back_populates='protocols')
vondravl
  • 90
  • 3
  • 11

2 Answers2

0

don't define the models like that you can set backref in Protocol model, refer to docs

class Customer(db.Model):
id = db.Column(db.Integer, primary_key=True)
objednatel = db.Column(db.String(120), unique=True, nullable=False)
protocols = db.relationship('Protocol',backref="cus")


class Protocol(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    customer_id = db.Column(db.Integer, db.ForeignKey('customer.id'), nullable=False)

You can make a query like that

customer1 = Customer.query.filter_by(id=1).first()
customer1.protocols[0].id // gives the id of the first protocol of customer1

Querying protocols, You can use the backref with cus to access the whole object Customer

protocol2 = Protocol.query.filter_by(id=2).first()
protocol2.cus // gives the full information of object something like - <Customer 2>
protocol2.cus.objednatel  // gives the objednatel of protocol2's customer

You can watch the video for more understanding

charchit
  • 1,492
  • 2
  • 6
  • 17
  • From what I've read I think that back_populates and backref are equivalents (see [here](https://stackoverflow.com/questions/39869793/when-do-i-need-to-use-sqlalchemy-back-populates)). But yes, the way how you wrote the queries - this works. Sorry for being slow to understand but can you explain why I always need to filter by specific customer to access cus relationship? Why I can't query all protocols with cus relationships? It is because the way how the relationship "column" works? – vondravl Jun 16 '21 at 07:58
  • ya you can query all the protocols like this `p = Protocol.query.filter_by().all()` , this gives a list of objects, loop through them like for i in p: print(p.cus , p.objednatel) . – charchit Jun 16 '21 at 08:02
  • OK, it also works without filter_by(), just `Protocol.query.all()`, but this will return `[, ] ` when I write `db.session.query(Protocol.id, Protocol.customer_id).all() ` then this will return `[(1, 1), (2, 1)] ` but back to the original question why this will not work `db.session.query(Protocol.id, Protocol.cus.objednatel).all() ` – vondravl Jun 16 '21 at 08:52
  • I never queried like that maybe this SO [question](https://stackoverflow.com/questions/40020388/flask-sqlalchemy-db-session-querymodel-vs-model-query), and [this](https://stackoverflow.com/questions/39193039/attributeerror-neither-instrumentedattribute-object-nor-comparator-object-a) can help. – charchit Jun 16 '21 at 09:11
0

So I finally found pretty much same question here on SO.

From the answer there it looks like the session.query can't take relationship as an argument. They link docs reference, where it is written that entities are coming as argument. So I guess that relationship is not entity (entity's attribute) and session.query can't consume it. If somebody can confirm, it would be nice, but I guess this is it.

vondravl
  • 90
  • 3
  • 11