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.
