CODEWITHSUNDEEP
phpcakephp
ajt
ajt

Reputation: 662

find all on complex model

I just need to get the details from a find all across 4 associated tables. I looked at the docs and I can get the data from 2 tables but not the other tables. I have a lessons table where I get the relevant tutor id rows to display only. This works fine. I have a lessons-students table related to the lessons table over the FK lessons id. From this lessons-students table I then a field linked to another table.

lessons table (fk tutor_id but no student_id) tutors table(linked to lessons table with tutor_id) lessons_students table (linked with lessonID and has a studentID HABTM) students table(linked with studentID from lesson-students table)

so if I get a row from lessons table I want the tutor name (from tutor table and I can do this now), the student name via the studentID . This is 4 tables and this is proving a headache as the docs just do simple examples. I cant get the student name or student id to display

http://book.cakephp.org/2.0/en/models/retrieving-your-data.html

I tried this and I am guessing as I have a HABTM and a belongsTo relationship.There has to be documented somewhere as I am searching answers in google for hours.

$this->set( 'student',$this->Lesson->find('all', array( 'conditions' => array('Lesson.id' => 2), 'contain' => array( 'Student' => array('conditions' => array('Student.id' => 'Lesson.id'))))));

class LessonsController extends AppController {
   $this->set( 'tutor',$this->Lesson->find('all', array(
             'conditions'=>array('Lesson.Tutor_id'=> 1,'Lesson.id'=>'Lesson_Students.lesson_id') ) ));



view
////////

  <?php 
      foreach ($tutor as $item):

        echo '<tr><td>'. $item['Lesson']['id'].'</td>';
      //  echo '<td>'. $item['Tutor']['id']['username'].'</td>';
        echo '<td>'. $item['Lesson']['tutor_id'].'</td>';
         echo '<td>'. $item['Tutor']['last_name'].'</td>';  
           echo '<td>'. $item['Lesson_Student']['student_id'].'</td>'; 
        echo '</tr>';
     endforeach;
     unset($item);

Upvotes: 0

Views: 60

Answers (1)

alpening
alpening

Reputation: 28

I solved a similar problem by adding all needed joins manually to the options array:

'joins' => array(
    array(
        'table' => 'sales',
        'alias' => 'Sale',
        'type' => 'LEFT',
        'conditions' => array(
            'Sale.serialnumber_id = Serialnumber.id'
        )
    ),
    array(
        'table' => 'rentals',
        'alias' => 'Rental',
        'type' => 'left',
        'conditions' => array(
            'Rental.serialnumber_id = Serialnumber.id'
        )
    ),
)

Then you can use all these field in your conditions-array and add them to your fields-array.

To optimize the whole query, you should only retrieve the fields you need - by adding the fields option. A SELECT * query (like this one) is query is slow and creates a lot if useless traffic, especially when you query multiple tables.

Upvotes: 1

Related Questions