-1

I did outerjoin of two tables. And I got the correct result. But I don't know how to access the Columns in the result. Below is my code.

result = db.session.query(Purchase, Product.pr_id).outerjoin(Product, Purchase.id == Product.pr_id).filter(Purchase.user_id==current_user.id, Product.status==pr_status).order_by(desc(Purchase.cost)).all()

'result[0].Purchase.cost' gives me cost of the first purchase. But 'result[0].Product.status' giving AttributeError: Could not locate column in row for column 'Product'. Why this happen ? How can I access the 'status' column

Gireesh
  • 111
  • 1
  • 7

1 Answers1

1

Changing the second argument of db.session.query() from Product.pr_id to Product should work.

result = db.session.query(Purchase, Product).outerjoin(Product, Purchase.id == Product.pr_id).filter(Purchase.user_id==current_user.id, Product.status==pr_status).order_by(desc(Purchase.cost)).all()

This is in the docs at selecting-orm-entities-and-attributes] in the example with

stmt = select(User, Address).join(User.addresses).order_by(User.id, Address.id)
Ian Wilson
  • 6,223
  • 1
  • 16
  • 24
  • Also if the status column in the result is null, I want to change it to 'Pending'. I tried the following, for each in result: if each.Product.status == None : each.Product.status = 'Pending. But it gives error AttributeError: 'NoneType' object has no attribute 'status'. – Gireesh Mar 18 '22 at 08:26
  • Sometimes outerjoin has no matching right side, ie, Product, so it will be NULL. Could that be the case? – Ian Wilson Mar 18 '22 at 14:21
  • Yes. But after doing the outer join I want to rewrite all the unmatching to status = 'pending'. Any way to do that? – Gireesh Mar 18 '22 at 16:23
  • I think you will need to join from Product to Purchase, ie. swap left and right. `db.session.query(Product, Purchase).outerjoin(Purchase, Product.pr_id == Purchase.id)` etc. – Ian Wilson Mar 18 '22 at 16:54
  • @gireesh It helps to sometimes print the query out to make sure the SQL is what you expect. You can just print the result of `q = db.session.query(...)` before chaining `q.first()` or `q.all()`. – Ian Wilson Mar 18 '22 at 16:57
  • No. The join is from Purchase to Product only. Status column in Product table can have either 'Approved' or 'Rejected' ,ie, it contains purchase_id of 'Approved' or 'Rejected' purchases only. But if the purchase request is neither 'approved' nor 'rejected', I want status in my result to be 'Pending'. ie, pending purchases are not allowed to include in the product table. So if status column corresponding to a purchase in the result is 'NULL', it means the purchase is 'Pending'. In that case I want to make status column to 'pending' in the result. Any way to do this? – Gireesh Mar 18 '22 at 17:16
  • Maybe what you want is a [full outer join](https://docs.sqlalchemy.org/en/13/orm/query.html#sqlalchemy.orm.query.Query.join.params.full), ie. `.outerjoin(Product, Purchase.id == Product.pr_id, full=True)`. It hard for me to tell without seeing more. INNER JOIN (no nulls), LEFT OUTER JOIN (nulls on the right), RIGHT OUTER JOIN (nulls on the left), FULL OUTER JOIN (nulls on either side). So for full outer join you will see products not linked to a Purchase and purchases not linked from a product. When no link exists the other side will be NULL. – Ian Wilson Mar 18 '22 at 17:32
  • No. After doing outerjoin if 2 rows in my results are , pur_id = 2 pr_id = 1 status = 'Approved' , pur_id = 3 pr_id = 5 status = NULL – Gireesh Mar 18 '22 at 17:43
  • Then in the next step I want to convert this result into , pur_id = 2 pr_id = 1 status = 'Approved' , pur_id = 3 pr_id = 5 status = 'Pending' – Gireesh Mar 18 '22 at 17:43
  • I tried the following, for each in result: if each.Product.status == None : each.Product.status = 'Pending. But it gives error AttributeError: 'NoneType' object has no attribute 'status' – Gireesh Mar 18 '22 at 17:45
  • That is because `each.Product == None`, ie. You are trying None.status and None has no attribute status. – Ian Wilson Mar 18 '22 at 18:28