for guidance on how the flag is added, look at the "innerjoin=True" flag on joinedload() / relationship(). On Thu, Sep 20, 2018 at 10:30 AM Mike Bayer <mike...@zzzcomputing.com> wrote: > > you would need to change the code here: > > https://github.com/zzzeek/sqlalchemy/blob/master/lib/sqlalchemy/orm/strategies.py#L1914 > > however, you can't just change the query to unconditionally not > include the parent table, because you don't know what the > "primaryjoin" is. for this to be automatic, the primaryjoin would > need to be traversed and tested to ensure it is a direct comparison of > FK to PK col and that the FK values can be substituted directly. > > Here's a primaryjoin that *wont* work, for example: > > https://github.com/zzzeek/sqlalchemy/blob/master/test/orm/test_relationships.py#L3017 > > to make it easy, your optimized query can be switched on a user-set > flag, rather than attempting to guess. like selectinload(Foo.bar, > omit_join=True) / relatonship(..., lazy="selectinload", > omit_join=True). That would be vastly easier to test and release > with no chance of surprising anyone. > > > > > On Thu, Sep 20, 2018 at 9:08 AM Jayson Reis <santosdosr...@gmail.com> wrote: > > > > Hi there everyone. > > > > I would like to ask if there is a way to change how selectinload run its > > query. > > > > I put an example [1] that will show you sort of how my data is structured > > so you can have an idea, but the gist is: > > > > > > I have a main table (like customers) and another table (like order) which > > has the detailed data and then I created a view where summarizes customer’s > > orders. > > > > > > The problem is that I am using selectinload and it runs the query on the > > view joining customer’s table and PostgreSQL won’t be able to use the > > proper indexes and will run it slowly. Is there a way to make SQLAlchemy > > just query the view (without the join) as it already has the foreign key? > > > > > > Here [2] you can see the output of the example with explain analyze of both > > possible queries. > > > > > > When I run query(User).options(selectinload(SummarizedOrder)).all() it will > > run the query like this: > > > > SELECT “fields” > > FROM "user" AS "user_1" > > JOIN "view_summarized_order" ON "user_1"."id" = > > "view_summarized_order"."user_id" > > WHERE "user_1"."id" IN (%(primary_keys_1) s, %(primary_keys_2) s) > > ORDER BY "user_1"."id" > > > > > > But to make it faster, it could be this: > > > > SELECT "fields" > > > > FROM "view_summarized_order" > > > > WHERE "view_summarized_order"."user_id" IN (%(primary_keys_1) s, > > %(primary_keys_2) s) > > > > ORDER BY "view_summarized_order"."user_id" > > > > > > In my production database, the first one will take around 8 seconds to run > > and the second one around 100ms. > > > > > > If it possible to do but a PR is needed, could you point out the places > > that would need to be changed so I try to come up with a test and send a > > patch afterwards? > > > > > > Thank you in advance, > > > > Jayson > > > > > > > > [1] https://gist.github.com/jaysonsantos/e19af47ac5d57aa5e2e2a7ed2a950994 > > > > [2] > > https://gist.github.com/jaysonsantos/e19af47ac5d57aa5e2e2a7ed2a950994#file-2_output-txt > > > > -- > > 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.
