Figured out what I had wrong, I was trying to use the second table in the list as the basis, but the order dictates how SQLAlchemy handles it.
So: q = db.session.query(User, Articles, ReadArticles) should be: q = db.session.query(Articles, User, ReadArticles) and q = q.filter(User.id == 1).outerjoin(ReadArticles, ReadArticles.user_id == 1).all() should be q = q.filter(User.id == 1).outerjoin(ReadArticles, ReadArticles.article_id == Articles.id).all() to achieve what I was trying: (<Articles 2>, <User 1>, <ReadArticles 1>) (<Articles 1>, <User 1>, <ReadArticles 2>) (<Articles 3>, <User 1>, None) On Tuesday, May 22, 2018 at 6:25:29 PM UTC-5, [email protected] wrote: > > Simon, > > Thank you, I've tried a few things and am still a bit confused. > > I've reconfigured the table classes a bit, here is what I'm using > currently to try to grok this: > > class User(db.Model): > id = db.Column(db.Integer, primary_key=True) > username = db.Column(db.String(64), index=True, unique=True, > nullable=False) > > > class Articles(db.Model): > id = db.Column(db.Integer, primary_key=True) > article_name = db.Column(db.String(100), unique=True, nullable=False) > > > class ReadArticles(db.Model): > id = db.Column(db.Integer, primary_key=True) > user_id = db.Column(db.Integer, db.ForeignKey('user.id'), > nullable=False) > article_id = db.Column(db.Integer, db.ForeignKey('articles.id'), > nullable=False) > > I'm confused about two things, if I use this approach: > > q = db.session.query(User, Articles, ReadArticles) > q = q.filter(User.id == 1).outerjoin(ReadArticles, ReadArticles.user_id == > 1).all() > > what's return in q is a set of tuples (what I expected), but I receive > what seem to be duplicate rows. However many rows in ReadArticles have the > specified user_id each row that is returned is multiplied that many times. > So if I have 3 items in Articles, but only two of them match in > ReadArticles with an article_id matching that particular user_id I will > receive two rows for each row in Articles. Here is an example of the > tuples: > > for row in q: > print(row) > > (<User 1>, <Articles 1>, <ReadArticles 1>) > (<User 1>, <Articles 1>, <ReadArticles 2>) > (<User 1>, <Articles 2>, <ReadArticles 1>) > (<User 1>, <Articles 2>, <ReadArticles 2>) > (<User 1>, <Articles 3>, <ReadArticles 1>) > (<User 1>, <Articles 3>, <ReadArticles 2>) > > Article 3 (Articles.id = 3) does not match in the ReadArticles table, just > the ones matching id 1 and 2. > > Am I missing something in my query that would simply return 1 row for each > item and None if the user_id and article_id aren't in the same row in > ReadArticles? > > To this end I tried using an and_ but think I don't have the syntax > correct (well I know I don't, since it throws an error). Here is what I > tried, perhaps I'm approaching it correctly but don't know the proper way > to perform this? Would it return the behavior I described or still return > multiple rows? > > Here is the query I'm trying and an abbreviated form of the error: > > r = db.session.query(User, Articles, ReadArticles).outerjoin(ReadArticles, > and_(User.id == ReadArticles.user_id, Articles.id == > ReadArticles.article_id)).all() > > And the error seems to be: 1054, "Unknown column 'articles.id' in 'on > clause'" (giving me a reference to the SQLAlchemy docs > http://sqlalche.me/e/2j85 stating simply that I've got an internal error) > > Any help (and especially pointers to explanations so I can understand) > very appreciated! > Thanks! > > On Tuesday, May 1, 2018 at 10:05:04 AM UTC-5, Simon King wrote: >> >> On Tue, May 1, 2018 at 2:35 PM, <[email protected]> wrote: >> > I'm using the PrettyPrinted tutorial on Many to Many relationships with >> > Flask-SQLAlchemy however what I'm trying to figure out he doesn't go >> into in >> > the tutorial and I haven't had much luck in _Essential SQLAlchemy_ or >> > StackOverflow finding a solution. >> > >> > So for his example he has two main tables, Channel and User, and then a >> > relationship table to create the many to many relationship. >> > >> > What I'm trying to do is to use an outerjoin (or whatever would work >> for >> > this solution in SQLAlchemy's ORM) to retrieve a list of all of the >> entries >> > in Channel but filter it so if a certain user id is 'subscribed' (has a >> > relationship in the relationship table) then it will return that ID as >> well, >> > or a calculated column/alias (however is best in SQLAlchemy's ORM and >> most >> > Pythonic). >> > >> > At the moment the only way I'm able to accomplish this requires two >> queries, >> > one for all channels, and one for all the channels that the particular >> user >> > is subscribed to. Then combining them with a comprehension. While the >> > comprehension is Pythonic the double querying seems like a bad >> practice, and >> > also not very Pythonic. >> > >> > I'm using MySQL for the database if that helps, but I'm guessing >> whatever >> > the solution is it will be agnostic to the database due to the ORM? >> > >> > Here is the video: https://youtu.be/OvhoYbjtiKc >> > >> > So the DB has a Channel table, with an ID and Name, a User table with >> ID and >> > Name, and a 'subs' table (subscriptions) to manage the relationships >> that >> > has a foreign key for the ID in each of the other two tables. >> > >> > I've tried several methods using the outerjoin() from SQLAlchemy but it >> > doesn't seem to limit it in the way I'm trying. >> > >> > Here is the join that I've had the most luck with (but again, doesn't >> limit >> > it, it just returns all the channels, then in each channel if you look >> at >> > the User relationships you just see ALL the users subsribed to that >> channel, >> > not just the one I try to search for). Below that is the code for the >> > tables: >> > >> > chan_query = Channel.query.outerjoin(User, User.user_id==1).all() >> > >> > which returns all the rows in Channel (desired) but nothing to indicate >> if >> > the selected user is subscribed to the particular row/Channel, just a >> list >> > of ALL the users identified with that row, same for each of them. >> > >> > Code for the tables: >> > --- >> > >> > subs = db.Table('subs', >> > db.Column('user_id', db.Integer, db.ForeignKey('user.user_id')), >> > db.Column('channel_id', db.Integer, >> db.ForeignKey('channel.channel_id')) >> > ) >> > >> > >> > class User(db.Model): >> > user_id = db.Column(db.Integer, primary_key=True) >> > name = db.Column(db.String(20)) >> > subscriptions = db.relationship('Channel', secondary=subs, >> > backref=db.backref('subscribers', lazy='dynamic')) >> > >> > >> > class Channel(db.Model): >> > channel_id = db.Column(db.Integer, primary_key=True) >> > channel_name = db.Column(db.String(20)) >> > --- >> > >> > Any help is greatly appreciated, feel like I'm just stuck on this and >> don't >> > even know how to search to find the solution :( >> > >> >> You want to return 2 pieces of information in each row: the channel, >> and a flag that indicates whether the user subscribes. The best way to >> do that is to use the "session.query()" method. This is the more >> general version of your "Channel.query" function from >> Flask-SQLAlchemy. >> >> There are probably many different ways of expressing the query you >> want in SQL. I think of it as needing an inner join between "user" and >> "subs", with an extra condition restricting it to the user you are >> interested in. This is then outer-joined to the "channel" table, so >> that you still get channels that the user hasn't subscribed to. That >> would look something like this: >> >> userjoin = saorm.join( >> User, subs, >> sa.and_(subs.c.user_id == User.user_id, >> User.name == 'joe'), >> ) >> q = (session.query(Channel, User.user_id) >> .outerjoin(userjoin)) >> >> ...which produces this SQL (reformatted): >> >> SELECT channel.channel_id AS channel_channel_id, >> channel.channel_name AS channel_channel_name, >> user.user_id AS user_user_id >> FROM channel >> LEFT OUTER JOIN ( >> user JOIN subs ON subs.user_id = user.user_id AND user.name = ? >> ) ON channel.channel_id = subs.channel_id >> >> >> Here's a full working example: >> >> ############################################################ >> import sqlalchemy as sa >> import sqlalchemy.orm as saorm >> from sqlalchemy.ext.declarative import declarative_base >> Base = declarative_base() >> >> subs = sa.Table( >> 'subs', Base.metadata, >> sa.Column('user_id', sa.Integer, sa.ForeignKey('user.user_id')), >> sa.Column('channel_id', sa.Integer, >> sa.ForeignKey('channel.channel_id')), >> ) >> >> >> class User(Base): >> __tablename__ = 'user' >> user_id = sa.Column(sa.Integer, primary_key=True) >> name = sa.Column(sa.String(20)) >> subscriptions = saorm.relationship( >> 'Channel', >> secondary=subs, >> backref=saorm.backref('subscribers', lazy='dynamic'), >> ) >> >> >> class Channel(Base): >> __tablename__ = 'channel' >> channel_id = sa.Column(sa.Integer, primary_key=True) >> channel_name = sa.Column(sa.String(20)) >> >> >> if __name__ == '__main__': >> engine = sa.create_engine('sqlite:///:memory:') >> Base.metadata.create_all(engine) >> sessionmaker = saorm.sessionmaker(bind=engine) >> >> session = sessionmaker() >> joe = User(name='joe') >> bob = User(name='bob') >> >> foo = Channel(channel_name='foo') >> bar = Channel(channel_name='bar') >> baz = Channel(channel_name='baz') >> >> joe.subscriptions = [foo, bar] >> bob.subscriptions = [bar, baz] >> >> session.add_all([joe, bob, foo, bar, baz]) >> session.commit() >> >> userjoin = saorm.join( >> User, subs, >> sa.and_(subs.c.user_id == User.user_id, >> User.name == 'joe'), >> ) >> q = (session.query(Channel, User.user_id) >> .outerjoin(userjoin)) >> >> print q >> >> for (channel, userid) in q.all(): >> print channel.channel_name, userid >> >> ############################################################ >> >> Hope that helps, >> >> Simon >> > -- SQLAlchemy - The Python SQL Toolkit and Object Relational Mapper http://www.sqlalchemy.org/ To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description. --- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
