I am performing a relational query using the below method:
$connection = new CDbConnection(Yii::app()->db->connectionString,Yii::app()->db->username,Yii::app()->db->password);
$connection->active=true;
$sql = "SELECT ....."; //from two tables
$command = $connection->createCommand($sql);
$dataReader=$command->query();
Can someone please hint me on where exactly I should apply the $criteria so I can get the pagination and sorting working. It is not working at the moment
Thanks guys. Looks like the CDbCommandBuilder is an option with its applyCondition() method.
Another option that I am currently looking at is applying criteria into AR. However, after applying a join statement such as $criteria->join="LEFT JOIN tbl ON (tbl.id = tbl2.key_id)" is creating me some headache when I attempt to sort my results throu tbl.attribute [this is the table that is joining my current model/table]. Any idea what I am missing here?
Looks like I found a solution [sort of weird tho]. In the given relation above I am only able to sort according to a record of the joined table only if I declare alias in mysql such as:
$criteria->select="tbl1.*,tbl2.attr as Attr";
…only after declaring my select as such Yii allowes me to sort via Attr. In my first attempt I tried to sort throu tbl2.attr and it wouldn’t allow me to do so. Not sure if this is the desired effect in such cases or if such sort is available only of there is a relation declared in my model (which I don’t).
I had kind of ambiguous column name using CSort applyOrder() on a CDbCriteria object with multi-tables but it was Yii 1.0.2. Not sure if that is your headache but I wrote an XSort class to fix the problem like this:
<?php
/**
* This helper fix CSort issue when apply sorting order to a query which run on multiple tables
*/
class XSort extends CSort{
public function applyOrder($criteria)
{
$directions=$this->getDirections();
if(empty($directions))
$order=$this->defaultOrder;
else
{
$schema=CActiveRecord::model($this->modelClass)->getDbConnection()->getSchema();
$orders=array();
foreach($directions as $attribute=>$descending)
{
if(($pos=strpos($attribute,'.'))!==false)
$attribute=$schema->quoteTableName(substr($attribute,0,$pos)).'.'.$schema->quoteColumnName(substr($attribute,$pos+1));
else
$attribute=$schema->quoteColumnName($attribute);
//Add the table to ordering column
$attribute = CActiveRecord::model($this->modelClass)->tableName().'.'.$attribute;
$orders[]=$descending?$attribute.' DESC':$attribute;
}
$order=implode(', ',$orders);
}
if(!empty($order))
{
if(!empty($criteria->order))
$criteria->order.=', ';
$criteria->order.=$order;
}
}
}
?>
In this case Yii 1.1 will not replace ‘??’ symbols to corresponding alias and we will have SQL error. Does anybody have any ideas what we can use instead ‘??’ symbols in Yii 1.1 to avoid ambiguous column names?
Thanks a lot, but old behaviour was more useful in my opinion. For example, if I get AR "Blog" with its comments and their authors, and if I want to get only comments of the defined author what I should do in this case in Yii 1.1? The old behaviour allows me to do something like:
The ‘comments’ is the alias for the ‘comments’ relation but not for the ‘comments.authors’… Your example does not work. Sorry for a little bit incorrect sample, may it will all authors with a name ‘Mike’ instead of author_id , thus the condition should be applied to a ‘name’ field of the ‘authors’ table , old Yii 1.0 syntax below:
Mike, really the problem with ambiguous names is more complex that my sample, because aliases are not autogenerated now I should care out about different names of my relations in my models, e.g. (based on old sample) if I have relation with name ‘authors’ in ‘Blog’ model (the same relation name as in ‘Comment’ model), thus the code below (example 1) will fire SQL error again.
// 1.
...
with(
'authors',
'comments.authors' => array(
"condition" => "authors.name='Mike'"
)
) // here is an SQL error
...
// 2.
...
with(
'authors',
'comments.authors' => array(
'condition' => "another_authors.name='Mike'",
'alias' => 'another_authors'
)
) // here is a right but hard "new" way
...
// 3.
...
with(
'authors',
'comments.authors' => array(
'condition' => "??.name='Mike'",
)
) // here is a simple "old" way that already not supported
...
I don’t know why Yii developers not support ‘??’ aliases anymore, because in my opinion it was quite useful. All aliases were autogenerated and ‘??’ were automatically replaced with them - very good solution and very flexible way.
):
, thus the condition should be applied to a ‘name’ field of the ‘authors’ table , old Yii 1.0 syntax below: