This is fantastic, I didn't realise you could define a table using a sql expression. I am always amazed by how amazing and configurable SQLAlchemy is.
Thank you so much for the help with this. PS. I am a huge fan. Please give Dilly a pat for me =). On Saturday, October 21, 2017 at 5:14:28 AM UTC+2, Mike Bayer wrote: > > note I inadvertently used a previous form for the first GroupProfile > example. The two forms are: > > > class GroupProfile(Base): > __table__ = ( > select([Group.group_id, Profile]). > select_from(join(Group, ProfileGroupRole).join(Profile)). > distinct().alias() > ) > ... > > and the potentially more efficient one: > > distinct_profile_groups = select( > [ProfileGroupRole.group_id, ProfileGroupRole.profile_id]).\ > distinct().alias() > > > class GroupProfile(Base): > # the GroupProfile itself is then based on Group/Profile rows > # joined to our distinct group_id/profile_id pairs > __table__ = ( > select([Group.group_id, Profile]). > select_from(join(Group, distinct_profile_groups).join(Profile)). > alias() > ) > > > > The second form applies DISTINCT to only two columns, whereas the > first applies DISTINCT to all columns in Profile, which can be > wasteful. A third form could use Postgresql DISTINCT ON to limit the > scope of the DISTINCT without using a second subquery. > > > -- 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.
