[FIXED] How do I make a db relationship with secondary backref work in both directions in Flask SQLAlchemy?

Issue

I created a User model and Kid model. In the kid model I have:

parents = db.relationship('User', secondary=kids_users,
                        backref=db.backref('kids', lazy='dynamic'))

The secondary table definition looks like this:

kids_users = db.Table('kids_users',
  db.Column('kid_id', db.Integer(), db.ForeignKey('kid.id')),
  db.Column('user_id',db.Integer(), db.ForeignKey('user.id')))

And my User model does not contain anything related to this since this code does everything.

Here's the situation: when I query some_kid.parents it gives me back a nice array:

[<User u'someuser@yahoo.com'>]

But when I query the other way around some_user.kids it gives me back some sql query:

SELECT kid.id AS kid_id, kid.first_name AS kid_first_name, kid.middle_name AS kid_middle_name, kid.last_name AS kid_last_name, kid.dob AS kid_dob, kid.profile_pic_small AS kid_profile_pic_small, kid.profile_pic_smallish AS kid_profile_pic_smallish, kid.profile_pic_med AS kid_profile_pic_med, kid.profile_pic_large AS kid_profile_pic_large, kid.gender AS kid_gender, kid.current_group AS kid_current_group, kid.status AS kid_status, kid.status_time AS kid_status_time, kid.potty_trained AS kid_potty_trained, kid.pending AS kid_pending, kid.scholarship AS kid_scholarship, kid.govt AS kid_govt, kid.extra_credit AS kid_extra_credit, kid.hold AS kid_hold, kid.school_id AS kid_school_id, kid.between_schedule_id AS kid_between_schedule_id, kid.one_for_all_schedule_id AS kid_one_for_all_schedule_id, kid.hourly_schedule_id AS kid_hourly_schedule_id, kid.calendar_schedule_id AS kid_calendar_schedule_id, kid.code AS kid_code FROM kid, kids_users

I am trying to populate forms with SomeForm(request.data, obj=some_user), and it never populates the kids select field, because it doesn't return an array.

How do I get it to work nicely in both directions?

Solution

You've used lazy='dynamic', which makes the relationship return another query rather than a collection. To actually get the collection, call some_kid.parents.all(). The dynamic query has the methods append(instance) and remove(instance) to manage the collection. Typically, lazy='dynamic' is used for very large collections where you will mostly filter it further before loading any related instances.

Usually, you just need lazy='select' (the default) which instructs SQLAlchemy to load the collection with an automatic select on access. Then it will behave as a collection like you expect.

Answered By - davidism

Answer Checked By - Clifford M. (PHPFixing Volunteer)