Re: [web2py] Re: I think I need to make a join on a many to many... getting complicated for me...

2011-04-15 Thread howesc 
jason,

you will need to join in the user table and adjust your grouping 
accordingly.  somthing like:

 count = db.users_tags.id.count()

 query = db((db.users_tags.user != auth.user.id)   #\/-- isn't 
that the join?

(db.users_tags.tag.belongs(db(db.users_tags.user==
auth.user.id)._select(db.users_tags.tag))) 

(db.auth_user.id==db.user_tags.user))

 rows = query.select(db.users_tags.user, db.auth_user.first_name, 
db.auth_user.last_name

 count,


 groupby=db.users_tags.user|db.auth_user.first_name|db.auth_user.last_name,

 orderby=count)

 for row in rows:

 print row.as_dict()

Re: [web2py] Re: I think I need to make a join on a many to many... getting complicated for me...

2011-04-14 Thread Jason Brower 

On 04/14/2011 08:46 AM, ron_m wrote:
The _select is a nested select - the one in the second half of the 
WHERE clause that Christian wrote out for the SQL equivalent.


But the _select needs to return exactly one column, try changing 
_select() to _select(db.user_tags.tag)


There is some discussion in the manual under the belongs topic

Ron

I see a bit better now, and it kind of works.
{'users_tags': {'user': 1}, '_extra': {'COUNT(users_tags.id)': 1}}
{'users_tags': {'user': 3}, '_extra': {'COUNT(users_tags.id)': 2}}
This represents one user having a relation on tags between two users, 
very cool, but I want the users information, and doing a query on each 
user is the inefficient way to do it right? I should join them, and I 
know how to do that, but this one, I have no idea.


count = db.users_tags.id.count()

