By chrisivens on

I wonder if anyone can help me. I'm trying to get a sort set up within a module I'm writing that uses views. I can get close but can't get my head around it fully.

I would like to construct this: 

SELECT node.nid, count(iomvote.nid) AS votes_nid FROM node node LEFT JOIN iomvote iomvote ON node.nid = iomvote.nid WHERE (node.type IN ('video')) AND (node.status = '1') GROUP BY nid ORDER BY votes_nid DESC LIMIT 0, 4

But I can only get as far as this:

SELECT node.nid, iomvote.nid AS votes_nid FROM node node LEFT JOIN iomvote iomvote ON node.nid = iomvote.nid WHERE (node.type IN ('video')) AND (node.status = '1') ORDER BY votes_nid DESC LIMIT 0, 4

I can't get the count to work is basically the premise of it.

My hndler function looks like this

function iomvote_handler_votes_sort($op, &$query, $sortinfo, $sortdata) {
	//print_r($query);
	$join = array(
		  'left' => array(
			'table' => 'node',
			'field' => 'nid'
		  ),
		  'right' => array(
			'field' => 'nid'
		  )
		 );

	$query->add_table('{iomvote}', false, 1, $join);
	$query->set_count_field('votes_nid');
	
	$query->add_orderby('{iomvote}', 'nid', 'DESC', 'votes');
	
}

Anyone who can help me out here? Please :)

⋅ Categories: Drupal 5.x

Comments

KamBho’s picture

KamBho commented

Well! This is what we have done in order to create our custom sort according to the COUNT(nid):

function _audio_sort_handler($op, &$query, $sortinfo, $sortdata) {
$join = array(
'left' => array(
'table' => 'node',
'field' => 'nid'
),
'right' => array(
'field' => 'nid'
)
);

$query->add_table('{iomvote}', false, 1, $join);

$query->set_count_field('iomvote.nid');

$query->add_orderby('','COUNT(iomvote.nid)', 'DESC', 'cake');
$query->add_groupby('nid');

}

We used 'cake' as our alias as the default alias was causing an SQL error which resulted in our sort not working.

I hope this helps...

chrisivens’s picture

chrisivens commented

The cake alias made it easier to search for in the queries that the devel module displays. I made quite a hacky work-around in the meantime to get it to work which is still in place today. The code posted by Kam has been used for a different purpose but should still work for general views.

I hope this helps someone out as I really couldn't find much info on set_count_field() in the views documentation. I believe views v2 is much better documented.

Now to change the cake for something less fattening.