Yii Framework Forum: how to apply $criteria to an sql select - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

how to apply $criteria to an sql select Rate Topic: -----

#1 User is offline   bettor 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 751
  • Joined: 02-February 09

Posted 02 January 2010 - 07:36 PM

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

#2 User is offline   jonah 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 733
  • Joined: 27-November 08
  • Location:California (US)

Posted 03 January 2010 - 01:26 AM

I believe you can only use CDBCriteria with CActiveRecord

eg
myModel::model()->find($criteria);
http://php-thoughts.cubedwater.com - my bloggings about Yii
0

#3 User is offline   iGrog 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 64
  • Joined: 09-October 09

Posted 03 January 2010 - 05:15 AM

See CDbCommandBuilder
0

#4 User is offline   bettor 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 751
  • Joined: 02-February 09

Posted 03 January 2010 - 09:23 AM

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

#5 User is offline   bettor 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 751
  • Joined: 02-February 09

Posted 03 January 2010 - 09:39 AM

View Postbettor, 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.
0

#6 User is offline   Hudson Nguyen 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 95
  • Joined: 19-January 09
  • Location:Vietnam

Posted 04 January 2010 - 06:39 AM

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

Making Yii better ...
0

#7 User is offline   Dave 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 187
  • Joined: 09-October 08

Posted 04 January 2010 - 09:53 AM

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

#8 User is offline   jonah 

  • Advanced Member
  • Yii
  • Group: Yii Dev Team
  • Posts: 733
  • Joined: 27-November 08
  • Location:California (US)

Posted 06 January 2010 - 06:10 PM

View PostiGrog, on 03 January 2010 - 05:15 AM, said:

See CDbCommandBuilder


I was looking for something like this myself.. awesome
http://php-thoughts.cubedwater.com - my bloggings about Yii
0

#9 User is offline   vladm 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 19
  • Joined: 26-December 09

Posted 11 January 2010 - 11:51 AM

View PostDave, 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?
0

#10 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 12 January 2010 - 04:34 AM

View Postvladm, 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
0

#11 User is offline   vladm 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 19
  • Joined: 26-December 09

Posted 12 January 2010 - 09:06 AM

View PostMike, 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?
0

#12 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 13 January 2010 - 03:51 AM

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

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

0

#13 User is offline   vladm 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 19
  • Joined: 26-December 09

Posted 13 January 2010 - 03:09 PM

View PostMike, 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'"
    )
  )
)
...

0

#14 User is offline   Mike 

  • Elite Member
  • PipPipPipPipPip
  • Yii
  • Group: Members
  • Posts: 3,013
  • Joined: 06-October 08
  • Location:Upper Palatinate

Posted 14 January 2010 - 03:05 AM

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

#15 User is offline   vladm 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 19
  • Joined: 26-December 09

Posted 14 January 2010 - 10:22 AM

View PostMike, 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.
-3

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users