[sqlalchemy] Re: [PATCH] filter_by_via
On 6/2/07, Michael Bayer [EMAIL PROTECTED] wrote: On Jun 2, 2007, at 6:02 AM, Gaetan de Menten wrote: Hmmm, after some more thoughts there is one little aspect of that which bothers me: once you joined to something, you can't add filtering criteria on the initial table/class. This is actually one of the features I disliked about the current code. It might be rare use case but I, for one, like to be able to construct queries in any order, so that I can factor out the common part and store it somewhere then add what is specific at a later point. Here, if the specific part is about the initial table, I'm screwed. Adding a method to just move/reset the joinpoint would solve this, though I find it ugly. Better than nothing though. This would look like this: q = session.query(User).join(['orders', 'items']).filter_by (item_name='foo'). user_query = q.join(['addresses']).filter_by (email_address='[EMAIL PROTECTED]').reset_joinpoint() users = user_query.filter_by(name='Foo').list() yeah i had that idea as well, and yeah its a little ugly. theres also the possiblity of using join(None). let me summarize things that im thinking we do: - we want to go with the joinpoint concept here, where join() starts from the beginning, and join(None)/reset_joinpoint brings it back. I'd personally vote for join(None). Seem pretty logical if join starts from the beginning and doesn't introduce a new method (IMHO there are already too many of them on query objects). join() is used to add a join and also modify the joinpoint of the query, so that you can add more criterion using filter() or filter_by () or others. I think this particuar tweak would probably even be OK to put in the current trunk for release 0.3.8 unless people think its going to create problems...the only backwards-incompatible change being a join() starts from the beginning, not the previous join(). - i think filter_by(['list','of','properties'], **kwargs), i.e. an optional, positional string/list-of-strings argument, should also be present, and it will create the joins and criterion using table aliases, and will not be related to joinpoint at all. apparently django does this, and it would let us define criterion for multiple overlapping paths, such as q.filter_by(['a', 'b, 'c'], d=x).filter_by (['a', 'b', 'e'], d=x). thats something that you cant do with the straight join() alone (but of course you can do with explicit aliases and filter()/select_from()). That'd be pretty nice to have that alias feature, because in that case you could join several times to the same table through different relationships easily. - the auto find me a property behavior is gone. not sure if I want to remove it from select_by() and friends, i think it should probably remain in those in a deprecated state. - ClauseElement support would be removed from filter_by(). you can just use filter() for those. the older _by() methods, which i want to deprecate, would be left alone for backwards compatibility. What do you replace order_by with? - i want to deprecate all the criterion methods that are not filter, i.e. all the selects and most of the gets (except straight get()). selecting from a full statement we can do with query.from_statement (select statement), the argument of which is a select() or a string. deprecating select() and select_by() is to create a single simple interface to query based on the more flexible filter(). but it does mean a bit more typing in many cases. I would hope everyone is OK with that. I'd personally like this but that's probably because I don't use those much. But I think many people are using those so that might be an unpopular move. As such, it would probably deserve a thread on its own, so that people would actually have a chance to react... -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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: [PATCH] filter_by_via
On Jun 3, 2007, at 8:10 AM, Gaetan de Menten wrote: - ClauseElement support would be removed from filter_by(). you can just use filter() for those. the older _by() methods, which i want to deprecate, would be left alone for backwards compatibility. What do you replace order_by with? oh, no we leave order_by() (and group_by()), by _by() i meant the selecting functions like select_by(), selectfirst_by(), etc. - i want to deprecate all the criterion methods that are not filter, i.e. all the selects and most of the gets (except straight get()). selecting from a full statement we can do with query.from_statement (select statement), the argument of which is a select() or a string. deprecating select() and select_by() is to create a single simple interface to query based on the more flexible filter(). but it does mean a bit more typing in many cases. I would hope everyone is OK with that. I'd personally like this but that's probably because I don't use those much. But I think many people are using those so that might be an unpopular move. As such, it would probably deserve a thread on its own, so that people would actually have a chance to react... OK then...though I dont see how we can really keep them. the docs / explanation of query would become so much simpler with only one obvious way to specify filtering criterion / adjustments etc. --~--~-~--~~~---~--~~ 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] PROPOSAL: whack query.select(), selectfirst(), selectone(), select_by(), selectfirst_by(), selectone_by(), get_by(), auto-join feature
This is being discussed in a different thread and it was suggested I put this up in its own brightly lit thread so that people can notice it. now that the Query object in 0.3 has merged an enhanced set of capabilities from the SelectResults extension, namely that it behaves in a generative fashion whereby criteria and other modifiers are applied via filter(), filter_by(), join(), order_by(), etc., its time to create one and preferably only one obvious way to do it for Query. the current documentation at Basic Data Mapping spends a lot of effort explaining the dichotomy between generative and non- generative methods, being able to say query.select() which returns results immediately, or query.filter().list() which allows generation. I spent a lot of time getting that doc to make some semblance of sense but it was pretty clear that the API had become muddied, with a better idea moving in and the older idea seeming kind of redundant. the details of the proposal would be: - the methods select(), selectfirst(), selectone(), select_by(), selectfirst_by(), selectone_by() and get_by() would be deprecated. this means they will remain present on the Query object but the documentation would be reorganized to talk only about filter(), filter_by(), list(), scalar(), and a new method called one() which is like scalar() but ensures that only one row was returned. By deprecating the methods but retaining their exact behavior, the majority of upgrade paths for those still using these methods is painless. the methods would remain present until version 0.5. - the ability to place ClauseElements at the start of filter_by() would be removed. - 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). - for execution of a completely literal text statement, as well as a fully constructed select() object, a method query.from_statement() will be added, and the older select_text() method (that i dont think anyone knows about) will be deprecated like the others. executions would look like query.from_statement(select * from table).scalar() as well as query.from_statement(sometable.select()).list() - the behavior of join() changes slightly, such that each call to join () will reset the joinpoint of the resulting query back to the beginning before creating the join. this means that join() will always build its joins from the original queried class. join(None) resets the joinpoint back to the beginning for subsequent filter()/ filter_by() calls. - assignmapper would also keep all of its current methods with regards to selecting/filtering. it seems like tools like Elixir are going to move away from assignmapper anyway which is a good thing. - the behavior of select_by(), get_by(), join(), filter_by(), and others right now is such that when you name an attribute which is not immediately present on the queried class, a search is performed through all the mapped properties of the class, into its subclasses, until it finds a property of that name, upon which it constructs a join from the initial table to the target table, adding in a comparison criterion for the key selected. this feature would remain in the methods select_by() and the other deprecated _by() methods, but would be removed from filter_by() as well as join(). - for those who have built MapperExtensions into select() and select_by(), we'll put some MapperExtension hooks into list() which is the execution point for queries. - SelectResults remains deprecated until 0.5 when its also removed. and now for the reasons: - the select() methods are now redundant in light of the new generative methods. all of the various flags which you can use with select() are present generatively, i.e. order_by(), limit(), with_lockmode(), filter(), select_from(), etc. By favoring the generative methods we get a more flexible interface and only one way to do it. - by removing the word select from Query's API entirely, the timeless confusion of sql.select()? or query.select()? goes away. the identifier select at last loses its ambiguity. this is a big win. - the multi-dispatch behavior of select() is also little known and confusing. Did you know that select() can take not just a where criterion, but also a
[sqlalchemy] Re: PROPOSAL: whack query.select(), selectfirst(), selectone(), select_by(), selectfirst_by(), selectone_by(), get_by(), auto-join feature
Well, that answers my next N anticipated questions on this list plus
essentially every unasked-yet one in my backlog. These consistency/
predictability-of-syntax/redundancy points cut to the core of every
issue I've had and/or every time I've had to hit the docs for more
than a brief reminder.
+1 oh yeah
On Jun 3, 8:35 am, Michael Bayer [EMAIL PROTECTED] wrote:
{super duper SQLA proclamation}
--~--~-~--~~~---~--~~
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: - the methods select(), selectfirst(), selectone(), select_by(), selectfirst_by(), selectone_by() and get_by() would be deprecated. this means they will remain present on the Query object but the documentation would be reorganized to talk only about filter(), filter_by(), list(), scalar(), and a new method called one() which is like scalar() but ensures that only one row was returned. I'm unsure about this but wouldn't one be redundant with scalar and [0]? Couldn't we have only one of either one or scalar (and have the check there) and otherwise let people use [0]. so..whattaya say ? Except from that little tweak, everything sounds great (you knew my opinion already but I couldn't resist saying it again)! -- Gaëtan de Menten http://openhex.org --~--~-~--~~~---~--~~ 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: - the methods select(), selectfirst(), selectone(), select_by(), selectfirst_by(), selectone_by() and get_by() would be deprecated. this means they will remain present on the Query object but the documentation would be reorganized to talk only about filter(), filter_by(), list(), scalar(), and a new method called one() which is like scalar() but ensures that only one row was returned. +1 - by removing the word select from Query's API entirely, the timeless confusion of sql.select()? or query.select()? goes away. the identifier select at last loses its ambiguity. this is a big win. For this reason. SQLAlchemy has too many ways to do the same thing, and too many ways to access the same object. I would suggest renaming .list() to .all(). It seems funny having a method with the same name and same behavior as list(query) -- I can never decide which to use. There is the concern about building dozens of intermediate query objects that you immediately throw away, but that would be a good target for optimization. For instance, I'm not sure if it clones a query by rebuilding the criteria from scratch, or if it just copies one list of immutable (sharable) objects. If you can guarantee that the existing query won't be used anymore you can just reassign (share) the criteria as single unit, but I guess you can't guarantee that. - assignmapper would also keep all of its current methods with regards to selecting/filtering. it seems like tools like Elixir are going to move away from assignmapper anyway which is a good thing. It would still be worth a separate proposal to reform assignmapper; i.e., delete the query methods .foo() that duplicate MyClass.query().foo(). A lot of non-Exilir people use assignmapper, and it's frustrating that .select() exists but .filter() doesn't, so either add the missing methods or delete the redundant ones. On the other hand, this can be handled in the documentation by emphasizing .query() and deprecating the query methods. -- Mike Orr [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 Jun 3, 2007, at 5:29 PM, Mike Orr wrote: I would suggest renaming .list() to .all(). It seems funny having a method with the same name and same behavior as list(query) -- I can never decide which to use. list() has been around awhile and is also derived from hibernate's similar interface, not sure if this is a good time to change that. There is the concern about building dozens of intermediate query objects that you immediately throw away, but that would be a good target for optimization. For instance, I'm not sure if it clones a query by rebuilding the criteria from scratch, or if it just copies one list of immutable (sharable) objects. If you can guarantee that the existing query won't be used anymore you can just reassign (share) the criteria as single unit, but I guess you can't guarantee that. im giong to look into optimizing the cloning. as ive said, hibernate's criteria object behaves generatively but doesnt actually copy the object; several folks here seem to want the generativeness. ive been considering sneaking in a flag/method that would turn off the generativeness but id have to make sure you dont notice it :). It would still be worth a separate proposal to reform assignmapper; i.e., delete the query methods .foo() that duplicate MyClass.query().foo(). A lot of non-Exilir people use assignmapper, and it's frustrating that .select() exists but .filter() doesn't, so either add the missing methods or delete the redundant ones. filter() and filter_by() were added in 0.3.8. --~--~-~--~~~---~--~~ 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: im giong to look into optimizing the cloning. as ive said, hibernate's criteria object behaves generatively but doesnt actually copy the object; several folks here seem to want the generativeness. ive been considering sneaking in a flag/method that would turn off the generativeness but id have to make sure you dont notice it :). Does that mean returning the results immediately, or modifying the query in place and returning it? Maybe modifying the query in place and returning it isn't such a bad idea after all, considering that this is kind of a special case, having to call so many methods to build up a query. Most other OO systems don't require so many method calls to build up a meaningful object, but most other OO systems are not SQL queries either. And if it provides a way to get away from q = q.filter(...) in favor of q.filter(...), that would be an advantage. It gets tiring assigning the same variable to itself again and again when assignment isn't really the nature of what's going on. It would still be worth a separate proposal to reform assignmapper; i.e., delete the query methods .foo() that duplicate MyClass.query().foo(). A lot of non-Exilir people use assignmapper, and it's frustrating that .select() exists but .filter() doesn't, so either add the missing methods or delete the redundant ones. filter() and filter_by() were added in 0.3.8. Hooray. I've been following the trunk and reading the CHANGELOG but I didn't notice that feature. -- Mike Orr [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: Weakly-referenced object error
No weak-reference error for three days now so it looks like the MySQLdb upgrade cured it. Curious because I've been running other sites sites with that same older version and never gotten that error. But this is the first site that's mulththreaded (Pylons rather than Quixote) so I bet that had something to do with it. -- Mike Orr [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] Left join or subselect syntax(more than one filter on join key)
SQL query for left join which give my desire result is as follow. i can use left join or sub select, though I prefer to use left join but I am Ok with subselect also. I don't know how to write this query in sqlalchemy. I refered document, but my query differes on join key as I have more than one filter on join key. . SELECT R.roleId, R.name, R.description from roles R left join roleElements RE on R.roleId = RE.roleId and RE.isActive = 'Y' and RE.elementId = 1 and RE.elementLevelId = 1 where RE.roleId IS NULL OR SELECT * FROM roles WHERE roleId not in (select roleId from roleElements where elementLevelId = 1 and elementId = 1 and isActive= Y) note: In actual query, values of RE.elementId and RE.elementLevelId would be variable. I really stuck here and tried a lot but couldn't get it. Hopping any help. Thank you in advance. Aruna Kathiriya --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
