[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!
