2

I'm querying four tables (which are: resources, tag_list, resource_tags and votes) and trying to retrieve a list of resources with each list item having grouped tags and the sum of votes for that resource.

This is my current model: 

$this->db->select('*');
$this->db->select('GROUP_CONCAT(DISTINCT tag SEPARATOR " | ") AS tags, SUM(vote) AS sumvotes');
$this->db->from('resources');
$this->db->join('resource_tags', 'resources.r_id = resource_tags.resource_id', 'left');
$this->db->join('tag_list', 'tag_list.t_id = resource_tags.tag_id', 'left');
$this->db->join('votes', 'votes.resource_id = resources.r_id', 'left');
$this->db->where('resources.published', '1');   
$this->db->group_by('resources.r_id'); 
$this->db->order_by('votes.vote', 'desc');
$query = $this->db->get();

Edit: Here is the raw generated SQL

SELECT *, GROUP_CONCAT(DISTINCT tag SEPARATOR " | ") AS tags, SUM(vote) AS sumvotes
FROM (`olu_resources`)
LEFT JOIN `olu_resource_tags` ON `olu_resources`.`r_id` = `olu_resource_tags`.`resource_id`
LEFT JOIN `olu_tag_list` ON `olu_tag_list`.`t_id` = `olu_resource_tags`.`tag_id`
LEFT JOIN `olu_votes` ON `olu_votes`.`resource_id` = `olu_resources`.`r_id`
WHERE `olu_resources`.`published` =  '1'
GROUP BY `olu_resources`.`r_id`
ORDER BY `olu_votes`.`vote` desc

It seems to do everything except for calculating the correct number of votes, it returns the number of votes there are multiplied by the number of tags that item has. Does anyone know why this is happening? Or how to go about fixing this query?

David
  • 25
  • 1
  • 5
  • Have you profiled the actual query to see the raw generated SQL? Use `$this->output->enable_profiler(TRUE)` and post the actual results, and the results you expected. Use the [edit] link to update your question. – Wesley Murch Mar 14 '12 at 00:31
  • Is the `vote` field in your resources table? – augustknight Mar 14 '12 at 00:36
  • Hey, Thanks for the replies. I've added the raw sql, the results were pretty much what I expected. And augustknight the vote field is in a table called votes which is linked to the resource table by the resource id. – David Mar 14 '12 at 00:48
  • what is datatype of vote column ? –  Mar 14 '12 at 01:05
  • Hey Teez, the vote column type is 'int(1)'. – David Mar 14 '12 at 01:10

1 Answers1

0

Why don't you use a sub-query to get the votes? Active Record in CI is great to use for simple queries but difficult (and slow) when you have more complex queries like yours.

It is probably all possible with Active Record though I would check the profiler first and look at the difference in speed. Last time I did something like that it made a 7 second difference. I would try something like this:

$SQL = "SELECT *, GROUP_CONCAT(DISTINCT tag SEPARATOR ' | '),
(SELECT SUM(vote) FROM olu_votes WHERE olu_votes.resource_id = olu_resources.r_id) AS sumvotes
FROM (olu_resources)
LEFT JOIN olu_resource_tags ON olu_resources.r_id = olu_resource_tags.resource_id
LEFT JOIN olu_tag_list ON olu_tag_list.t_id = olu_resource_tags.tag_id
LEFT JOIN olu_votes ON olu_votes.resource_id = olu_resources.r_id
WHERE olu_resources.published =  '1'
GROUP BY olu_resources.r_id
ORDER BY olu_votes.vote desc"

$this->db->query($SQL);

This should hopefully also resolve your problem. Let me know if this works! if needed I'll create a test query on my own system to get you the result you want.

bottleboot
  • 1,659
  • 2
  • 25
  • 41