On 6/4/07, Michael Bayer <[EMAIL PROTECTED]> wrote: > anyway, the filter on product/store and product/type example you just > mention presents a third scenario, which I wouldnt consider to fall > under the filter_by([attributes]) functionality, it would fall under > the regular join() call which creates joins *without* aliasing. but, > its not supported by the current attribute-joining functionality, > because it asks to generate two joins from a common joinpoint that is > *not* the root. the two ideas with regards to join() are the current > way (join() moves the joinpoint which then never recedes) and the > newer way (join() starts from the beginning each time). > > I just tried out an example of "rejoining from the middle" with the > 0.4 code, with a mapping of User->Order->Keywords, Items > > create_session().query(User).join(['orders', 'items']).filter_by > (id=5).join(['orders','keywords']).filter_by(id=7).list() > > and I got this: > > SELECT users.id AS users_id \nFROM users JOIN orders ON users.id = > orders.user_id JOIN items ON orders.id = items.order_id JOIN orders > ON users.id = orders.user_id JOIN keywords ON orders.id = > keywords.order_id \nWHERE ((items.id = ?) AND (keywords.id = ?)) > ORDER BY users.oid > > which is wrong, it joined on 'orders' twice (throws SQL error). but > it seems like if join() were made a little smarter to see that > 'orders' was already in there, which is pretty much required here > since throwing an error is the least desireable option, youd get: > > SELECT users.id AS users_id \nFROM users JOIN orders ON users.id = > orders.user_id JOIN items ON orders.id = items.order_id JOIN keywords > ON orders.id = keywords.order_id \nWHERE ((items.id = ?) AND > (keywords.id = ?)) ORDER BY users.oid > > where you can see the JOIN keywords is joining against the previous > 'orders' table. I think that would work here and would be the > approach for that scenario.
Great! That's basically what I was trying to say. Sorry that I can't convey it in a more useful form. What do you think of me putting up the full pathological-case code from Django ORM and SQLAlchemy on the SQLAlchemy wiki, and maybe see if there are improvements to be found for that as well (since it uses ILIKE and not equality and a bunch of AND and OR statements, the new filter_by improvements can't be used for it). Just need to whittle it down to the bare minimum in terms of tables and code first. A bunch of real-life queries and the best code to achieve them would be invaluable on the wiki. Then, when changes to the API are discussed, the examples can serve as a baseline for the types of things that are being made easier or harder. Neil -- Neil Blakey-Milner http://nxsy.org/ [EMAIL PROTECTED] --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
