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 :( Thanks! -- 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.
