from SQL syntax to SQLAlchemy syntax

Question

I am trying to "translate" the following SQL statement to SQLalchemy in flask:

select subs.user_id, subs.magazine_id, article.title from article join subs on subs.magazine_id=article.magazine_id and subs.user_id=2;

It works correctly in a mysql shell but when I try to make it work from a plas app I completely fail. Let me explain what I am trying to do: I have three different classes of objects: users, magazines, and articles. A user can be subscribed to many magazines. All articles belong to a unique magazine. Below are the models I use:

class User(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column( db.String(64) )
    subscriptions= db.relationship('Magazine', secondary=subs, backref=db.backref('subscribers', lazy='dynamic'), lazy='dynamic')


class Magazine(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64))
    articles= db.relationship('Article', backref='magazine', lazy='dynamic')


class Article(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String(64))
    abstract = db.Column(db.String(64))
    magazine_id= db.Column(db.Integer, db.ForeignKey('magazine.id'))

subs = db.Table('subs', 
    db.Column('user_id', db.Integer, db.ForeignKey('user.id')),
    db.Column('magazine_id', db.Integer, db.ForeignKey('magazine.id'))
)

The subs table is there to keep record of the subscriptions.What I need to do is, for a given user, retrieve all the articles of the magazines to which she is subscribed.

I know how to make it work in python, let's say user1 is a user:


articles=[]
for m in user1.subscriptions:
    articles+=m.articles.all()

But I need to do it from SQLAlchemy because I want to filter the articles (read/unread, date of publication, themes ecc) and paginate the results. I can do it from a mysql shell with the statement at the beginning of the post but I fail misarebly when trying to do it from SQLAlchemy:

user1.subscriptions.join(Article).filter(Article.magazine_id==id).all()

gives the error:

AttributeError: 'builtin_function_or_method' object has no attribute 'translate'

Can you please post a longer stacktrace? I also suspect you want Aritcle.c.magazine_id to refer to a column in a result inside the filter, not the column definition in the model. — 9000
– 9000, Commented Oct 2, 2019 at 17:06
What's id? Are you sure you're not passing the builtin function of the same name? And why would you filter articles by magazine id at all after joining them to magazines? — Ilja Everilä
– Ilja Everilä, Commented Oct 2, 2019 at 17:27

Douglas Leister · Accepted Answer · 2019-10-03 01:42:37Z

1

If you are looking for the result to return all articles from magazines to which the user is subscribed, try structuring the search starting from the Article. Putting Magazine.subscribers in the join clause should negate the need to directly reference the subs table in the query.

userArticles = db.session.query(Article).join(Article.magazine, Magazine.subscribers).filter(User.id==tbdID).all()

answered Oct 3, 2019 at 1:42

Douglas Leister

1918 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user1862963 Over a year ago

Thanks a lot douglas, I am really lost with SQLAlchemy. As soon as I have some time I have to go deep in it. Let me ask you something else: what if I want all the subscribers to the magazines I am subscribed to? I have hit another dead end here!

Douglas Leister Over a year ago

try a subquery: sub = db.session.query(Magazine).join(Magazine.subscribers).filter(User.id ==id).subquery() subscribers = db.session.query(User).select_from(sub).join(Magazine.subscribers).all()

user1862963 Over a year ago

I am experiencing a strange behaviour of "paginate" when paginating "subscribers". Can it be because of the subquery? I have posted the problem here: stackoverflow.com/questions/58282132/…

Epion · Accepted Answer · 2019-10-02 20:01:39Z

0

If you made subs into a regular class, you could have success with the following query:

session.query(Subs.user_id, Subs.magazine_id, Article.title)
.join(Article, Subs.magazine_id == Article.magazine_id)
.filter(Subs.user_id == 2)

answered Oct 2, 2019 at 20:01

Epion

5183 silver badges8 bronze badges

Collectives™ on Stack Overflow

from SQL syntax to SQLAlchemy syntax

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related