FuelPHP Forums

ActiveRecord hmmm great for one or two tables but after that I gave up as I ended up doing an additional foreach to process results for a third table and forth table which just seemed an absolute mess to me. I need to look at it again at some stage I suppose as I must have been doing something wrong but I have no idea what so anyway I just use normal query code like below. Try changing you table stucture to this: tables: id, table_name
chairs: id, table_id, chair_name
persons: id, chair_id, person_name So a person belongs to a chair which belongs to a table.

$query = DB::select('*')
      ->from('table')
      ->join('chair', 'left')
      ->on('table.id', '=', 'chair.table_id') // chair belongs to a table
      ->join('person', 'left')
      ->on('chair.id', '=', 'person.chair_id') // person belongs to a chair
      ->execute()
      ->as_array();
// By using as_array you can simply print_r($query); 
// to see the query results. Then when your happy use as_object to 
// reference it like $field->value
//
// Don't forget unless you alias every thing you will have no id
// for table or chair as it is not a unique field name.

This is untested but should give you a good idea to start with. Hopefully some ActiveRecord fans will jump in on this and explain how ActiveRecord really works for four or more tables without having to do a sub foreach for each of the additional table data elements required. Hope that helps.

Yeah I was also pointed to that as the Fuel implementation is supposed to be built similar to that but couldn't really tie that up with what I was trying to achieve. I got it working but found it to be a real mess with addition foreach to process data from a third and forth table. I simply couldn't reference the data for my lookup tables. One deep ($item->title) worked but two deep was another object so couldn't do $item->tablename->value. Ended up doing foreach ($item->tablename as $data) which just seemed wrong. Just have to wait for the documentation with lots of examples I hope. Anyway good luck

Okay, I'm a step closer, first of all: Models MUST be in single form for ActiveRecord (Model_Chair).
Database table names MUST be in multiple form (chairs).
The reference MUST be in single form + _id (chair_id inside persons). In my example a person can have one table and one chair but a table can be assigned to many persons (as for chair).

class Model_Person extends ActiveRecord\Model {     
    protected $has_one = array('chair', 'table');
//  protected $has_many = array();
//  protected $belongs_to = array();
}

class Model_Chair extends ActiveRecord\Model {     
//  protected $has_one = array();
    protected $has_many = array('persons');
//  protected $belongs_to = array();
}

class Model_Table extends ActiveRecord\Model {     
//  protected $has_one = array();
    protected $has_many = array('persons');
//  protected $belongs_to = array();
}

This line gets only the contents of a person.

$person = Model_Person::find_by_person_name($person_name);

You can however access the table and chair, by:

echo $person->table->table_name;
echo $person->chair->chair_name;

Behind the scene there are two queries done, one for the table and the second echo one for the chair. It is possible to add the two references right away, by:

$options = array('include' => array('table', 'chair'));
$person = Model_Person::find_by_person_name($person_name, $options);

Using the same lines happens the same except behind the scene there are no queries because they were already included.

echo $person->table->table_name;
echo $person->chair->chair_name;

Now, I hope this helps someone... If I or maybe you find more ways and explanations doning ActiveRecord and multiple tables, post it here. Take some time reading http://lukebaker.org/projects/activerecord-in-php/documentation/ after a while you will understand.