alphanumeric alphanumeric - 21 days ago 5
Python Question

How to query Model using SQLAlchemy by ID

There are two tables: "User" and "Project" defined as follows:

create table user (
id integer primary key autoincrement
);

create table project (
id integer primary key autoincrement,
user_id integer
);


And the third table is used to map users to projects ids:

create table users_to_projects_table (
user_id integer,
project_id integer
);


In Python I declare the relation table first:

users_to_projects_table = db.Table('users_to_projects_table',
db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
db.Column('project_id', db.Integer, db.ForeignKey('project.id'))
)


Then I proceed by creating classes for
Project
and
User
tables:

class User(db.Model):
id = db.Column(db.Integer, primary_key=True)
projects = db.relationship('Project', backref='user', lazy='dynamic')


class Project(db.Model):
id = db.Column(db.Integer, primary_key=True)
user_id = db.Column(db.Integer, db.ForeignKey('user.id'), nullable=False)

_users = db.relationship('User', secondary=users_to_projects_table, backref=db.backref('users_to_projects_backref', lazy='dynamic'))

@property
def users(self):
return self._users

@users.setter
def users(self, user_ids):
self._users = [User.query.filter_by(id=user_id).one() for user_id in user_ids]


Now when it is all done I go ahead and create
user
:

user = User()
db.session.add(user)
db.session.commit()


Next I create the
project
by passing it
user.id
as an argument:

project = Project( users=[user.id] )
db.session.add(project)
db.session.commit()


Everything looks good. I can see that both
user
and
project
were written to database. And that the
users_to_projects_table
was properly updated with both project_id and associated with it user_id.

Question: Now with
users_to_projects_table
"knowing" which user_id corresponds to which
project_id
how do I query which users are linked to which Project?
The command below fails with
InvalidRequestError
:

project_id = 1
User.projects.query.filter_by(project_id=project_id)


Error is below:

InvalidRequestError: Entity '<class '_app.User'>' has no property 'project_id'


What needs to be changed in order make it work?

Answer

You need to set secondary=users_to_projects_table on the projects relationship.

A possible query would be User.query.filter(User.projects.any(id=project_id)), although it would probably be better to use a join.

A potentially faster (albeit longer) query using a join:

User.query.select_from(User).join(users_to_projects_table).join(Project).filter(Project.id==project_id