$Condition With Related Models In Cactivedataprovider

Yii 1.1 General Discussion
1

I have the following tables:




CREATE TABLE w2w_schedule

(

    id int PRIMARY KEY NOT NULL AUTO_INCREMENT,

    shift_type varchar(10),

    date date NOT NULL,

    start varchar(7) NOT NULL,

    end varchar(7) NOT NULL,

    assignment_type int NOT NULL

);

and




CREATE TABLE w2w_shift_employee

(

    id int PRIMARY KEY NOT NULL AUTO_INCREMENT,

    shift_id int NOT NULL,

    employee_id int NOT NULL,

    notes varchar(7)

);

with the following relations:

in shift_employee…


        

return array(

            'schedule' => array(self::BELONGS_TO, "W2wSchedule", 'shift_id'),

        );

    }

and in schedule




        return array(

            'shift_employees' => array(self::HAS_MANY, "W2wShiftEmployee", 'id'),

        );

and I’m trying to create a activeDataProvider to search for a range of date(s) combining both tables. I have the following ADP:




    $date = '2012-12-13';

    $dataProvider=new CActiveDataProvider('W2wShiftEmployee', array(

        'criteria'=>array(

            'with'=> array('schedule'=>

                array('select'=>'t.id,w2w_schedule.date,w2w_schedule.start,w2w_schedule.end'),

                array('condition'=> 't.shift_id = w2w_schedule.id'),

                array('condition'=> "w2w_schedule.date = `$date`")

            ),

            'together'=> true,

        ),

        'pagination'=>array(

            'pageSize'=>10,

        ),

    ));

I’m getting no errors, however the last condition is not being used and Im getting ALL W2w_shift_employee records with the corresponding w2w_schedule data. Please help!

Marquis

2

i think you should change your condition like below




$date = '2012-12-13';

    $dataProvider=new CActiveDataProvider('W2wShiftEmployee', array(

        'criteria'=>array(

            'with'=> array('schedule'=>

                array('select'=>'t.id,w2w_schedule.date,w2w_schedule.start,w2w_schedule.end'),

                array('condition'=> 't.shift_id = w2w_schedule.id and w2w_schedule.date = `$date`')

            ),

            'together'=> true,

        ),

        'pagination'=>array(

            'pageSize'=>10,

        ),

    ));
3

Nope unfortunately that didnt work, the query that was produced was


SELECT `t`.`id` AS `t0_c0`, `t`.`shift_id` AS `t0_c1`, `t`.`employee_id` AS `t0_c2`, `t`.`notes` AS `t0_c3`, `t`.`hasNarrative` AS `t0_c4`, `schedule`.`id` AS `t1_c0`, `schedule`.`date` AS `t1_c2`, `schedule`.`start` AS `t1_c3`, `schedule`.`end` AS `t1_c4` FROM `w2w_shift_employee` `t`  LEFT OUTER JOIN `w2w_schedule` `schedule` ON (`t`.`shift_id`=`schedule`.`id`)  LIMIT 10
4

Dear Friend

Kindly check whether the following is helpful




$date = '2012-12-13';

$dataProvider=new CActiveDataProvider('W2wShiftEmployee', array(

      'criteria'=>array(

            'with'=> array('schedule'=>array(

                   'select'=>'t.id,w2w_schedule.date,w2w_schedule.start,w2w_schedule.end',

                   'condition'=> 't.shift_id = w2w_schedule.id and w2w_schedule.date=:date',

                   'params'=>array(':date'=>$date),

                   'together'=> true)),

                     ),

      'pagination'=>array(

            'pageSize'=>10,

                         ),

    ));

Regards.

5

Yes friend, it was quite helpful!

I accomplished what I wanted with the following code…


        $dataProvider=new CActiveDataProvider('W2wShiftEmployee', array(

        'criteria'=>array(

            'with'=> array('schedule'=>array(

                'select'=>'t.id,schedule.date,schedule.start,schedule.end',

                'condition'=> 't.shift_id = schedule.id AND schedule.date=:date',

                'params'=>array(':date'=>$date),

                'together'=> true)),

        ),

        'pagination'=>array(

            'pageSize'=>10,

        ),

    ));

I guess the mistake was to try to join with the table name INSTEAD of the relations name…

Thanks! :D