query = db((db.users_tags.user != auth.user.id)   #\/-- isn't that 
the join?

   
(db.users_tags.tag.belongs(db(db.users_tags.user==auth.user.id)._select(db.users_tags.tag

   #/\--- Right 
there?

rows = query.select(db.users_tags.user,

count,

groupby=db.users_tags.user,

orderby=count)

for row in rows:

print row.as_dict()


Best Regards,
Jason

[web2py] Re: I think I need to make a join on a many to many... getting complicated for me...

2011-04-14 Thread Massimo Di Pierro 
I think you want

(db.users_tags.user != db.auth_user.id)

instead of

(db.users_tags.user != auth.user.id

On Apr 14, 12:05 pm, Jason Brower encomp...@gmail.com wrote:
 On 04/14/2011 08:46 AM, ron_m wrote: The _select is a nested select - the 
 one in the second half of the
  WHERE clause that Christian wrote out for the SQL equivalent.

  But the _select needs to return exactly one column, try changing
  _select() to _select(db.user_tags.tag)

  There is some discussion in the manual under the belongs topic

  Ron

 I see a bit better now, and it kind of works.
 {'users_tags': {'user': 1}, '_extra': {'COUNT(users_tags.id)': 1}}
 {'users_tags': {'user': 3}, '_extra': {'COUNT(users_tags.id)': 2}}
 This represents one user having a relation on tags between two users,
 very cool, but I want the users information, and doing a query on each
 user is the inefficient way to do it right? I should join them, and I
 know how to do that, but this one, I have no idea.

      count = db.users_tags.id.count()

      query = db((db.users_tags.user != auth.user.id)           #\/-- isn't 
 that the join?

                 
 (db.users_tags.tag.belongs(db(db.users_tags.user==auth.user.id)._select(db. 
 users_tags.tag

                                                                 #/\--- Right 
 there?

      rows = query.select(db.users_tags.user,

                          count,

                          groupby=db.users_tags.user,

                          orderby=count)

      for row in rows:

          print row.as_dict()

 Best Regards,
 Jason

Re: [web2py] Re: I think I need to make a join on a many to many... getting complicated for me...

2011-04-14 Thread Jason Brower 

Actually I think the correct way is

(db.users_tags.user != auth.user.id

Because I want all possible users but me. I am still learning this, but 
I think that's what it does.

BR,
Jason
On 04/14/2011 09:06 PM, Massimo Di Pierro wrote:

I think you want

(db.users_tags.user != db.auth_user.id)

instead of

(db.users_tags.user != auth.user.id

On Apr 14, 12:05 pm, Jason Browerencomp...@gmail.com  wrote:

On 04/14/2011 08:46 AM, ron_m wrote:  The _select is a nested select - the one 
in the second half of the

WHERE clause that Christian wrote out for the SQL equivalent.
But the _select needs to return exactly one column, try changing
_select() to _select(db.user_tags.tag)
There is some discussion in the manual under the belongs topic
Ron

I see a bit better now, and it kind of works.
{'users_tags': {'user': 1}, '_extra': {'COUNT(users_tags.id)': 1}}
{'users_tags': {'user': 3}, '_extra': {'COUNT(users_tags.id)': 2}}
This represents one user having a relation on tags between two users,
very cool, but I want the users information, and doing a query on each
user is the inefficient way to do it right? I should join them, and I
know how to do that, but this one, I have no idea.

  count = db.users_tags.id.count()

  query = db((db.users_tags.user != auth.user.id) #\/-- isn't 
that the join?

 
(db.users_tags.tag.belongs(db(db.users_tags.user==auth.user.id)._select(db. 
users_tags.tag

 #/\--- Right 
there?

  rows = query.select(db.users_tags.user,

  count,

  groupby=db.users_tags.user,

  orderby=count)

  for row in rows:

  print row.as_dict()

Best Regards,
Jason

Re: [web2py] Re: I think I need to make a join on a many to many... getting complicated for me...

2011-04-14 Thread Jason Brower 

I still can't get the user's information out of this query.
How would I do that?
BR,
Jason

On 04/15/2011 06:22 AM, Jason Brower wrote:

Actually I think the correct way is

(db.users_tags.user != auth.user.id

Because I want all possible users but me. I am still learning this, 
but I think that's what it does.

BR,
Jason
On 04/14/2011 09:06 PM, Massimo Di Pierro wrote:

I think you want

(db.users_tags.user != db.auth_user.id)

instead of

(db.users_tags.user != auth.user.id

On Apr 14, 12:05 pm, Jason Browerencomp...@gmail.com  wrote:
On 04/14/2011 08:46 AM, ron_m wrote:  The _select is a nested 
select - the one in the second half of the

WHERE clause that Christian wrote out for the SQL equivalent.
But the _select needs to return exactly one column, try changing
_select() to _select(db.user_tags.tag)
There is some discussion in the manual under the belongs topic
Ron

I see a bit better now, and it kind of works.
{'users_tags': {'user': 1}, '_extra': {'COUNT(users_tags.id)': 1}}
{'users_tags': {'user': 3}, '_extra': {'COUNT(users_tags.id)': 2}}
This represents one user having a relation on tags between two users,
very cool, but I want the users information, and doing a query on each
user is the inefficient way to do it right? I should join them, and I
know how to do that, but this one, I have no idea.

  count = db.users_tags.id.count()

  query = db((db.users_tags.user != auth.user.id) 
#\/-- isn't that the join?


 
(db.users_tags.tag.belongs(db(db.users_tags.user==auth.user.id)._select(db. 
users_tags.tag


 
#/\--- Right there?


  rows = query.select(db.users_tags.user,

  count,

  groupby=db.users_tags.user,

  orderby=count)

  for row in rows:

  print row.as_dict()

Best Regards,
Jason

[web2py] Re: I think I need to make a join on a many to many... getting complicated for me...

2011-04-13 Thread pbreit 
Like this?
http://web2py.com/book/default/chapter/06?search=distinct#Grouping-and-Counting

[web2py] Re: I think I need to make a join on a many to many... getting complicated for me...

2011-04-13 Thread howesc 
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

Re: [web2py] Re: I think I need to make a join on a many to many... getting complicated for me...

2011-04-13 Thread Jason Brower 

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

Re: [web2py] Re: I think I need to make a join on a many to many... getting complicated for me...

2011-04-13 Thread ron_m 
The _select is a nested select - the one in the second half of the WHERE 
clause that Christian wrote out for the SQL equivalent.

But the _select needs to return exactly one column, try changing _select() 
to _select(db.user_tags.tag)

There is some discussion in the manual under the belongs topic

Ron