• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Querying four tables with joins, GROUP_CONCAT() and SUM()

#1
[eluser]Unknown[/eluser]
Hello, I'm after some guidance on the best way to go about Querying four tables with joins, GROUP_CONCAT() and SUM() and then passing that to the view.

I'm trying to retrieve a list of resources from the database and each list item needs a grouped list of tags for that resource and also the sum of votes for that resource.

Here's the simple version of the table structure:
Table resources contains id, resource_name resource_description and so.
Table resource_tags contains id, resource_id, tag_id.
Table tag_list contains id, tag.
Table votes contains id, resource_id, votes.

The tables structure can also be changed if there is a more effective way of structuring it.

Here's my model so far, it doesn't group the tags or get the sum of the votes column as I couldn't get anything close to working.

Code:
$this->db->from('resources AS re');
$this->db->join('resource_tags AS rt', 're.id = rt.resource_id', 'left');
$this->db->join('tag_list AS tl', 'tl.id = rt.tag_id', 'left');
$this->db->join('votes AS vo', 'vo.resource_id = re.id', 'left');

Any pointers would be much appreciated, Thanks!


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.