Modifying model to take into account my pivot table

Yii 1.1 General Discussion
1

I have three tables - myusers, organisation_classrooms, organisation_classrooms_myusers (this is a pivot table between organisation_classrooms & the myusers table)

organisation_classrooms(classroom_id, organisation_id, title)

organisation_classrooms_myusers(classroom_id, user_id)

myusers(user_id, organisation_id, fullname)

I have the following search() function within one of my models:




        return new CActiveDataProvider(OrganisationClassroom::model()->currentUserOrganisation(), array(

            'criteria' => $criteria,

            'pagination' => array(

            'pageSize' => 10,

            )

        ));

This is the dataset that I am using

organisation_classrooms

classroom_id title organisation_id

  1          Class A         37383


  2          Class B         37383


  3          Class C         37383

organisation_classrooms_myusers

classroom_id user_id

  2            1558


  3            1558

myusers

user_id organisation_id name

1558 37383 Bob Doe

I’d like to amend the model so that for each organisation_classroom that belongs to the organisation, check if the user_id 1558 exists in the organisation_classrooms_myusers pivot table and basically set a boolean value

e.g

I’d like the following data returned via the organisation_classrooms CActiveDataProvider used above for the user_id of 1558

classroom_id title organisation_id pivot_set

  1        Class A         37383            0


  2        Class B         37383            1


  3        Class C         37383            1

Can anyone suggest how to go about this? This way I will be able to do a sort_by pivot_set column ASC/DESC etc…

P.S I apologise for the formatting - the forum doesn’t seem to retain the spacing i added with the dataset stuff above

2

This is the query you need, right ? I.e.: always all records from organisation_classrooms, but with an extra flag to indicate whether for a specific user the organisation_classrooms_myusers combination exists ?

[sql]select t.classroom_id, t.title, t.organisation_id, case when cu.classroom_id is null then 0 else 1 end as pivot_set

from organisation_classrooms t

left join organisation_classrooms_myusers cu on cu.classroom_id=t.classroom_id and cu.user_id=:uid

[/sql]

So, add a join and an extra field to the CDbCriteria:


/* CDbCriteria $criteria already prepared given other search criteria, then: */

$criteria->select = 't.classroom_id, t.title, t.organisation_id, case when cu.classroom_id is null then 0 else 1 end as pivot_set';

$criteria->join = 'left join organisation_classrooms_myusers cu on cu.classroom_id=t.classroom_id and cu.user_id=:uid';

$criteria->params['uid'] = $uid;  /* e.g. 1558 */
3

That does appear to look correct, i will give that a shot shortly thanks - will let you know if that works :)

4

Awesome! That query looks bang on cheers mate

UPDATE! Works great thanks