Multi to Multi database call

[eluser]Designinglives[/eluser]
I'm new to codeigniter (have only been working with it for a few months now) but I'm stuck on something and was hoping there was anyone out there who had this experience before and would be able to help me. So, here it goes...

I've got a database which uses 4 tables and 3 tables to link them together as there are relations between them which are m to m ones. I've sketched it a bit in the attached picture. You basically got a table remarks which can have multiple arrangements, treatments and products in it. Every product, treatment and arrangement can also be in multiple remarks so I used 3 tables to join them together.

remarks_products - links products table to remarks table
remarks_arrangements - links arrangements table to remarks table
and as you may have guessed by now remarks_treatments - links treatments to remarks

I wanted to get a list of all the remarks (which is simply a database call with no join) but it should also get every product, arrangement and treatment attached to it. As far as I know you can only join 3 tables together but now I need 4 more actually. Anyone who can help me on the way? I've tried quite a few things already but always seem to get stuck when getting the other 4 tables in it. I really hope someone here can help me with this so if you have any more questions or it's not clear feel free to ask.

Thanks in advance!

Diagram can be found here as attachement wouldn't work.

EDIT: Not sure if it's the right solution at all but for me it works more or less.

Code:

function allRemarksForCustomerId($customerId) {

  //Get all remarks for the customerId (or even all remarks if you would change the function
  $data['allRemarksForCustomerId'] = $this->m_remarks->getAllRemarksForCustomerId($customerId);
  
  //Initiate a timer
  $i = 0;

  //Start a foreach to go over every remark and to include some more info later on
  foreach($data['allRemarksForCustomerId']->result_array() as $row) {
  
  //Get data for all remarks - products
   $remarkId = $row['remark_id'];

   $allProductsForRemark = '';
   $allProductsForRemark = $this->m_remarks->getProductsForRemark($remarkId);

   $allArrangementsForRemark = '';
   $allArrangementsForRemark = $this->m_remarks->getArrangementsForRemark($remarkId);

   $allTreatmentsForRemark = '';
   $allTreatmentsForRemark = $this->m_remarks->getTreatmentsForRemark($remarkId);
  
   //Put all remarks in a new array
   $array1 = array_values($data['allRemarksForCustomerId']->result_array());
  
   $array1[$i]['products'] = $allProductsForRemark;
   $array1[$i]['arrangements'] = $allArrangementsForRemark;
   $array1[$i]['treatments'] = $allTreatmentsForRemark;
  
   $i+=1;
  }
  
  $this->load->view('v_listRemarks', $array1);
}

[eluser]rogierb[/eluser]
Jou can join a lot of tables together and even the same one multiple times. Older database engines might have a limit but I only found one in MSSQL 7 (it was 68 joins:-))

I think your looking for something like this

Code:

$this->db->get('remark r');
$this->db->join('remarks_products rp', 'r.id = rp.remark_id', 'left');
$this->db->join('products p', 'rp.product_id = p.id', 'left');
$this->db->join('remarks_arrangements ra', 'r.id = ra.remark_id', 'left');
$this->db->join('arrangements a', 'ra.arrangements_id = p.id', 'left');
$this->db->join('remarks_treatments rt', 'r.id = rt.remark_id', 'left');
$this->db->join('treatments t', 'rt.treatments_id = p.id', 'left');