On Jun 4, 2007, at 3:27 AM, Neil Blakey-Milner wrote:
> The joined tables are aliased as the path from the base object. In > this case, the "jobtitle" join is: > > INNER JOIN "strids" AS "positionprofiles__jobtitle" ON > "positionprofiles"."jobtitleid" = "positionprofiles__jobtitle"."id" > > The "domain__str__content" join is: > > INNER JOIN "dimensions" AS "positionprofiles__domain" ON > "positionprofiles"."domainid" = "positionprofiles__domain"."id" INNER > JOIN "strids" AS "positionprofiles__domain__str" ON > "positionprofiles__domain"."strid" = > "positionprofiles__domain__str"."id" INNER JOIN "content" AS > "positionprofiles__domain__str__content" ON > "positionprofiles__domain__str"."id" = > "positionprofiles__domain__str__content"."strid" > > ie, it joins on domain, then str, then content, giving each one an > alias based on the path from the mapped object. those two joins are exclusive to each other (i.e. "strids" has a different alias in the second join than the first). this is the equivalent to what im proposing except i didnt have a deterministic (well, human-readable) naming convention in mind for tha alias names themselves. that naming scheme django is using cant really work anyway since it will very quickly go over the character limit of databases like oracle and firebird. > > I'm a little worried that using anonymous aliases will make some > queries impossible. For example, if you want to list all purchases of > product type B made by a manufacturer with a particular discount > agreement, since one would require a filter_by(['product', 'store'], > ...) and the other will require a filter_by(['product', 'type'], ...). > In the Django ORM, since the path to 'product' is the same, it's the > same join. i think you need to illustrate a clearer example since I dont understand the meaning of "positionprofiles" or "strids" above in relation to the names used in filter() (and what happened to Post?). from what I can see, the alias names are based on where the join is *going*, not where it came from so its not clear to me how this example would be accomplished. 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. --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
