-1

I would like to display query results for my database, but it confuses me - it doesn't act as I would expect, not displaying anything every time. My models:

class Ball(Base):
__tablename__ = 'balls'
id = Column(Integer, primary_key=True)
ball_id = Column(Integer, ForeignKey('fields.id'))
ball = relationship("Field", back_populates="fields")


class Field(Base):
    __tablename__ = 'fields'
    id = Column(Integer, primary_key=True)
    nickname = Column(String)
    places = relationship("Ball", order_by=Ball.id, back_populates="fields")

My code in Flask:

@app.route("/addball", methods=['GET', 'POST'])
def addball():
    records = dict()
    with create_session() as session:
        for field in session.query(Field).order_by(Field.nickname.asc()).all():
            record = (session.query(Field).
                      join(Ball).
                      filter(Ball.field_id == field.id))
            if record:
                records[field.id] = record
    return render_template("addball.html", form=form, records=records)

and Jinja template:

{% for key, field in records.items() %}
    {% for elem in field %}
        {{ elem }}
    {% endfor %}
{% endfor %}

What I want to accomplish: I want to be able to display id and ball_id and also nickname for every field. My best shot so far was to display query in Jinja template, like so:

SELECT fields.id AS fields_id, fields.nickname AS fields_nickname FROM fields JOIN balls ON balls.id = balls.ball_id WHERE balls.ball_id = ?
PotatoBox
  • 583
  • 2
  • 10
  • 33
  • You may want to rename your question so that it addresses your actual issue about querying the database, if printing the query is just a troubleshooting step. – robmathers Aug 31 '17 at 23:16

1 Answers1

0

You're making your query overly complicated. You've given SQLAlchemy relationship information, so don't worry about trying to create a query with a join.

To get a list of fields, and display the related ball data, I would do something along these lines (apologies if this doesn't totally match your schema, but it should be close):

In your Flask route:

records = session.query(Field.order_by(Field.nickname.asc()).all()
return render_template("addball.html", form=form, records=records)

In your template:

{% for field in records %}
<p>
    Field ID: {{ field.id }}
    Ball ID: {{ field.ball.id }}
    Nickname: {{ field.nickname }}
</p>
{% endfor %}

The whole point of an ORM is that you get back objects with properties that will link between related tables, so that you can access data as you would any other objects and variables in your code, like field.ball, and don't have to mess around with joins or other complex queries too much.

robmathers
  • 3,028
  • 1
  • 26
  • 29
  • I'm unable to access ``Ball`` fields at all, when printing ``{{ field }}``, the output is ````, so no fields from ``Ball``. My best shot so far ``session.query(Field, Ball).filter(Field.id == Ball.id).order_by(Field.nickname.asc()).all()`` and got ``(, )``, but it misses the point of ORM at all. – PotatoBox Sep 01 '17 at 10:34
  • That's because `{{ field }}` outputs the string representation of the object. What happens when your template uses `{{ field.id }}`? – robmathers Sep 01 '17 at 16:01
  • It renders ``Field`` model fields correctly and therse values are easy accessible (Just like you wrote), but now I think it's a problem with relations - just when I thought I figured it out and changed ``back_populates="fields"`` to ``back_populates="balls"`` in ``Ball`` model, error ``Mapper 'Mapper|Field|fields' has no property 'balls'`` appeared. – PotatoBox Sep 01 '17 at 18:36
  • Yeah, looking at your class definitions, it looks like you've got your relationships mixed up a bit. If you're using `back_populates`, I think they need to be reciprocal. The `back_populates` value on Field should match the attribute value on Ball, and vice versa. See [this example](http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#one-to-many) from the docs. – robmathers Sep 01 '17 at 18:52