Jason,
the python stuff and sql ninja stuff are the results of some great
code-reviewers teaching me tricks and lots of complex queries over the
years!
tags = db(db.concert_tag.playlist== \
active_playlist.id).select(db.concert_tag.tag)
that's a simple web2py query. i take you can follow that line.
count = db.concert_tag.id.count()
sum = db.concert_tag.magnitude.sum()
count and sum come from the book. search on count and sum to find
them.
[t.tag for t in tags]
is python magic. tags is the iterable query result. and that's a
shortcut to build a list based on the iterable set. it is equivalent
to:
a = []
for t in tags:
a.append(t.tag)
then,
query = db((db.concert_tag.tag.belongs([t.tag for t in tags])) &
(db.concert_tag.playlist != active_playlist.id) &
(db.playlist.id == db.concert_tag.playlist) )
is the query, with the "magic" list for the belongs operator (just
like SQL "IN" clause). so this gets all concert_tags and associated
playlists (except for the active playlist) with one or more tag in
common with the active playlist - because the tag list in the belongs
is the list of tags for the active playlist.
rows = query.select(db.playlist.name,
db.concert_tag.playlist,
db.playlist.uid,
count,
sum,
groupby=db.concert_tag.playlist|
db.playlist.name|
db.playlist.uid,
orderby=~count|~sum|~db.playlist.date,
limitby=(0,NUM_RELATED_LINKS))
the select statement. determines what fields to get - playlist.name,
playlist.uid, count of shared tags, sum of the magnitude of the shared
tags (in your system we can assume all magnitudes are 1 so this part
is unnecessary). I messed up the group by in my first email because
it should contain all fields selected except for the count and sum
fields (the aggregate fields). lookup how SQL count/sum/group by work
if you need more information. orderby is just count (desc), sum
(desc), and playlist.date (desc) applied in order (lookup SQL order
by for more information). Limitby gets me NUM_RELATED_LINKS (which is
a constant of 5 for me) rows, starting from the first row returned.
does that help more?
cfh
On May 29, 4:03 am, Jason Brower <encomp...@gmail.com> wrote:
> Perhaps if you broke it down a bit I could get what is going on. For
> example, it looks like some pretty cool python syntax with.
> [t.tag for t in tags] I really can't understand what that does but it
> looks cool. :P
> I also didn't know you could use or operators in the limitby, spiffy.
> Thanks for your help so far. :)
> Best Regards,
> Jason
>
> On Fri, 2010-05-28 at 14:32 -0700, howesc wrote:
> > check outwww.tenthrow.com, click on a concert and you will see an
> > "other concerts" box. those are generated following several rules,
> > the first if finding concerts that share tags with this concert. My
> > tags have a "magnitude" or a weighting, so some are more valued than
> > others. hence the magnitude references below. otherwise my tag and
> > concert_tag tables mimic what you have in your model. I don't display
> > the tag name that they share though.
>
> > my relevant queries:
> > #active_playlist is a record, passed to my function.
> > tags = db(db.concert_tag.playlist== \
>
> > active_playlist.id).select(db.concert_tag.tag)
>
> > count = db.concert_tag.id.count()
> > sum = db.concert_tag.magnitude.sum()
> > query = db((db.concert_tag.tag.belongs([t.tag for t in tags]))
> > &
> > (db.concert_tag.playlist != active_playlist.id) &
> > (db.playlist.id == db.concert_tag.playlist) )
>
> > rows = query.select(db.playlist.name,
> > db.concert_tag.playlist,
> > db.playlist.uid,
> > count,
> > sum,
> > groupby=db.concert_tag.playlist|
> > db.playlist.uid,
> > orderby=~count|~sum|~db.playlist.date,
> > limitby=(0,NUM_RELATED_LINKS))
>
> > dunno if that helps or not...
>
> > On May 28, 1:31 pm, Jason Brower <encomp...@gmail.com> wrote:
> > > I tried doing this last year and I was close and couldn't figure it out.
> > > I need to complete implementing it and wondered if you could help out.
> > > Using the attatched model I want to do the following:
> > > I need to have a list of all the users that have similar tags to the
> > > user that is logged in. I need those tag names as well.
> > > As an added sugar candy bonus I wonder if there is an easy way to could
> > > them or should I just count them as I sort throw them in the view.
> > > I think I am close with this, it modified from something done last year.
> > > Am I doing this right?
> > > Best Regards,
> > > Jason Brower
>
> > > My pertinent controller function:
> > > ---
>
> > > @auth.requires_login()
> > > def your_connections():
> > > related_tags = db((
>
> > > db.auth_user.id==db.user_tags.user_id)&(db.user_tags.tag_id==db.tag.id)&(db.user_tags.tag_id.belongs(
> > > db(db.user_tags.user_id==auth.user.id)._select(
> > > db.user_tags.tag_id)))).select(
> > > db.auth_user.ALL,db.tag.ALL,groupby=db.auth_user.id)
> > > return dict(related_tags = related_tags)
> > > ---
>
> > > db.py
> > > 3KViewDownload
