CODEWITHSUNDEEP
pythonpostgresqlfiltersqlalchemy
Slater Victoroff
Slater Victoroff

Reputation: 21914

Filtering by relation in SQLAlchemy

So, here's the situation. I've got a many-to-many field of users and files. Here's the basic code:

owners_datasets = db.Table(
    'owners_datasets',
    db.Column('user_id', db.Integer(), db.ForeignKey(
        'user.id', ondelete='CASCADE')),
    db.Column('dataset_id', db.Integer(), db.ForeignKey(
        'dataset.id', ondelete='CASCADE'))
)

class User(UserJsonSerializer, UserMixin, db.Model):
    id = db.Column(db.Integer, primary_key=True)
    ...

class Dataset(DatasetJsonSerializer, db.Model):
    id = db.Column(db.Integer(), primary_key=True)
    ...
    owners = db.relationship(
        'User',
        secondary=owners_datasets,
        backref=db.backref('collections', lazy='dynamic')
    )

My goal here is just to set up a relation between the Dataset object and the User object. At a later point I want to query datasets for only ones where the current user is on the list of owners. Since this is a many-to-many mapping I'm looking for something like a contains() query, but I can't seem to find anything. This is what I've got thus far:

datasets = Dataset.query.filter_by(Dataset.owners.any(current_user))

which returns the error:

AttributeError: 'User' object has no attribute '_annotate'

I haven't been able to find any reference to this error, and I'm uncertain with how to proceed here.

Using postgres 9.3

Upvotes: 1

Views: 229

Answers (1)

van
van

Reputation: 76992

Dataset.query.filter(Dataset.owners.any(User.id==current_user.id))

Upvotes: 2

Related Questions