Findbyattributes With Like

Yii 1.1 General Discussion
1

What I would like to do is find 2 variables from table ULD (idULD, idUser, idLocation, idActivity). I’d like to get idLocation and idActivity and put it into next query to get the data from table OS. The problem is that in dropdown list I want to fill, all I get is: “Inventarna - Naziv (NRedni)” instead of all values that are in the table.

Basically what I want to do is in MySQL sentence:

SELECT * FROM OsnovnoSredstvo WHERE idLocation LIKE (SELECT idLocation FROM ULD WHERE idUser=’.Yii::app()->user->id.’)

AND idActivity LIKE (SELECT idActivity FROM ULD WHERE idUser=’.Yii::app()->user->id.’);

Here is my code:


function getOsnovnoSredstvo()

	{

		$idVars=ULD::model()->find('idUser=:user',array(':user'=>Yii::app()->user->id));

		$OsnovnoSredstvo = OsnovnoSredstvo::model()->findByAttributes(array(),

			$condition  = 'idActivity LIKE :param1 AND idLocation LIKE :param2',

			$params     = array(

				':param1' => $idVars->idActivity,

				':param2' => $idVars->idLocation,

			)

		);

		$list    = ExtHtml::listData($OsnovnoSredstvo , 'Inventarna', array('Inventarna',' - ', 'Naziv', '(','NRedni',')'));

		return $list;

	}
2

I found out that actually my query is working, but I get weird data when trying to print it, quoted part of if:

Anyone knows what I am doing wrong?

3

This wiki link might help you

http://www.yiiframework.com/wiki/199/creating-a-parameterized-like-query/

4

Because I believe that when you do


$idVars=ULD::model()->find('idUser=:user',array(':user'=>Yii::app()->user->id));

you are getting the Active Record object itself, which is not what happens when you use a simple SQL query and go throught the results like


$sql = "SELECT * FROM OsnovnoSredstvo WHERE ...."

$result = mysql_query($sql);


while ($row = mysql_fetch_assoc($result)){

    echo $row["id"];

}

With AR, all you have to do is




foreach ($idVars as $result){

    echo $result->id;

}

If you var_dump $idVars, you will not get the result you are looking for, as that is the Object itself.

More info here

That is my opinion, but hey I’m just starting out :P

Regards,

SilverPT

5

When I do an intentional error in my MySQL code I get the message:

In this message I see the right values of :param1 and :param2. Not sure if idVars is the real problem. Or am I not understanding something?

6

Hi!

Check the CDbCriteria.addSearchCondition

E.g. (not tested)




...

      $idVars = ULD::model()->find('idUser=:user',array(':user'=>Yii::app()->user->id));


      $criteria = new CDbCriteria;

      $criteria->addSearchCondition('idActivity', $idVars->idActivity);

      $criteria->addSearchCondition('idLocation', $idVars->idLocation);

     

      $OsnovnoSredstvo = OsnovnoSredstvo::model()->find($criteria);

...
7

I get the same result with addSearchCondition as before. As it looks like something is wrong with this part of code:


$list = ExtHtml::listData($OsnovnoSredstvo , 'Inventarna', array('Inventarna',' - ', 'Naziv', '(','NRedni',')'));

it is weird, because with a similar code it is working ok:


function getServis()

	{

		$Servis = Servis::model()->findAll();

		$list    = ExtHtml::listData($Servis , 'idServis', array('idServis', 'Naziv'));

		return $list;

	}
8

Hi Rasho,

findByAttributes returns only one AR object or null. You have to use findAllSomething here.

And, you have to add '%'s manually to the bound parameters, when you want to use LIKE.




$params = array(

      ':param1' => '%' . $idVars->idActivity . '%',

      ':param2' => '%' . $idVars->idLocation . '%',

);

BTW, you don’t have to use findAllByAttributes, when you have to pass an empty array as the 1st parameter. :)

9

I’m getting more and more confused here. I have two options that I’m playing with:

OPTION No.1:


		$idVars = ULD::model()->find('idUser=:User',array(':User'=>Yii::app()->user->id));

	

		$criteria = new CDbCriteria;

		$criteria->addSearchCondition('idActivity', $idVars->idActivity);

		$criteria->addSearchCondition('idLocation', $idVars->idLocation);

		 

		$OsnovnoSredstvo = OsnovnoSredstvo::model()->findAll($criteria);

If I do this, I get an empty array in return.

OPTION No.2:


		$idVars = ULD::model()->find('idUser=:User',array(':User'=>Yii::app()->user->id));

		

		$OsnovnoSredstvo = OsnovnoSredstvo::model()->findAll(

			$condition = 'idActivity LIKE :param1 AND idLocation LIKE :param2',

			$params = array(

				':param1' =>$idVars->idActivity,

				':param2' =>$idVars->idLocation,

			)

		);

If I do this I do not even get an error back. It’s just an empty screen. Can not find an error in code. Eclipse is also giving me nothing.

@softark: Do I still need to add ‘%’ if I already have them in database?

10

Well, could this be a legal syntax of calling a method in PHP?

Does it work? And what’s the scope for $condition and $params? I don’t know … I hope someone will teach me.

Anyway, I would write OPTION No.2 like this:




$idVars = ULD::model()->find('idUser=:User',array(':User'=>Yii::app()->user->id));

$condition = 'idActivity LIKE :param1 AND idLocation LIKE :param2';

$params = array(

	':param1' => '%' . $idVars->idActivity . '%',

	':param2' => '%' . $idVars->idLocation . '%',

);

$OsnovnoSredstvo = OsnovnoSredstvo::model()->findAll($condition, $params);

And according to the reference of CDbCriteria::addSearchCondition, this should create the same SQL as OPTION No.1.

CDbCriteria::addSearchCondition will make ready binding barameters for you. So you don’t have to set $params manually when you use it to construct the criteria. But when you want to supply the condition with a plain text, you have to set the binding parameters explicitly.

Now, I think your OPTION No.1 has no error and is working as expected. I mean that the empty array is the right result. So you may want to check the return value of ULD::model()->find.




$idVars = ULD::model()->find('idUser=:User',array(':User'=>Yii::app()->user->id));

// check if $idVars is what you expect here

...
11

Thank you all, guys for a lot of help! The last thing softark wrote did the trick and as I expected I do not even need to put ‘%’ into the code, because I already read % sign from database. Thank you all again for quick replys and big help!