[bettor]

Hi all,

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,
B.

[jonah]

I believe you can only use CDBCriteria with CActiveRecord

eg
myModel::model()->find($criteria);

[iGrog]

See CDbCommandBuilder

[bettor]

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?

Thanks,
b.

[bettor]

bettor, on 03 January 2010 - 09:23 AM, said:

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?

Thanks,
b.

Update:

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).

Thanks for your help jonah and iGrog

Cheers,
b.

[Hudson Nguyen]

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;
        }
    }
}
?>

[Dave]

To avoid ambigious column names you should use "??.<column>" in your select or provide an alias and use it instead.

[jonah]

iGrog, on 03 January 2010 - 05:15 AM, said:

See CDbCommandBuilder

I was looking for something like this myself.. awesome

[vladm]

Dave, on 04 January 2010 - 09:53 AM, said:

To avoid ambigious column names you should use "??.<column>" in your select or provide an alias and use it instead.

Are you assured that it work in Yii 1.1? I have found that Yii 1.1 does not support this feature anymore. In any case a next definition won't work:

...
public function relations()
{
  return array(
    'relname' => array(self::HAS_MANY, 'ActiveRecord', 'fk_field', 'condition' => '??.any_field>2', 'alias' => 'relname'),
  );
}
...

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?

[Mike]

vladm, on 11 January 2010 - 11:51 AM, said:

Does anybody have any ideas what we can use instead '??' symbols in Yii 1.1 to avoid ambiguous column names?

Please check the guide on "Disambiguating Column Names" (note the note ):
http://www.yiiframew...de/database.arr

[vladm]

Mike, on 12 January 2010 - 04:34 AM, said:

Please check the guide on "Disambiguating Column Names" (note the note ):
http://www.yiiframew...de/database.arr

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:

...
with(
  array(
    'comments', 
    'comments.authors' => array(
      'condition' => '??.author_id = 1'
    )
  )
)
...

As I understand for new Yii 1.1 behaviour I should do it like this:

...
with(
  array(
    'comments', 
    'comments.authors' => array(
      'condition' => 'comments_authors_that_I_need.author_id = 1',
      'alias' => 'comments_authors_that_I_need'
    )
  )
)
...

Oh my... Two '?' symbols are more simple in usage, you don't think so?

[Mike]

The relations name is used as alias. So it should work like this:

with(array(
    'comments.authors'=>array( 'condition' => 'comments.author_id=1'),
))

[vladm]

Mike, on 13 January 2010 - 03:51 AM, said:

The relations name is used as alias. So it should work like this:

with(array(
    'comments.authors'=>array( 'condition' => 'comments.author_id=1'),
))

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:

...
with(
  array(
    'comments', 
    'comments.authors' => array(
      'condition' => "??.name = 'Mike'"
    )
  )
)
...

[Mike]

Ok, what is the name of the relation to authors in your comments record? If it is authors then try this:

with(array(
    'comments.authors'=>array( 'condition' => "authors.name='Mike'"),
))

At least that's what i'd excpect. Couldn't really try out 1.1 ARs yet, but that's what's stated in the changes/upgrade guide.

And btw.: You shouldn't need the 'comments' entry in your with() array. 'comments.authors' will load comments implicitly.

[vladm]

Mike, on 14 January 2010 - 03:05 AM, said:

Ok, what is the name of the relation to authors in your comments record? If it is authors then try this:

with(array(
    'comments.authors'=>array( 'condition' => "authors.name='Mike'"),
))

At least that's what i'd excpect. Couldn't really try out 1.1 ARs yet, but that's what's stated in the changes/upgrade guide.

And btw.: You shouldn't need the 'comments' entry in your with() array. 'comments.authors' will load comments implicitly.

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.