CODEWITHSUNDEEP
mysqlcakephpmodel
Stathis G.
Stathis G.

Reputation: 247

cakephp translate query to cakephp model query

I have 2 models Scale and Step, where model Scale has many Step. The db tables are:

scale : id | name | s1 | s2 | s3 | s4 | s5 | s6 | s7
step : id | val

I want to write the following query in cakephp structure...

select s.id, s1.val as v1, s2.val as v2, s3.val as v3, s4.val as v4, s5.val as v5, s6.val as v6, s7.val as v7
from scale s
join step s1 on s.s1 = s1.id
join step s2 on s.s2 = s2.id
join step s3 on s.s3 = s3.id
join step s4 on s.s4 = s4.id
join step s5 on s.s5 = s5.id
join step s6 on s.s6 = s6.id
join step s7 on s.s7 = s7.id

Can you please help me with the syntax??

Upvotes: 0

Views: 139

Answers (2)

Stathis G.
Stathis G.

Reputation: 247

$this->find('all', array(

        'fields' => array('s1.val', 's2.val'),
        'joins' => array(array(
            'table' => 'step',
            'alias' => 's1',
            'type' => 'inner',
            'conditions' =>array('Scale.s1 = s1.id')
            ),array(
            'table' => 'step',
            'alias' => 's2',
            'type' => 'inner',
            'conditions' =>array('Scale.s2 = s2.id')
            ),
        ),
        'conditions' => array('Scale.id = '.$id.'')
        ));

Upvotes: 0

Crwydryn
Crwydryn

Reputation: 852

I would suggest that you maybe change your database structure. Rather than having Step bound to s1, s2, s3...etc in Scale, which has a few disadvantages, one of which is that it restricts you to only 7 steps per Scale, and isn't really a good relational database model, I would suggest that you create a third table, called something like ScaleSteps, and has three columns:

id | ScaleId | StepId

Imagine you had a Scale entry with an id of 7, and that Scale had Step entries 1, 2, and 3 associated with it, the resultant table would look like this:

id | ScaleId | StepId
1  | 7       | 1
1  | 7       | 2
1  | 7       | 3

Then setup your CakePHP model relationships using: hasOne, belongsTo...etc, for example:

class Scale extends Model {
var $name = "Scale";
var $hasMany = "ScaleStep"; 
}

class ScaleStep extends Model {
var $name = "ScaleStep";
var $belongsTo = "Step";    
}

Upvotes: 1

Related Questions