But for work in works: work.like_query.count() causes inefficient 1+N queries even if we have the right indices for it. Of course I could query like session.query(Work, count()).join(Work.like_set).group_by(Work) but it’s somewhat complicated to read and write for me (is it only me?). I want to reject such uses in ORM…
Anyway I use PostgreSQL. On Thursday, December 6, 2012 9:17:14 AM UTC+9, Eric Ongerth wrote: > > But work.like_query.count() will be efficient if you have the right > indexes in the database, no? > > I think if you want to denormalize that count all the way and also stay > very efficient, maybe it would be good to do it right on the db server with > a trigger and a stored procedure and avoid extra python function calls. > What database do you use? > > > On Wednesday, December 5, 2012 3:06:03 PM UTC-8, Hong Minhee wrote: >> >> Hi, >> >> I am making a typical web application using SQLAlchemy, and it contains >> “users”, “works” and “likes”: >> >> >> - users ( id, login, … ) >> - works ( id, title, … ) >> - likes ( user_id, work_id ) >> >> >> I want to print how many users liked each work, so the most simple (but >> naive) is querying count of them e.g.: >> >> len(work.like_set) >> work.like_query.count() >> >> >> However it’s inefficient, so I want to maintain like_count field or such >> thing for works. The problem is that I have to manually update the field >> every time new like is inserted or existing like is deleted. I thought it >> could be automatically updated without inconsistency if I catch SQLAlchemy >> events, but I’m not sure whether it’s correct way to do it or not. >> >> How do you guys solve such situation with SQLAlchemy? >> >> Thanks, >> Hong Minhee >> > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/WoMqe657sPEJ. 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.
