[sqlalchemy] Re: Single row of a One to Many query
On Jul 24, 7:19 pm, Michael Bayer mike...@zzzcomputing.com wrote:
[..]
finding a list of objects and the most recent/highest/somethingest
related item requires joining to a subquery, where the subquery selects
the func.max(desiredfield) and GROUP BY's the columns that relate the rows
to the parent.
An example of that is in the ORM tutorial under Using Subqueries.
Replace func.count() with func.max() and you're on your way.
I'm sorry about such a late follow up. I ended up working on something
else and couldn't try this out.
I spent a while trying to crack this but couldn't.
My mappers are Client and Order and I first create the subquery like
so.
stmt = session.query(Order.table.c.client_id,func.max
(Order.table.c.date).label('latest_order')).group_by
(Order.table.c.date).subquery()
Which selects the order with the maximum date.
I then build my complete query like so
for client, lo in session.query(Client, stmt.c.latest_order).filter
(Client.id == 16983).join((stmt,Client.id ==
stmt.c.client_id)).order_by(Client.id): print client,lo
(The filter is there just to limit the clients to a single one)
I get back a all the orders for the client which seems to make sense
from the SQL standpoint but it's not what I want.
I get back something like
Client#16983 date0
Client#16983 date1
Client#16983 date2
which are the 3 orders that the client has placed.
What I want is the latest one only. ie.
Client#16983 date2
Can't I get just these?
Thanks.
--~--~-~--~~~---~--~~
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: Single row of a One to Many query
On Thu, Sep 3, 2009 at 8:05 AM, Noufal nou...@gmail.com wrote:
stmt = session.query(Order.table.c.client_id,func.max
(Order.table.c.date).label('latest_order')).group_by
(Order.table.c.date).subquery()
I think your group_by needs to be Order.table.c.client_id to get latest
order per client
--
Mike Conley
--~--~-~--~~~---~--~~
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] http://www.sqlalchemy.org/docs/05/ormtutorial.html
Im trying to run through this tutorial
http://www.sqlalchemy.org/docs/05/ormtutorial.html.
After running.
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class User(Base):
... __tablename__ = 'users'
...
... id = Column(Integer, primary_key=True)
... name = Column(String)
... fullname = Column(String)
... password = Column(String)
...
... def __init__(self, name, fullname, password):
... self.name = name
... self.fullname = fullname
... self.password = password
...
... def __repr__(self):
...return User('%s','%s', '%s') % (self.name,
self.fullname, self.password)
I got an error.
Traceback (most recent call last):
File ipython console, line 1, in module
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.5-
py2.6.egg/sqlalchemy/ext/declarative.py, line 546, in __init__
_as_declarative(cls, classname, dict_)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.5-
py2.6.egg/sqlalchemy/ext/declarative.py, line 539, in _as_declarative
cls.__mapper__ = mapper_cls(cls, table, properties=our_stuff,
**mapper_args)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.5-
py2.6.egg/sqlalchemy/orm/__init__.py, line 749, in mapper
return Mapper(class_, local_table, *args, **params)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.5-
py2.6.egg/sqlalchemy/orm/mapper.py, line 198, in __init__
self._configure_pks()
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.5-
py2.6.egg/sqlalchemy/orm/mapper.py, line 439, in _configure_pks
key columns for mapped table '%s' % (self,
self.mapped_table.description))
ArgumentError: Mapper Mapper|User|users could not assemble any primary
key columns for mapped table 'users'
Any ideas why this happens?
rgds,
Paulo
--~--~-~--~~~---~--~~
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: http://www.sqlalchemy.org/docs/05/ormtutorial.html
not much idea. I'd make sure its not iPython screwing things up, I seem
to recall people having issues with it for some reason (which of course
seems ridiculous)
paulo wrote:
Im trying to run through this tutorial
http://www.sqlalchemy.org/docs/05/ormtutorial.html.
After running.
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class User(Base):
... __tablename__ = 'users'
...
... id = Column(Integer, primary_key=True)
... name = Column(String)
... fullname = Column(String)
... password = Column(String)
...
... def __init__(self, name, fullname, password):
... self.name = name
... self.fullname = fullname
... self.password = password
...
... def __repr__(self):
...return User('%s','%s', '%s') % (self.name,
self.fullname, self.password)
I got an error.
Traceback (most recent call last):
File ipython console, line 1, in module
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.5-
py2.6.egg/sqlalchemy/ext/declarative.py, line 546, in __init__
_as_declarative(cls, classname, dict_)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.5-
py2.6.egg/sqlalchemy/ext/declarative.py, line 539, in _as_declarative
cls.__mapper__ = mapper_cls(cls, table, properties=our_stuff,
**mapper_args)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.5-
py2.6.egg/sqlalchemy/orm/__init__.py, line 749, in mapper
return Mapper(class_, local_table, *args, **params)
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.5-
py2.6.egg/sqlalchemy/orm/mapper.py, line 198, in __init__
self._configure_pks()
File /usr/local/lib/python2.6/dist-packages/SQLAlchemy-0.5.5-
py2.6.egg/sqlalchemy/orm/mapper.py, line 439, in _configure_pks
key columns for mapped table '%s' % (self,
self.mapped_table.description))
ArgumentError: Mapper Mapper|User|users could not assemble any primary
key columns for mapped table 'users'
Any ideas why this happens?
rgds,
Paulo
--~--~-~--~~~---~--~~
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] records not appearing for a short period of time
Hey guys, I've had a really hard time with this issue and I'm hoping you can tell me that I just have a caching issue (or something similar). I have a system that manages related life cycle records. There is a one to many relationship between a parent life cycle and many child life cycles. Once all the child life cycles are marked as complete then the parent life cycle may transition to the next state. Here's an example. I have one parent life cycle that hasn't transitioned. Every few minutes I run a query against the database and essentially take a count of child records that aren't done processing. If that number is zero then I transition the parent life cycle to its next state. The query would look like this: select count (*) from child_life_cycle where doneProcessing is null. By the way, the doneProcessing column is a int type and holds a unix epoch time stamp. So if there were five child_life_cycle records in the database and three had values for doneProcessing and the other two didn't then I would expect to get a 2 back from my query and not transition. However, I'm finding that in some cases (when I look in my logs) the query will return zero (0) and then a few seconds later it will return the correct number of 2. Somehow those two records didn't show up in the query. They were masked. I'm running on: Windows server 2003 Python 2.5.2 SQLAlchemy 0.5.3 MySQL 5.1.34-community This is a high volume, highly concurrent system. The scenario I've described occurs once in every 50k to 100k transactions. This identical setup is running on many machines and all have experienced the same hiccup. Please let me know if there is a known bug or other sqlalchemy related issue that I should have in mind while troubleshooting. Thanks, Daniel --~--~-~--~~~---~--~~ 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: http://www.sqlalchemy.org/docs/05/ormtutorial.html
Any chance this is the second iteration of declaring the User class in this session, and the first time was missing the primary_key? I run into this in interactive sessions and need to call clear_mappers() before redoing the class. It seems that the old mapper is still hanging around and causes the compilation error. -- Mike Conley --~--~-~--~~~---~--~~ 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: http://www.sqlalchemy.org/docs/05/ormtutorial.html
I guess it's ipython cause when I tried saving the tutorial source code in a file and run python .py everything is working fine. Yeah you're right seems ridiculous. :) On Thu, Sep 3, 2009 at 11:10 PM, Mike Conley mconl...@gmail.com wrote: Any chance this is the second iteration of declaring the User class in this session, and the first time was missing the primary_key? I run into this in interactive sessions and need to call clear_mappers() before redoing the class. It seems that the old mapper is still hanging around and causes the compilation error. -- Mike Conley --~--~-~--~~~---~--~~ 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: records not appearing for a short period of time
Daniel wrote: So if there were five child_life_cycle records in the database and three had values for doneProcessing and the other two didn't then I would expect to get a 2 back from my query and not transition. However, I'm finding that in some cases (when I look in my logs) the query will return zero (0) and then a few seconds later it will return the correct number of 2. Somehow those two records didn't show up in the query. They were masked. it could that those zeros are occurring before the child records actually exist in the DB, which may or may not be possible. If for some reason your query for null is in a transaction that is more stale than the one that added the child records, that would cause it as well. I'm assuming you're all on InnoDB tables otherwise you're not getting any kind of transactional consistency. It also may be a known MySQL bug. Search around on their tracker as I have seen some bugs related to incorrect data being returned for InnoDB. --~--~-~--~~~---~--~~ 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: Inferring joins from table A to table C via table B
MUST we explicitly supply the join to such query objects? Or is there some way that SA can figure out that tbl_people_documents is in between tbl_people and tbl_documents on its own? Perhaps there is something we can add to the tbl_people/tbl_documents object definitions that clues SA in? join on the relation. query(A).join(A.relation_to_b).filter(B.foo == 'bar') The problem with that, from what we're trying to build, is that we have to explicitly know that relation object and supply it. We want SA to *infer* the relationship between any two tables based on the ORM relationships that we have already defined in our mapper objects. Again, my example was not the joining between two adjacent tables, which we can get SA to handle on its own without our help. It was between two tables that are adjacent only through an intermediary table that touches both of them. --Damon --~--~-~--~~~---~--~~ 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: records not appearing for a short period of time
I am using InnoDB for transactional support. I also have a timeLifeCycleInitialized field for each life cycle record and a timeLifeCycleTransitioned for the parent record. I have verified that the child records have existed for minutes to hours and have shown up in previous queries, so it's not the case that they don't exist yet. I'll look at the MySQL issues and post in their forum next. Thanks for your reply. Daniel On Sep 3, 9:43 am, Michael Bayer mike...@zzzcomputing.com wrote: Daniel wrote: So if there were five child_life_cycle records in the database and three had values for doneProcessing and the other two didn't then I would expect to get a 2 back from my query and not transition. However, I'm finding that in some cases (when I look in my logs) the query will return zero (0) and then a few seconds later it will return the correct number of 2. Somehow those two records didn't show up in the query. They were masked. it could that those zeros are occurring before the child records actually exist in the DB, which may or may not be possible. If for some reason your query for null is in a transaction that is more stale than the one that added the child records, that would cause it as well. I'm assuming you're all on InnoDB tables otherwise you're not getting any kind of transactional consistency. It also may be a known MySQL bug. Search around on their tracker as I have seen some bugs related to incorrect data being returned for InnoDB. --~--~-~--~~~---~--~~ 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: Inferring joins from table A to table C via table B
Damon wrote: MUST we explicitly supply the join to such query objects? Or is there some way that SA can figure out that tbl_people_documents is in between tbl_people and tbl_documents on its own? Perhaps there is something we can add to the tbl_people/tbl_documents object definitions that clues SA in? join on the relation. query(A).join(A.relation_to_b).filter(B.foo == 'bar') The problem with that, from what we're trying to build, is that we have to explicitly know that relation object and supply it. We want SA to *infer* the relationship between any two tables based on the ORM relationships that we have already defined in our mapper objects. but you're asking for it to infer the join between *three* tables - i.e. your association table. The current SQLA functionality is that ORM-level joins, that is joins which occur due to the presence of a relation(), must be expressed explicitly in terms of the relation between the two entity classes. Right now only a SQL level join, that is joins which occur due to the presence of a known foreign key between the two tables, is what happens if you don't specify the relation() you'd like to join on. The proposed enhancement would require that we change the method used when someone joins from A to B using query.join(), in that it would specifically search for ORM-level relations, instead of relying upon SQL-level joining which searches only for foreign keys between the two tables. It would also throw an error if there were any ambiguity involved. I'm not 100% sure but I think it's quite possible that we had such a assume the only relation() in use feature a long time ago when constructing joins, and it was removed in favor of explicitness, but I'd have to dig through 0.3 functionality to see if that was the case. My initial take on this feature is -1 on this since I don't think being explicit about an ORM relation is burdensome or a bad idea (plus we might have already made this decision a long time ago). We might just need some better error messages when a join can't be found between A and B to suggest that its only looking for immediate foreign keys in that case, not ORM relations. Alternatively, SQL-expression level join() would search for any number of paths from table A to table B between any other tables that may create a path between them. that would also find the association table between A and B and create a longer series of joins without ORM involvement. I'm strongly -1 on such a feature as the expression language shouldn't be tasked with performing expensive graph traversals just to formulate a SQL query, and table.join()'s contract is that it produces a JOIN between only two tables, not a string of joins. --~--~-~--~~~---~--~~ 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: Inferring joins from table A to table C via table B
Thank you very much for the explanation. It is what I feared was the case. One of the great features we love about SA is the mappers, allowing us to define table relationships in such a way that we can decide what table(s) around which to pivot, giving us different ways of returning data even when processed from the same query. It seemed to us that if the mappers are able to traverse all the joins necessary to render the mapped objects -- we greatly admire SA's ability to construct all the outer joins required to do this in one fell swoop -- that it should also be possible to have SA follow similar logic to construct query objects as well -- in a completely analogous fasion -- when supplied with filters. Alas that this is not the case. :( --Damon On Sep 3, 12:29 pm, Michael Bayer mike...@zzzcomputing.com wrote: Damon wrote: MUST we explicitly supply the join to such query objects? Or is there some way that SA can figure out that tbl_people_documents is in between tbl_people and tbl_documents on its own? Perhaps there is something we can add to the tbl_people/tbl_documents object definitions that clues SA in? join on the relation. query(A).join(A.relation_to_b).filter(B.foo == 'bar') The problem with that, from what we're trying to build, is that we have to explicitly know that relation object and supply it. We want SA to *infer* the relationship between any two tables based on the ORM relationships that we have already defined in our mapper objects. but you're asking for it to infer the join between *three* tables - i.e. your association table. The current SQLA functionality is that ORM-level joins, that is joins which occur due to the presence of a relation(), must be expressed explicitly in terms of the relation between the two entity classes. Right now only a SQL level join, that is joins which occur due to the presence of a known foreign key between the two tables, is what happens if you don't specify the relation() you'd like to join on. The proposed enhancement would require that we change the method used when someone joins from A to B using query.join(), in that it would specifically search for ORM-level relations, instead of relying upon SQL-level joining which searches only for foreign keys between the two tables. It would also throw an error if there were any ambiguity involved. I'm not 100% sure but I think it's quite possible that we had such a assume the only relation() in use feature a long time ago when constructing joins, and it was removed in favor of explicitness, but I'd have to dig through 0.3 functionality to see if that was the case. My initial take on this feature is -1 on this since I don't think being explicit about an ORM relation is burdensome or a bad idea (plus we might have already made this decision a long time ago). We might just need some better error messages when a join can't be found between A and B to suggest that its only looking for immediate foreign keys in that case, not ORM relations. Alternatively, SQL-expression level join() would search for any number of paths from table A to table B between any other tables that may create a path between them. that would also find the association table between A and B and create a longer series of joins without ORM involvement. I'm strongly -1 on such a feature as the expression language shouldn't be tasked with performing expensive graph traversals just to formulate a SQL query, and table.join()'s contract is that it produces a JOIN between only two tables, not a string of joins. --~--~-~--~~~---~--~~ 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: Inferring joins from table A to table C via table B
Damon wrote: Thank you very much for the explanation. It is what I feared was the case. One of the great features we love about SA is the mappers, allowing us to define table relationships in such a way that we can decide what table(s) around which to pivot, giving us different ways of returning data even when processed from the same query. It seemed to us that if the mappers are able to traverse all the joins necessary to render the mapped objects -- we greatly admire SA's ability to construct all the outer joins required to do this in one fell swoop -- that it should also be possible to have SA follow similar logic to construct query objects as well -- in a completely analogous fasion -- when supplied with filters. Alas that this is not the case. :( you have to realize in all of those cases, the mappers have been given by you the exact paths which it is to join on - it's never just looking for something and picking the first match. The outer join formulation (i.e. via eager loading) is present since you've placed lazy=False on those relations. The key practice here is requiring explicit statement of all behaviors, and we try to stick to that pretty often unless a behavior has absolutely zero chance of being surprising or appearing inconsistent with the steps required in more complex scenarios. Specifically with query(A).join(B) working automatically, if your code were greatly dependent on this, and you then someday added a second relation() between A and B, all your existing code would break. On Sep 3, 12:29 pm, Michael Bayer mike...@zzzcomputing.com wrote: Damon wrote: MUST we explicitly supply the join to such query objects? Or is there some way that SA can figure out that tbl_people_documents is in between tbl_people and tbl_documents on its own? Perhaps there is something we can add to the tbl_people/tbl_documents object definitions that clues SA in? join on the relation. query(A).join(A.relation_to_b).filter(B.foo == 'bar') The problem with that, from what we're trying to build, is that we have to explicitly know that relation object and supply it. We want SA to *infer* the relationship between any two tables based on the ORM relationships that we have already defined in our mapper objects. but you're asking for it to infer the join between *three* tables - i.e. your association table. The current SQLA functionality is that ORM-level joins, that is joins which occur due to the presence of a relation(), must be expressed explicitly in terms of the relation between the two entity classes. Right now only a SQL level join, that is joins which occur due to the presence of a known foreign key between the two tables, is what happens if you don't specify the relation() you'd like to join on. The proposed enhancement would require that we change the method used when someone joins from A to B using query.join(), in that it would specifically search for ORM-level relations, instead of relying upon SQL-level joining which searches only for foreign keys between the two tables. It would also throw an error if there were any ambiguity involved. I'm not 100% sure but I think it's quite possible that we had such a assume the only relation() in use feature a long time ago when constructing joins, and it was removed in favor of explicitness, but I'd have to dig through 0.3 functionality to see if that was the case. My initial take on this feature is -1 on this since I don't think being explicit about an ORM relation is burdensome or a bad idea (plus we might have already made this decision a long time ago). We might just need some better error messages when a join can't be found between A and B to suggest that its only looking for immediate foreign keys in that case, not ORM relations. Alternatively, SQL-expression level join() would search for any number of paths from table A to table B between any other tables that may create a path between them. that would also find the association table between A and B and create a longer series of joins without ORM involvement. I'm strongly -1 on such a feature as the expression language shouldn't be tasked with performing expensive graph traversals just to formulate a SQL query, and table.join()'s contract is that it produces a JOIN between only two tables, not a string of joins. --~--~-~--~~~---~--~~ 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: Inferring joins from table A to table C via table B
you can explicitly create these many-to-many join relations and eager load them. users = session.query(User).options(eagerload(User.groups)).all() if you want to query for relations with a filter, AFAIK you need to define them as a separate relation. class User(...) groups = relation(Group, primaryjoin=(User.id == GroupMember.user_id), secondaryjoin=(GroupMember.group_id == Group.id), secondary=GroupMember) large_groups = relation(Group, primaryjoin=(User.id == GroupMember.user_id), secondaryjoin=(GroupMember.group_id == Group.id Group.size 10), secondary=GroupMember) users = session.query(User).options(eagerload(User.large_groups)).all() users[0].large_groups # ... On Sep 3, 2009, at 1:24 PM, Damon wrote: Thank you very much for the explanation. It is what I feared was the case. One of the great features we love about SA is the mappers, allowing us to define table relationships in such a way that we can decide what table(s) around which to pivot, giving us different ways of returning data even when processed from the same query. It seemed to us that if the mappers are able to traverse all the joins necessary to render the mapped objects -- we greatly admire SA's ability to construct all the outer joins required to do this in one fell swoop -- that it should also be possible to have SA follow similar logic to construct query objects as well -- in a completely analogous fasion -- when supplied with filters. Alas that this is not the case. :( --Damon On Sep 3, 12:29 pm, Michael Bayer mike...@zzzcomputing.com wrote: Damon wrote: MUST we explicitly supply the join to such query objects? Or is there some way that SA can figure out that tbl_people_documents is in between tbl_people and tbl_documents on its own? Perhaps there is something we can add to the tbl_people/tbl_documents object definitions that clues SA in? join on the relation. query(A).join(A.relation_to_b).filter(B.foo == 'bar') The problem with that, from what we're trying to build, is that we have to explicitly know that relation object and supply it. We want SA to *infer* the relationship between any two tables based on the ORM relationships that we have already defined in our mapper objects. but you're asking for it to infer the join between *three* tables - i.e. your association table. The current SQLA functionality is that ORM-level joins, that is joins which occur due to the presence of a relation(), must be expressed explicitly in terms of the relation between the two entity classes. Right now only a SQL level join, that is joins which occur due to the presence of a known foreign key between the two tables, is what happens if you don't specify the relation() you'd like to join on. The proposed enhancement would require that we change the method used when someone joins from A to B using query.join(), in that it would specifically search for ORM-level relations, instead of relying upon SQL-level joining which searches only for foreign keys between the two tables. It would also throw an error if there were any ambiguity involved. I'm not 100% sure but I think it's quite possible that we had such a assume the only relation() in use feature a long time ago when constructing joins, and it was removed in favor of explicitness, but I'd have to dig through 0.3 functionality to see if that was the case. My initial take on this feature is -1 on this since I don't think being explicit about an ORM relation is burdensome or a bad idea (plus we might have already made this decision a long time ago). We might just need some better error messages when a join can't be found between A and B to suggest that its only looking for immediate foreign keys in that case, not ORM relations. Alternatively, SQL-expression level join() would search for any number of paths from table A to table B between any other tables that may create a path between them. that would also find the association table between A and B and create a longer series of joins without ORM involvement. I'm strongly -1 on such a feature as the expression language shouldn't be tasked with performing expensive graph traversals just to formulate a SQL query, and table.join()'s contract is that it produces a JOIN between only two tables, not a string of joins. --~--~-~--~~~---~--~~ 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: Single row of a One to Many query
On Sep 3, 6:15 pm, Mike Conley mconl...@gmail.com wrote:
On Thu, Sep 3, 2009 at 8:05 AM, Noufal nou...@gmail.com wrote:
stmt = session.query(Order.table.c.client_id,func.max
(Order.table.c.date).label('latest_order')).group_by
(Order.table.c.date).subquery()
I think your group_by needs to be Order.table.c.client_id to get latest
order per client
Ah. Select the one with the highest date when they're bunched together
by client_id. Makes sense. It works too.
Thanks Mike.
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
