AR question

Archives Yii 1.0.x Discussions
1

I have next tables:

user

id

name

isActive

…

email

id

userId

isPrimary

…

I need condition to select all emails by user isActive flag & name (based on DBCriteria or attributes). how it may looks like?

2

Quiang, why condition of sql not translated to it's aliases?

example:

SELECT user.id AS t0_c0, user.userStatusId AS t0_c1…

WHERE userStatusId>? LIMIT 1

why "userStatusId>?" instead "t0_c1>?"

3

In your Email model, declare a 'userFilter' relation like the following:



'userFilter'=>array(self::BELONGS_TO, 'user', 'userId', 'condition'=>'??.userStatusId>:userStatusId'),

Then you can query as follows,



Email::model()->with(array(


     'userFilter'=>array(


         'params'=>array(':userStatusId'=>$userStatus),


     ),


))->findAll();

Note, this requires the latest SVN.

An alternative approach is to use the 'join' option in CDbCriteria and explicitly join the two tables to perform query.

And yet another approach is to write SQL by yourself and use findAllBySql().

SQL syntax doesn't allow you to use alias in condition. Here we use the prefix "??." to disambiguate the column references.

4

Thanks, Qiang, but why you didn't use the simple syntax, like:

Email::model()->with('user', 'email')->findAllByAttributes(array('user.statusId' => 1, 'email.isPrimary' => 1));

????

5

Yeah, that’s fine, too. ;)

6

We can expect such a syntax?  ::)

7

Yes. It's valid.

8

There are any projections?? Next month/build/year? :)

(among other things, you can calculate how many of code we need to write to create filter-relations in such case, if the cross-queries are carried out between 5-10 tables with 4-7 fields)

9

You can use it now. Remember to declare 'alias' option in your relation.

10

Insufficient documentation… + Too few examples of AR…

UserEmail::model()->with(array(

'user' => array(


	'alias' => 'user',


))

)->findByAttributes(array('user.userStatusId' => 1));

  • it’s not work :)

where is my mistake?

11

Please state the error message and call stack.

12 
CDbException


Description





Table "user_email" does not have a column named "user.userStatusId".


Source File





...frameworkdbschemaCDbCommandBuilder.php(540)





00528:                         $values[]=$value;


00529:                     }


00530:                     else


00531:                     {


00532:                         $conditions[]=$table->rawName.'.'.$column->rawName.'=:'.$name;


00533:                         $values[':'.$name]=$value;


00534:                     }


00535:                 }


00536:                 else


00537:                     $conditions[]=$table->rawName.'.'.$column->rawName.' IS NULL';


00538:             }


00539:             else


00540: throw new CDbException(Yii::t('yii','Table "{table}" does not have a column named "{column}".',


00541:                     array('{table}'=>$table->name,'{column}'=>$name)));


00542:         }


00543:         $criteria->params=array_merge($values,$criteria->params);


00544:         if(isset($conditions[0]))


00545:         {


00546:             if($criteria->condition!=='')


00547:                 $criteria->condition=implode(' AND ',$conditions).' AND ('.$criteria->condition.')';


00548:             else


00549:                 $criteria->condition=implode(' AND ',$conditions);


00550:         }


00551:         return $criteria;


00552:     }





Stack Trace





#0 ...frameworkdbarCActiveFinder.php(128): CDbCommandBuilder->createColumnCriteria(Object(CMysqlTableSchema), Array, '', Array)


#1 ...protectedcontrollersSiteController.php(17): CActiveFinder->findByAttributes(Array)


#2 ...frameworkwebactionsCInlineAction.php(32): SiteController->actionIndex()


#3 ...frameworkwebCController.php(259): CInlineAction->run()


#4 ...frameworkwebfiltersCFilterChain.php(128): CController->runAction(Object(CInlineAction))


#5 ...frameworkwebCController.php(242): CFilterChain->run()


#6 ...frameworkwebCController.php(219): CController->runActionWithFilters(Object(CInlineAction), Array)


#7 ...frameworkwebCWebApplication.php(343): CController->run('index')


#8 ...frameworkwebCWebApplication.php(119): CWebApplication->runController('site/index')


#9 ...frameworkbaseCApplication.php(170): CWebApplication->processRequest()


#10 ...index.php(11): CApplication->run()


#11 {main}
13

Sorry, I was wrong. findAllByAttributes requires that the attributes belong to the primary AR. In this case, you should put it in a condition:



...->findAll('user.userStatusId=1')
14

ok, thx