[development] Ranking in a query

[Nikola Kotur]

Than, you'd have to adjust it.

This is what I get, from my test db:


+------+-----+---------------+
| row  | uid | comment_count |
+------+-----+---------------+
|    1 |   1 |            27 |
|    2 |   4 |            13 |
|    3 |   9 |             7 |
|    4 |  20 |             5 |
|    5 |  70 |             4 |
|    6 | 110 |             3 |
|    7 |   6 |             2 |
|    8 |  64 |             2 |
|    9 | 151 |             2 |
|   10 | 152 |             2 |
|   11 |  62 |             1 |
|   12 |  67 |             1 |
|   13 |  10 |             1 |
+------+-----+---------------+
13 rows in set (0.00 sec)


But, I am not sure how this will work with LIMIT from pager. Seems like
you'd have to put more logic into this, and there's a good chance that
one query won't be up to the job.

On Fri, 2010-10-29 at 12:47 -0700, nan wich wrote:
> 
> Thanks, but it didn't do anything on my system. No errors, no "empty
> result", nothing at all.
>  
> 
> Nancy
> 
>  
> 
> Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L.
> King, Jr.
> 
> 
> 
> 
> 
> 
> 
> ______________________________________________________________________
> From: Nikola Kotur <kotnick at gmail.com>
> To: development at drupal.org
> Sent: Fri, October 29, 2010 9:32:56 AM
> Subject: Re: [development] Ranking in a query
> 
> Here it is:
> 
> select @row := @row + 1 as row, uid, comment_count from (SELECT c.uid,
> COUNT(c.cid) AS comment_count FROM comments c WHERE c.uid <> 0 GROUP
> BY c.uid ORDER BY comment_count DESC) as com_count, (SELECT @row := 0)
> r
> 
> Note: I've changed comments.mail to comments.uid, since my testing db
> has mail fields empty.
> 
> But, I am warning you that this query might brake if you put it
> through db_query_rewrite.
> 
> On Fri, 2010-10-29 at 05:35 -0700, nan wich wrote: 
> 
> > 
> > Thanks, but that doesn't do what I want. What I need is the row
> > number that results from this query (simplified for example). 
> >   
> > SELECT c.mail, COUNT(c.cid) AS comment_count FROM comments c WHERE
> > c.mail <> '' GROUP BY c.mail ORDER BY comment_count DESC 
> >   
> > And remember that pager_query is going to add LIMIT xxx, 10.
> >   
> > Nancy
> > 
> >  
> > 
> > Injustice anywhere is a threat to justice everywhere. -- Dr. Martin
> > L. King, Jr.
> > 
> > 
> > 
> > 
> > 
> > ____________________________________________________________________
> > 
> > From: Nikola Kotur <kotnick at gmail.com>
> > To: development at drupal.org
> > Sent: Fri, October 29, 2010 5:19:17 AM
> > Subject: Re: [development] Ranking in a query
> > 
> > On Thu, 2010-10-28 at 06:32 -0700, nan wich wrote:
> > 
> > > I have a query that counts comments and all is well with it. But
> > > I'd like add a ranking column to the display. The problem is that
> > > it uses tablesort, so the ranking has to be in the query because I
> > > don't know which column will be used to sort the display. Oh, it
> > > is also paged so I can't count on the first row being #1 and so
> > > on.   Has anyone got an idea on how to add a ranking column to the
> > > query? (MySql only.) 
> > 
> > 
> > Nancy, here's a query that returns a nid, and it's row position:
> > 
> > SELECT @row := @row + 1 as row, n.nid FROM node n, (SELECT @row :=
> > 0) r;
> > 
> > You can start from there.
> > 
> > 
> 
> 
> 
> 


-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.drupal.org/pipermail/development/attachments/20101031/e22159c5/attachment.html