Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
ActiveRecord class - Join
  • How can I make a multiple table join? Example: I have the tables: persons: id, table_id, chair_id, person_name
    tables: id, table_name
    chairs: id, chair_name Like this?
    // classes/models/persons.php
    // class Model_Persons extends ActiveRecord\Model {}
    
    // classes/controllers/test.php
    // function action_test()
    
    $person_name = 'Abc Def';
    
    $options = array('include' => array('tables', 'chairs'));
    $person = Model_Persons::find_by_person_name($person_name, $options);
    
    if ($person !== null)
    {
    echo $table = $person->table_name;
    echo $chair = $person->chair_name;
    }
    
  • 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.
  • Thx debug for your reply. Your example is great. But I knew this already. (To anyone, just open the db and model classes and see all basic examples.) I'll wait for the AR guru ;-) Change the tables is no option. In my example case it is not belongs to belongs to but one person can choose form multiple chairs and multiple tables. Your way is totally different. http://lukebaker.org/projects/activerecord-in-php/documentation/ Nice documentation but I still can not figure out to make multiple joins with AR.
  • 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
  • Someone knows ActiveRecord? Is this near right for my example? (still not working)
    class Model_Persons extends ActiveRecord\Model {  
     protected $belongs_to = array('chair', 'table');
    }
    
    class Model_Chairs extends ActiveRecord\Model {  
     protected $has_many = array('persons',
      array('tables' => array('through' => 'persons')));
    }
    
    
    class Model_Tables extends ActiveRecord\Model {  
     protected $has_many = array('persons',
      array('chairs' => array('through' => 'persons')));
    }
    
    $person = Model_Persons::find_by_person_name($person_name);
    
    
  • 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.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion