SQL query to CakePHP translation

Question

I have been trying to figure out this (I guess really simple) thing for several days:

I have three tables with these fields:

1. alumnes: id, nomcognoms
2. grups: id
3. alumnesgrups: id, alumne_id (related to Alumnes.id) and grup_id (related to Grups.id). This is a pivot table (join table, relational table...)

My question is:

What should I code in AvadiariesController.php if I want to populate a select box (in an add.ctp) with results from this query:

SELECT
alumnes.nomcognoms

FROM
alumnes_grups

INNER JOIN grups ON
alumnes_grups.grup_id = grups.id

INNER JOIN alumnes ON
alumnes_grups.alumne_id = alumnes.id

WHERE 
alumnes_grups.grup_id = '16-17 2nB'

ORDER BY
alumnes.nomcognoms

Can I have a display field from another table? How can I see 'name' instead of 'id'?

User bill kindly suggested something I rewrote (correctly?) to:

$alumnesGrups = $this->Avadiaries->AlumnesGrups->Alumnes->find('list',
    ['fields' => ['Alumnes.nomcognoms'], // keep the alias consistent with whatever you define in the join
    'joins' => [['table' => 'alumnes',
                 'alias' => 'Alumnes',
                 'type' => 'INNER',
                 'conditions' => ['Alumnes.id' => 'AlumnesGrups.alumne_id']],
        // mimic the above to join the other table
                ['table' => 'grups',
                 'alias' => 'Grups',
                 'type' => 'INNER',
                 'conditions' => ['AlumnesGrups.grup_id' => '16-17 2nB'],
                 'order' => ['Alumnes.nomcognoms' => 'ASC']]
                ]
               ]
     );

Now add.ctp gives no error, but this condition is not being applied:

'conditions' => ['AlumnesGrups.grup_id' => '16-17 2nB']

I just need this starting point, so I can figure out some other things.

Answer 1

Try something like:

$this->AlumnesGrup->find('all',
    array(
        'fields' => array(
            'Alumne.nomcognoms' // keep the alias consistent with whatever you define in the join
        ),
        'joins' => array(
            array(
                'table' => 'alumnes',
                'alias' => 'Alumne',
                'type' => 'INNER',
                'conditions' => array(
                    'Alumne.id = AlumnesGrup.alumne_id
             ),
            // mimic the above to join the other table
        ),
        'conditions' => array(
            'AlumnesGrup.grup_id' => '15-16 2nB'
        ),
        'order' => array(
            'Alumne.nomcognoms ASC'
        )
    )
);

For clarity, I modified your query a bit to something like this:

SELECT
alumnes.nomcognoms

FROM
alumnes_grups

INNER JOIN grups ON
alumnes_grups.grup_id = grups.id

INNER JOIN alumnes ON
alumnes_grups.alumnes_id = alumnes.id

WHERE 
alumnes_grups.grup_id = '15-16 2nB'

ORDER BY
alumnes.nomcognoms

Answer 2

I finally found the solution after reading this document.

$alumnesGrups = $this->Avadiaries->AlumnesGrups->find('all', [
'fields' => ['Alumnes.name'], 
'contain' =>['Alumnes', 'Grups'], 
'conditions' => ['Grups.id =' => 1]
]);

My problem now is that I'm getting {"Alumnes":{"name":"Angela Smith"}} and not just plain "Angela Smith". I'll keep searching.