LIKE in WHERE clause in CDbCriteria

Archives Yii 1.0.x Discussions
1

Hi,

How could I use LIKE in CDbCriteria? I tried this but did not work ;(

$acc = $accNo . ‘%’;

$criteria = new CDbCriteria;

$criteria->condition = "TransactionDate >= :start AND TransactionDate <= :end AND AccountFK [color="#FF0000"]LIKE [/color]:account";

$criteria->params = array (

		':start' =&gt; &#036;start,


		':end' =&gt; &#036;end,


		':account' =&gt; &#036;acc,


	     );

Anyone can help?

Thx,

Daniel

2

Define what you mean by “it didn’t work”. Did the query throw an error or did it just not return the expected result?

Is it possible for you to post the SQL that is being generated using this Criteria as well as what you are using to make the query?

3

Thx for the quick reply.

If I used,

$acc = $accNo . ‘%’;

$criteria = new CDbCriteria;

$criteria->condition = “TransactionDate >= :start AND TransactionDate <= :end AND AccountFK [color=”#FF0000"]LIKE ‘:account’"[/color];

$criteria->params = array (

		':start' =&gt; &#036;start,


		':end' =&gt; &#036;end,


		':account' =&gt; &#036;acc,


	     );

noticed that :account is surrounded by ', it gives me an error.

If I removed the ', become -> [color="#FF0000"]LIKE :account"[/color];, it works but not all transactions are returned.

I intended to get all transactions start with 12. Example:

121 Bank A

122 Bank B

I use % to get all all transactions involving Bank A and B.

How can I obtain the SQL produced by the CDbCriteria?

Many thanks,

Daniel

4

In the most recent versions of Yii (1.0.10 or 1.1) you can use:




$criteria->addSearchCondition('accountFK', $acc);

See http://www.yiiframework.com/doc/api/CDbCriteria#addSearchCondition-detail for more info

5

in the config.php

…




        'log'=>array(

            'class'=>'CLogRouter',

            'routes'=>array(

                array(

                    'class'=>'CFileLogRoute',

                    'levels'=>'error, warning,trace',

                ),

            ),

        ),

and see runtime/application.log

6

Solution (mysql):




$criteria->condition = "TransactionDate >= :start AND TransactionDate <= :end AND AccountFK LIKE concat(:account,'%')";
7

This could work, and I did look at this, but with the default arguments it adds ‘%’ to the front and back of the value, which is not the intended result. You could use something like this:




$criteria->addSearchCondition('accountFK', $acc, false);

but all the addSearchCondition does is add a new condition with “$column LIKE :ycp3”, then add a param of $params[’:ycp3’] = $acc, so he’s doing the same thing it does already. I don’t think that’s really the problem.

I’m going to say that using a concat in the query is a hack. PDO is designed so you don’t have to do that. If you have do that, then there is something else wrong somewhere (and that something may even be a bug in the PDO driver).

From everything I see, what he is doing should work fine.

Can you give us the entire code snippet where you are using the criteria? I just did some testing on my box and it looks like what your doing should work…

8

Thank you very much to all of you for the best feedback and responses.

The query is work okay, the problem is with the pagination ;( But, I like use your suggestion (addSearchCondition). You are right. If we use php hack then what would be the point of using Yii? I understand that Yii may not have the feature we need, but then it is our task to make it available.

Once again. Thank you for the help.

9

Thnks…

This method is very easy to Understand and also working…

Thanks again…

10

I realize this is a relative old topic, but I thought I should post the solution I found, which I believe is better. In your case the conditions could be written like this




$criteria->condition = "AccountFK LIKE :account";

$criteria->params = array(':account' => trim($acc) . '%');