[sqlalchemy] Re: ForeignKey + schema
On 9/13/07, Rick Morrison [EMAIL PROTECTED] wrote: SA 0.3* doesn't seem to handle relationships between tables in different schemas very well: it seems to think that schema.A - public.B is: schema.A - schema.B and even specifying primaryjoin= in the mapper won't help it. There seems to be no directive to say use the default / primary schema (I'm using MSSQL, but I've seen very similar q's regarding both PG and Oracle, so I suspect it's not a db-specific issue) Just curious if there's been any work in this area on 0.4 in this area? I've been avoiding jumping in to 0.4 so far, but this could be the issue that gives me the push. I'm not sure about creation, but I've not had any problems using cross-schema foreign keys, relations, joins and so forth using SQLAlchemy 0.3 and PostgreSQL. We're using Elixir (and I had to fix a bug in Elixir's relation management for it to pass things through to SQLAlchemy properly). 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: new Engine document
On 7/6/07, Michael Bayer [EMAIL PROTECTED] wrote: I just wrote this but haven't had time to carefully proof it...if people have corrections / comments let me know. I think this is a lot easier to understand - thanks and well done. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: PROPOSAL: whack query.select(), selectfirst(), selectone(), select_by(), selectfirst_by(), selectone_by(), get_by(), auto-join feature
On 6/3/07, Michael Bayer [EMAIL PROTECTED] wrote: - filter_by() would gain a new positional argument, which is optional, which is either a string or list of strings indicating property names, indicating a join path. when this argument is present, a self-contained join among those mapped properties will be generated, and the keyword criterion of the filter_by() would be expressed against the endpoint of that join. by self-contained, i mean that the tables used in the join will be *aliased* anonymously, so that different filter_by()s which specify join paths that overlap can be used together. the existing behavior of being able to say query.join(['x', 'y', 'z']).filter_by(**kwargs) remains as well (but is different in that it doesnt create aliases). ... - the new positional argument of filter_by() is based on Gaetan's idea, as well as some light that was shed on the IRC channel. it looks like this: session.query(User).filter_by(['orders', 'items', 'keywords'], keyword_name='foo').filter_by(['orders', 'items'], item_price=49.50).list() above, we have created two joins from the users table, one of which joins from 'orders' to 'items' to 'keywords', and the other from 'orders' to 'items'. the two sets of joins are constructed using anonymous aliases, so that you get all users who have purchased an item that has the 'foo' keyword, and have also purchased an item that costs 49.50. the two paths across 'orders' and 'items' are isolated from each other. if aliases were not used above, the redundant 'orders' and 'items' tables would converge into one FROM clause each in the final statement since thats the behavior of select(), but then the semantics of the query would then be all users who purchased items that cost 49.50 and also have the 'foo' keyword. the above capability is currently available by constructing your own Alias object and building join criterion manually. But it was pointed out on IRC that the Django ORM constructs filtering queries using aliases as above. for a lot of cases the approach saves a lot of lines of code spent creating manual aliases. Hi there, I like where things are going, but I think I should clarify how the Django ORM does things to my knowledge. The filter method in the Django ORM only supports keyword args a la filter_by. Every query is based off a mapped object (like Post). Each filter can access other tables' columns via the relations, using a construct like: .filter(jobtitle__content__txt__icontains = keyword, domain__str__content__txt__icontains = keyword) Both jobtitle and domain__str (the domain relation and then the str relation) are of a mapped object Strids type, which maps to the strids table. This has a relation content to a table with contains the actual text to use for the current locale. (__icontains is there to perform a LIKE '%keyword%', since there's no ClauseElement equivalent. I'm not suggesting supporting that.) 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. 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. Not being able to rejoin on the same path may make certain kinds of query building impossible. Then again, using anonymous aliases for each kind can solve different types of problems that can't be done without them. I think the principle of least astonishment would be to not have a new join for the exact same path. But, given enough documentation, one can avoid astonishment. I hope this made the situation clearer, and not more confusing. 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] Re: PROPOSAL: whack query.select(), selectfirst(), selectone(), select_by(), selectfirst_by(), selectone_by(), get_by(), auto-join feature
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 -~--~~~~--~~--~--~---
