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] <javascript:>> 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.
