I made it fit my tables better:
count = db.users_tags.id.count()
query = db((db.users_tags.user != auth.user.id) & #<--- that is a join
(db.users_tags.tag.belongs(db(db.users_tags.user==auth.user.id)._select())))
#<--- I thought select was only used at the end when you done. :/
rows = query.select(db.users_tags.user,
count,
groupby=db.users_tags.user,
orderby=count)
But the query sadly doesn't work...
It give...
OperationalError:only a single result allowedforaSELECT thatispart of an
expression
And I sadly have no idea what that is.
Could you perhaps explain what each line does for me in this query? I
am very curious how this all works.
BR,
Jason Brower
On 04/14/2011 01:56 AM, howesc wrote:
untested but...
If i where to write raw SQL, i think this is what you are asking for
(where auth.user.id==42):
SELECT ut.user, count(*)
FROM users_tags ut
WHERE ut.user != 42
AND ut.tag IN (SELECT tag FROM user_tags WHERE user=42)
GROUP BY ut.user
ORDER BY count(*)
and i think this translated to DAL is:
count = db.user_tags.id.count()
query = db((db.user_tags.user != auth.user.id) &
(db.user_tags.tag.belongs(db(db.user_tags.user==auth.user.id)._select())))
rows = query.select(db.user_tags.user,
count,
groupby=db.user_tags.user,
orderby=count)
as i said, untested, but following similar logic i had written before.
christian
