Parametrized scope and eager loading

Archives Yii 1.0.x Discussions
1

hello all,

I saw in this topic something mentioned about scopes and eager loading.

But I have an other problem. I have the following scope in my CalenderDayUser model:



<?php


public function inMonth($month, $year)


    {


        $end = date('t', mktime(0,0,0,$month,0,$year));


        $this->getDbCriteria()->mergeWith(array(


            'condition'=>'CDUDate BETWEEN 1 AND :end',


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


        ));


        return $this;


    }

But how can I give those parameters in my statement:

$groepLeden = Group::model()->with('users.calendarDayUsers:inMonth($month,$year).schemaItems.timeslot')->findAll($criteria);

above suggestion does'nt work. Yii doesn't either recognize my inMonth function as a scope: Active record class "CalendarDayUser" does not have a scope named "inMonth".

Is there a solution for my problem?

2

This is not supported.

Because you are binding the parameters to the query, you may define the following scope:



public function scopes()


{


   return array(


      'inMonth'=>array('condition'=>'CDUDate BETWEEN 1 AND :end'),


   );


}

And then use it in eager loading as follows:



Group::mode()->with(array(


   'users.calendarDayUsers:inMonth'=>array(


      'params'=>array(':end'=>...),


   ),


))->findAll($criteria);
3

Ok thanks that works!!

One other question: can I also use named scopes on a delete query for example?

CalendarDayUser::model()->inMonth($this->maand, $this->jaar)->concept()->deleteAll();

Because it looks like the generated sql is:

Executing SQL: DELETE FROM CalendarDayUser

4

I just implemented this feature. Thank you for your suggestion!

5

Mmh, that gives me an error:

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':end AND MONTH(CDUDate) = :month AND YEAR(CDUDate) = :year) AND (CDUStatus=0)' at line 1

Yes I did update my svn 1.0 trunk

I execute this command:

CalendarDayUser::model()->inMonth($aantalDagen, $this->maand, $this->jaar)->concept()->deleteAll();

I have also tried it this way, but that doesn't work either:

CalendarDayUser::model()->inMonth(array(':end' => $aantalDagen, ':month' => $this->maand, ':year' =>$this->jaar))->concept()->deleteAll();

my scopes:

public function scopes()

    {

        return array(

            'inMonth'=>array('condition'=>'DAY(CDUDate) BETWEEN 1 AND :end AND MONTH(CDUDate) = :month AND YEAR(CDUDate) = :year'),

            'concept'=>array('condition'=>'CDUStatus=0'),

            'published'=>array('condition'=>'CDUStatus=1'),

        );

    }

Executed SQL:

Executing SQL: DELETE FROM CalendarDayUser WHERE (DAY(CDUDate) BETWEEN 1 AND :end AND MONTH(CDUDate) = :month AND YEAR(CDUDate) = :year) AND (CDUStatus=0)

This scopes works well in the following query:$groepLeden = Group::model()->with(array('users.calendarDayUsers:concept:inMonth'=>array('params' => array(':end' => $aantalDagen, ':month' => $this->maand, ':year' => $this->jaar))))->findAll($criteria);

Or am I doing something wrong?

6

It doesn't work because your scope is parameterized, but you are defining it in scopes() instead of a separate method.

7

Ok, thanks that works!

But it seems a bit strange to me that i need to have 2 scopes for the same thing. I have now a function in my model and I have a scope in scopes(), because I need that for the eagerly loading mentioned before in this topic. Is there no better/cleaner way?

TIA

8

If you only have scopes(), you need to supply the parameters in deleteAll() so that they can be used to bind to the parameter placeholders in the scope.