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 Injustice anywhere is a threat to justice everywhere. -- Dr. Martin L. King, Jr.
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.
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@gmail.com> To: development@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.
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@gmail.com> To: development@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.
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@gmail.com> To: development@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@gmail.com> To:development@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.
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@gmail.com> To: development@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@gmail.com> To: development@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.
participants (2)
-
nan wich -
Nikola Kotur