[sqlalchemy] Re: Selecting an ORM from self JOINs and/or from a query with subqueries
Alexander Kotelnikov wrote: Assuming I have a table CREATE TABLE seq (i int auto_increment primary key, used bool default false); And want to query an mapped object from it which corresponds a query SELECT i,used FROM seq WHERE NOT used AND i+1 IN (SELECT i FROM seq WHERE AND used) or (more or less equivalent) SELECT seq.i,seq.used FROM seq JOIN seq AS seq1 WHERE seq.i+1==seq1.i AND NOT seq.used AND NOT seq1.used (retrieve first of n, here - 2, not used numbers in a sequence) Number of subqueries/JOINs might be a runtime parameter. Would be nice to know if it is possible, and if it is - how? you can map to any select(), but since the statement here is a runtime thing just map to the seq table normally and use Query as needed to construct the joins and filter criterion. If you're looking to automate adding N joins, just build a function that calls query.join() the appropriate number of times. For an example of a completely different use case where a self-referential query.join() is being called an arbitrary number of times, see the elementtree/optimized_al.py example in the distribution. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Selecting an ORM from self JOINs and/or from a query with subqueries
Michael, do you mean, that subqueries could not be wrapped into sqlalchemy? what should be the arguments to join() I failed to figure out? how do I reference different instances of seq in fileter() after? Thanks, A On Jun 19, 7:43 am, Michael Bayer mike...@zzzcomputing.com wrote: you can map to any select(), but since the statement here is a runtime thing just map to the seq table normally and use Query as needed to construct the joins and filter criterion. If you're looking to automate adding N joins, just build a function that calls query.join() the appropriate number of times. For an example of a completely different use case where a self-referential query.join() is being called an arbitrary number of times, see the elementtree/optimized_al.py example in the distribution. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Selecting an ORM from self JOINs and/or from a query with subqueries
sacha wrote: Michael, do you mean, that subqueries could not be wrapped into sqlalchemy? you talked about mapping to a select statement. mapping means this: m = mapper(MyClass, someselectable) mapping like the above is usually done against individual tables, and usually once per class per application. You can do it against select() statements but this is usually unnecessary. You can also make multiple mappers for a single class in an ad-hoc way, but again this is an ancient use case that is much better addressed by using the Query object as needed. what should be the arguments to join() I failed to figure out? how do I reference different instances of seq in fileter() after? Usually you use query.join(). Self referential queries require an alias for each join target. There is an example at http://www.sqlalchemy.org/docs/05/ormtutorial.html#using-aliases . Also the example I mentioned earlier in examples/elementtree/optimized_ai.py shows exactly a dynamically-constructed self-referential join. Thanks, A On Jun 19, 7:43 am, Michael Bayer mike...@zzzcomputing.com wrote: you can map to any select(), but since the statement here is a runtime thing just map to the seq table normally and use Query as needed to construct the joins and filter criterion. If you're looking to automate adding N joins, just build a function that calls query.join() the appropriate number of times. For an example of a completely different use case where a self-referential query.join() is being called an arbitrary number of times, see the elementtree/optimized_al.py example in the distribution. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Selecting an ORM from self JOINs and/or from a query with subqueries
I really want to get a clear vision. So, I have a table x and mapped class X. I can use query(X) for simple queries, can I query(X) for structured ones like SELECT * FROM x WHERE x.a IN (SELECT ) ? Same about multi-cartesian product can I use query(X).join() for SELECT * FROM x JOIN x JOIN x . ? All examples for the latter involve additional tables. On Jun 19, 12:03 pm, Michael Bayer mike...@zzzcomputing.com wrote: sacha wrote: Michael, do you mean, that subqueries could not be wrapped into sqlalchemy? you talked about mapping to a select statement. mapping means this: m = mapper(MyClass, someselectable) mapping like the above is usually done against individual tables, and usually once per class per application. You can do it against select() statements but this is usually unnecessary. You can also make multiple mappers for a single class in an ad-hoc way, but again this is an ancient use case that is much better addressed by using the Query object as needed. what should be the arguments to join() I failed to figure out? how do I reference different instances of seq in fileter() after? Usually you use query.join(). Self referential queries require an alias for each join target. There is an example athttp://www.sqlalchemy.org/docs/05/ormtutorial.html#using-aliases. Also the example I mentioned earlier in examples/elementtree/optimized_ai.py shows exactly a dynamically-constructed self-referential join. Thanks, A On Jun 19, 7:43 am, Michael Bayer mike...@zzzcomputing.com wrote: you can map to any select(), but since the statement here is a runtime thing just map to the seq table normally and use Query as needed to construct the joins and filter criterion. If you're looking to automate adding N joins, just build a function that calls query.join() the appropriate number of times. For an example of a completely different use case where a self-referential query.join() is being called an arbitrary number of times, see the elementtree/optimized_al.py example in the distribution. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Selecting an ORM from self JOINs and/or from a query with subqueries
I meant SELECT x.* FROM x JOIN x AS x1 JOIN x AS x2 ... WHERE On Jun 19, 1:55 pm, sacha sa...@myxomop.com wrote: Same about multi-cartesian product can I use query(X).join() for SELECT * FROM x JOIN x JOIN x . ? --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Selecting an ORM from self JOINs and/or from a query with subqueries
sacha wrote: I really want to get a clear vision. So, I have a table x and mapped class X. I can use query(X) for simple queries, can I query(X) for structured ones like SELECT * FROM x WHERE x.a IN (SELECT ) sel = session.query(X.a).filter(X.b=='foo') session.query(X).filter(X.a.in_(sel)) Same about multi-cartesian product can I use query(X).join() for SELECT * FROM x JOIN x JOIN x . ? for self-referential, use aliases as follows: x1 = aliased(X) x2 = aliased(X) x3 = ... session.query(X).join((x1, X.somerelation), (x2, x1.somerelation), ...) or session.query(X).join((x1, X.some_id==x1.some_other_id), (x2, x1.some_id==x2.some_other_id), ...) anything can be in query() too, i.e. query(X, x1, x2.foo, ...) then .filter(X.foo=='bar').filter(x1.bar=='bat').filter(x2.hoho=='lala') etc. On Jun 19, 12:03 pm, Michael Bayer mike...@zzzcomputing.com wrote: sacha wrote: Michael, do you mean, that subqueries could not be wrapped into sqlalchemy? you talked about mapping to a select statement. mapping means this: m = mapper(MyClass, someselectable) mapping like the above is usually done against individual tables, and usually once per class per application. You can do it against select() statements but this is usually unnecessary. You can also make multiple mappers for a single class in an ad-hoc way, but again this is an ancient use case that is much better addressed by using the Query object as needed. what should be the arguments to join() I failed to figure out? how do I reference different instances of seq in fileter() after? Usually you use query.join(). Self referential queries require an alias for each join target. There is an example athttp://www.sqlalchemy.org/docs/05/ormtutorial.html#using-aliases. Also the example I mentioned earlier in examples/elementtree/optimized_ai.py shows exactly a dynamically-constructed self-referential join. Thanks, A On Jun 19, 7:43 am, Michael Bayer mike...@zzzcomputing.com wrote: you can map to any select(), but since the statement here is a runtime thing just map to the seq table normally and use Query as needed to construct the joins and filter criterion. If you're looking to automate adding N joins, just build a function that calls query.join() the appropriate number of times. For an example of a completely different use case where a self-referential query.join() is being called an arbitrary number of times, see the elementtree/optimized_al.py example in the distribution. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
