Found out what the problem was. I'm building a REST application and the client needs to query 50 results per page but for the pagination to work it also needs to know the total number of rows in the database so sqlalchemy generates the second query (i call it before applying limit, and offset):
SELECT count(1) AS count_1 FROM user Since I also apply a having in my query it brakes the 2nd query because second query only does a basic count on the primary table. >From my experience SQL_CALC_FOUND_ROWS should be used here so I don't have to call count () on the results. I'll have to research more on this with sqlalchemy. Maybe some1 else figured it out already. On Wed, Jun 8, 2011 at 5:44 PM, [email protected] <[email protected]> wrote: > Could it be because I'm defining it as a column to hold the > 'itemsCount' value and so it generates to queries to get the value? > > mapper( User, users_table, properties = { > 'itemsCount': column_property( > select( > [func.count( items_table.c.id )], > items_table.c.user_id == users_table.c.id > ).label( 'itemsCount' ) > ) > } ) > > On Wed, Jun 8, 2011 at 6:01 AM, Mike Conley <[email protected]> wrote: >> Not sure how you get 2 queries, but this seems like it gives right answer. >> >> class User(Base): >> __tablename__ = 'users' >> id = Column(Integer, primary_key=True) >> items = relationship('Item') >> class Item(Base): >> __tablename__ = 'items' >> id = Column(Integer, primary_key=True) >> user_id = Column(Integer, ForeignKey(User.id)) >> >> q1 = session.query(User.id, func.count(Item.id)).\ >> join('items').\ >> group_by(User.id).having(func.count(Item.id)>5) >> >> Check out the documentation for Query.join for alternatives on the join >> parameter >> >> http://www.sqlalchemy.org/docs/06/orm/query.html#sqlalchemy.orm.query.Query.join >> >> for example: if the "items" relationship isn't declared >> .join((Item,User.id==Item.user_id)) >> generates the same SQL >> >> -- >> Mike Conley >> >> -- >> You received this message because you are subscribed to the Google Groups >> "sqlalchemy" group. >> To post to this group, send email to [email protected]. >> 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. >> > > > > -- > > Daniel Nistor > [email protected] > Website : www.anonymous-proxies.net > -- Daniel Nistor [email protected] Website : www.anonymous-proxies.net -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to [email protected]. 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.
