CODEWITHSUNDEEP
pythonpython-3.xflaskjinja2flask-sqlalchemy
Alex B
Alex B

Reputation: 1182

Access column value from relationship property

Assume the following example where we have two models each referring to their respective sqlite tables:

class supplier_invoices(db.Model):
  id = db.Column('id', db.Integer, primary_key = True)
  at_date =  db.Column(db.String(100))    
  at_shortnumber =  db.Column(db.String(100))    

  def __repr__(self):
      return str(self.at_date)

class product_inventory(db.Model):
  id = db.Column('id', db.Integer, primary_key = True)
  pi_shortnumber =  db.Column(db.String(100))
  pi_lot_name =  db.Column(db.String(100))
  pi_at_id = db.Column(db.Integer, db.ForeignKey('supplier_invoices.id'),
      nullable=False)
  at_date = relationship("supplier_invoices")

  def __init__(self, id, pi_shortnumber):
    self.id = id
    self.pi_shortnumber = pi_shortnumber


@app.route('/pro_inv/')
def product_inv():
    return render_template('product_inventory.html', 
      product_query = product_inventory.query.order_by(product_inventory.pi_shortnumber.desc()).limit(20).all())

Then assume we are using the following jinja2 template to display the query in a table form:

{% for pq in product_query %}
<tr>
    <td>
        {{ pi.at_date }}
    </td>
    <td>
        {{ pi.pi_lot_name }}
    </td>
    <td>
        {{ <!-- at_shortnumber --> }} <!-- ******* HOW TO GET SECOND COLUMN DATA HERE? -->
    </td>
    <td>
</tr>
{% endfor %}

As you can see retrieving the first column data pi.at_date using sqlalchemy relationships is relatively straightforward. The resulting jinja2 table displays at_date from the supplier_invoices child table along side the corresponding pi_lot_name column data from the parent product_inventory table it is joined with.

However, this relationship only returns the at_date column.

How would one go about retrieving the at_shortnumber column in the same model in order to display it along side the at_date column?

Obviously, one solution would be to create a new separate model relationship but this seems cumbersome to me since if one wants to retrieve let's say 10 columns we would have to have establish 10 separate model relationships.

Would anyone have any suggestions as to how to retrieve multiple columns' data from a related table using the same model? Or possibly a completely different way of achieving the same result?

Thank you in advance!

edit: additionally, how would one assign a parent name within the parent model class to the child column data so that one can manipulate the data directly from the model class? For example, if one wished to use : concat = column_property(pi_shortnumber + "_" + pi_lot_name + "_" + at_shortnumber )

Upvotes: 1

Views: 462

Answers (1)

simanacci
simanacci

Reputation: 2344

You can access it as pd.at_date.at_shortnumber. From your code pq.at_date returns at_date value of the coressponding SupplierInvoices which is correct, what it actually does is pq.at_date.at_date. But you have it sharing names with supplier_invoices.at_date hence the confusion. Either change the column name for supplier_invoices.at_date or the relationship name for product_inventory.at_date.

Upvotes: 1

Related Questions