I'm working with a Flask application where I have a LargeGroupAttendance model that references another model called Attendee. I'm trying to request all of the LargeGroupAttendance objects that match a certain criteria, but I'm trying to sort them by a column of the Attendee model - is that even possible? Here are the two models below:
""" Attendeee Class """
class Attendee(Base):
__tablename__ = 'attendee'
id = Column(Integer, primary_key=True)
first_name = Column(String(200))
last_name = Column(String(200))
year = Column(String(200))
email = Column(String(100), unique=True)
dorm = Column(String(100))
def __init__(self, first_name, last_name, year, email, dorm):
self.first_name = first_name
self.last_name = last_name
self.year = year
self.email = email
self.dorm = dorm
def __repr__(self):
return '<Attendee %r>' % self.first_name
""" Large Group Attendance Class """
class LargeGroupAttendance(Base):
__tablename__ = 'large_group_attendance'
id = Column(Integer, primary_key=True)
first_time = Column(Integer)
large_group_id = Column(Integer, ForeignKey('large_group.id'))
large_group = relationship("LargeGroup", backref=backref('large_group_attendance', order_by=id))
attendee_id = Column(Integer, ForeignKey('attendee.id'))
attendee = relationship("Attendee", backref=backref('large_group_attendance', order_by=id))
Do I need to add something to my attendee class to make this possible? And here's a query I've tried before, but it's had no output (no errors either..). Where am I going wrong?
attendance_records = db.session.query(LargeGroupAttendance).filter_by(large_group_id=event_id).order_by(desc(LargeGroupAttendance.attendee.first_name))
You then create a Flask application instance called app , which you use to configure two Flask-SQLAlchemy configuration keys: SQLALCHEMY_DATABASE_URI : The database URI to specify the database you want to establish a connection with. In this case, the URI follows the format sqlite:/// path/to/database. db .
The relationship function is a part of Relationship API of SQLAlchemy ORM package. It provides a relationship between two mapped classes. This corresponds to a parent-child or associative table relationship.
First you need to supply a Primary Key for each model. Then you need to define one Foreign Key which refers to the Primary Key of the other model. Now you can define a relationship with a backref that allows direct access to the related model. In this case, the following 2 lines should look like this: request_id = db.
I think you need add a join to your query, something like this:
.join(LargeGroupAttendance.attendee)
so that the final query would look like this:
attendance_records = (db.session.query(LargeGroupAttendance).
filter_by(large_group_id = event_id).
join(Attendee, LargeGroupAttendance.attendee).
order_by(desc(Attendee.first_name))
)
See SQLAlchemy: How to order query results (order_by) on a relationship's field? for a more detailed explanation
I know this is an old post, but it should up when I was searching, so maybe this will be useful to someone else
""" Attendeee Class """
class Attendee(Base):
__tablename__ = 'attendee'
id = Column(Integer, primary_key=True)
first_name = Column(String(200))
last_name = Column(String(200))
year = Column(String(200))
email = Column(String(100), unique=True)
dorm = Column(String(100))
...
""" Large Group Attendance Class """
class LargeGroupAttendance(Base):
__tablename__ = 'large_group_attendance'
...
attendee_id = Column(Integer, ForeignKey('attendee.id'))
attendee = relationship(
"Attendee",
backref=backref(
'large_group_attendance',
order_by=Attendee.__table__.columns.id
)
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With