On Tue, May 22, 2018 at 10:06 PM, <chbrn...@gmail.com> wrote:
> 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)
you can also say:
sess.query(User, Articles,
ReadArticles).select_from(Articles).outerjoin(ReadArticles,
...).outerjoin(User, ...)
>
> 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, chbr...@gmail.com 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, <chbr...@gmail.com> 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 sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
