I posted on the d.o forum yesterday about putting together a sql query for a block in which users are listed according to the total pageviews their content had received. I got one or two suggestions but nothing concrete so waded in to try and get it done myself. I've come up against both sql problems and theming issues too.
The two relevant tables are node and node_counter, so if we take some example data:
node
nid uid 1 1 2 2 3 1 4 1 5 2
node_counter
nid totalcount 1 15 2 22 3 9 4 17 5 6
so I'm looking for a block that gives me:
USER PAGEVIEWS John (uid 1) 41 Ned (uid 2) 28
so far, I have this:
<table><tr><td>Title</td><td>Pageviews</td></tr> <?php $uid = $account->uid; $query= "SELECT node.title, node.nid, node_counter.totalcount FROM node INNER JOIN node_counter ON node.nid = node_counter.nid WHERE uid = $uid ORDER BY totalcount DESC LIMIT 5"; $results = db_query($query); while($output = db_fetch_object($results)){ print "<tr><td>"; print "<a href=\"/node/$output->nid\">"; print $output->title; print "</a>"; print "</td><td>"; print $output->totalcount; print "</td></tr>"; } ?> </table>
which gives me the top 5 viewed pages for each user on their main /user page. It's very handy and I think I'll use it. Can someone give me a pointer to go the next step towards my initial aim, the total of a user's pageviews and then a list of the top X users on a site based on pageviews. I know I need to use SUM in the sql statement but I don't seem to be able to get the syntax right.
My other question regards the theming of the output. You can see from my code that I've manually built the links...is there an easier way to do it. I've used "$output .= node_title_list($result);" before but that was just for a list of node titles, not for a table with 2-3 cols in it. Is there another theme function for what I'm trying to do?
thanks in advance for any hints you can give.
Neil
OK, I kept working away and am now very close:
<table><tr><td>User</td><td>Pageviews</td></tr> <?php $query= "SELECT node.nid, node.uid, SUM( node_counter.totalcount ) AS TotalPageviews FROM node JOIN node_counter ON node.nid = node_counter.nid GROUP BY node.uid ORDER BY `TotalPageviews` DESC LIMIT 5"; $results = db_query($query); while($output = db_fetch_object($results)){ print "<tr><td>"; print "<a href=\"/user/$output->uid\">"; print $output->uid; print "</a>"; print "</td><td>"; print $output->TotalPageviews; print "</td></tr>"; } ?> </table>
This gives me the following:
User Pageviews 2 349 7 298 1 187 5 183 8 136
How do I get the user names to show instead of the uid's? Is that going to be another JOIN with user table or can I get them from the UID in a simpler way?
Neil
----- Original Message ----- From: Neil: esl-lounge.com To: support@drupal.org Sent: Wednesday, September 10, 2008 12:12 PM Subject: [support] couple of issues with an sql query and theming a db query
I posted on the d.o forum yesterday about putting together a sql query for a block in which users are listed according to the total pageviews their content had received. I got one or two suggestions but nothing concrete so waded in to try and get it done myself. I've come up against both sql problems and theming issues too.
The two relevant tables are node and node_counter, so if we take some example data:
node
nid uid 1 1 2 2 3 1 4 1 5 2
node_counter
nid totalcount 1 15 2 22 3 9 4 17 5 6
so I'm looking for a block that gives me:
USER PAGEVIEWS John (uid 1) 41 Ned (uid 2) 28
so far, I have this:
<table><tr><td>Title</td><td>Pageviews</td></tr> <?php $uid = $account->uid; $query= "SELECT node.title, node.nid, node_counter.totalcount FROM node INNER JOIN node_counter ON node.nid = node_counter.nid WHERE uid = $uid ORDER BY totalcount DESC LIMIT 5"; $results = db_query($query); while($output = db_fetch_object($results)){ print "<tr><td>"; print "<a href=\"/node/$output->nid\">"; print $output->title; print "</a>"; print "</td><td>"; print $output->totalcount; print "</td></tr>"; } ?> </table>
which gives me the top 5 viewed pages for each user on their main /user page. It's very handy and I think I'll use it. Can someone give me a pointer to go the next step towards my initial aim, the total of a user's pageviews and then a list of the top X users on a site based on pageviews. I know I need to use SUM in the sql statement but I don't seem to be able to get the syntax right.
My other question regards the theming of the output. You can see from my code that I've manually built the links...is there an easier way to do it. I've used "$output .= node_title_list($result);" before but that was just for a list of node titles, not for a table with 2-3 cols in it. Is there another theme function for what I'm trying to do?
thanks in advance for any hints you can give.
Neil
------------------------------------------------------------------------------
-- [ Drupal support list | http://lists.drupal.org/ ]
On Wed, 10 Sep 2008 12:12:27 +0200 "Neil: esl-lounge.com" neil@esl-lounge.com wrote:
$query= "SELECT node.title, node.nid, node_counter.totalcount FROM node INNER JOIN node_counter ON node.nid = node_counter.nid WHERE uid = $uid ORDER BY totalcount DESC LIMIT 5";
which gives me the top 5 viewed pages for each user on their main /user page. It's very handy and I think I'll use it. Can someone give me a pointer to go the next step towards my initial aim, the total of a user's pageviews and then a list of the top X
select sum(node_counter.totalcount) from node inner join node_counter on node.nid = node_counter.nid where node.uid=$uid; total page viewed by a user if I interpret it right.
users on a site based on pageviews. I know I need to use SUM in the sql statement but I don't seem to be able to get the syntax right.
select uid, sum(node_counter.totalcount) from node inner join node_counter on node.nid = node_counter.nid group by node.uid order by sum(node_counter.totalcount) desc limit 5;
If I interpret it right.
The second query may be pretty expensive.
Thanks Ivan, my second mail crossed yours. There doesn't seem to be that much difference between what you've posted here and what I had in my second mail, does there?
I suppose it's an expensive query because it's looking through the whole node table, getting all of them, and then sorting them. It will only be in one block on the front page, so hopefully it shouldn't cause too many issues.
thanks a lot
Neil
----- Original Message ----- From: "Ivan Sergio Borgonovo" mail@webthatworks.it To: support@drupal.org Sent: Wednesday, September 10, 2008 12:56 PM Subject: Re: [support] couple of issues with an sql query and theming a db query
On Wed, 10 Sep 2008 12:12:27 +0200 "Neil: esl-lounge.com" neil@esl-lounge.com wrote:
$query= "SELECT node.title, node.nid, node_counter.totalcount FROM node INNER JOIN node_counter ON node.nid = node_counter.nid WHERE uid = $uid ORDER BY totalcount DESC LIMIT 5";
which gives me the top 5 viewed pages for each user on their main /user page. It's very handy and I think I'll use it. Can someone give me a pointer to go the next step towards my initial aim, the total of a user's pageviews and then a list of the top X
select sum(node_counter.totalcount) from node inner join node_counter on node.nid = node_counter.nid where node.uid=$uid; total page viewed by a user if I interpret it right.
users on a site based on pageviews. I know I need to use SUM in the sql statement but I don't seem to be able to get the syntax right.
select uid, sum(node_counter.totalcount) from node inner join node_counter on node.nid = node_counter.nid group by node.uid order by sum(node_counter.totalcount) desc limit 5;
If I interpret it right.
The second query may be pretty expensive.
-- Ivan Sergio Borgonovo http://www.webthatworks.it
-- [ Drupal support list | http://lists.drupal.org/ ]
Double check your indexes, but keep in mind that you can't build a cross-table index in MySQL. (Or Postgres for that matter)
If it's still too resource intensive, look at denormalizing the data.
-Mike
On Sep 10, 2008, at 4:09 AM, Neil: esl-lounge.com wrote:
Thanks Ivan, my second mail crossed yours. There doesn't seem to be that much difference between what you've posted here and what I had in my second mail, does there?
I suppose it's an expensive query because it's looking through the whole node table, getting all of them, and then sorting them. It will only be in one block on the front page, so hopefully it shouldn't cause too many issues.
thanks a lot
Neil
----- Original Message ----- From: "Ivan Sergio Borgonovo" mail@webthatworks.it To: support@drupal.org Sent: Wednesday, September 10, 2008 12:56 PM Subject: Re: [support] couple of issues with an sql query and theming a db query
On Wed, 10 Sep 2008 12:12:27 +0200 "Neil: esl-lounge.com" neil@esl-lounge.com wrote:
$query= "SELECT node.title, node.nid, node_counter.totalcount FROM node INNER JOIN node_counter ON node.nid = node_counter.nid WHERE uid = $uid ORDER BY totalcount DESC LIMIT 5";
which gives me the top 5 viewed pages for each user on their main /user page. It's very handy and I think I'll use it. Can someone give me a pointer to go the next step towards my initial aim, the total of a user's pageviews and then a list of the top X
select sum(node_counter.totalcount) from node inner join node_counter on node.nid = node_counter.nid where node.uid=$uid; total page viewed by a user if I interpret it right.
users on a site based on pageviews. I know I need to use SUM in the sql statement but I don't seem to be able to get the syntax right.
select uid, sum(node_counter.totalcount) from node inner join node_counter on node.nid = node_counter.nid group by node.uid order by sum(node_counter.totalcount) desc limit 5;
If I interpret it right.
The second query may be pretty expensive.
-- Ivan Sergio Borgonovo http://www.webthatworks.it
-- [ Drupal support list | http://lists.drupal.org/ ]
-- [ Drupal support list | http://lists.drupal.org/ ]
__________________ Michael Prasuhn mike@mikeyp.net http://mikeyp.net
-
Ivan Sergio Borgonovo -
Michael Prasuhn -
Neil: esl-lounge.com