sqlalchemy filter by count problem

[c***@gmail.com]

I have a select with a count on it. Count row is taken from another
related table so I can see how many items a user has. I want to filter
the results to select only those with items more than 5 let's say.

Sqlalchemy generates 2 queries for this. Placing a 'having' filter
gives an error but looking at the generated sql's the first query is
ok and working, only the second one gives an error. Can any1 say if
there's a way in forcing sqlqlchemy to only generate 1 query?

generated sql's:

SELECT (SELECT count(`items`.id) AS count_1
FROM `items`
WHERE `items`.user_id = user.id) AS `itemsCount`, user.id AS user_id
FROM user
HAVING itemsCount>5

SELECT count(1) AS count_1
FROM user
HAVING itemsCount>5


and the error:


OperationalError: (OperationalError) (1054, "Unknown column
'itemsCount' in 'having clause'") 'SELECT count(1) AS count_1 \nFROM
user \nHAVING itemsCount>5' ()

[Mike Conley]

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

[c***@gmail.com]

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' )
)
} )

Post by Mike Conley
Not sure how you get 2 queries, but this seems like it gives right answer.
        __tablename__ = 'users'
        id = Column(Integer, primary_key=True)
        items = relationship('Item')
        __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 unsubscribe from this group, send email to
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.

--
Daniel Nistor
***@gmail.com
Website : www.anonymous-proxies.net

[c***@gmail.com]

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.

Post by c***@gmail.com
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' )
       )
} )

Post by Mike Conley
Not sure how you get 2 queries, but this seems like it gives right answer.
        __tablename__ = 'users'
        id = Column(Integer, primary_key=True)
        items = relationship('Item')
        __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 unsubscribe from this group, send email to
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.

--
Daniel Nistor
Website : www.anonymous-proxies.net

--
Daniel Nistor
***@gmail.com
Website : www.anonymous-proxies.net