Sql Query Where In Sentence

Yii 1.1 General Discussion
1 

$criteria->addCondition('idDejavnost LIKE (SELECT idDejavnost FROM epmo.ULD WHERE idUporabnik='.Yii::app()->user->id.')');

		$criteria->addCondition('idLokacija LIKE (SELECT idLokacija FROM epmo.ULD WHERE idUporabnik='.Yii::app()->user->id.')','AND');

This is the last two lines of my code from criteria in model. I would like to change that so that I will be able to insert an array of variables to condition. I thought that this would work:


$criteria->addCondition('idDejavnost IN (SELECT idDejavnost FROM epmo.ULD WHERE idUporabnik='.Yii::app()->user->id.')');

		$criteria->addCondition('idLokacija IN (SELECT idLokacija FROM epmo.ULD WHERE idUporabnik='.Yii::app()->user->id.')','AND');

but as I see it, just the first part of my variable is recognised. The variable that is read by:


SELECT idDejavnost FROM epmo.ULD WHERE idUporabnik='.Yii::app()->user->id

is 101,102. But at the moment my program only returns me data with 101.

Interesting part is that if I post 101,102 directly to the query that is without subquery, the query is working just fine. Could it be that the problem is that idLocation and idDejavnost in one query are integers and in query that is called from subquery both fields are varchar? How is then possible that Like version is working?

2

Hi,

Try like this. i hope it will solve your problem

MyModel::model()->findAllBySql("SELECT * FROM MyModel WHERE Column1=:a AND Column2 IN

                                      (SELECT Column2 FROM OtherModel WHERE Column3=:<img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='B)' />&quot;,


                                      array(':a'=&gt;'SomeValue',':b'=&gt;'SomeOtherValue'));

Thanks

chandran nepolean

3

I do not think that we understand each other. I need to have this in criteria.

4

Wait, you actually store ids in the database as strings concatenated with a coma. Why would you do this? It’s a sign of a bad database design.

You could handle it though. In PostgreSQL you could explode the string into an array and search through that array but the manual even tells when you need to search arrays it’s a sign of bad database design. You should use tables and joins.

And you were right, mixing integers and varchars is asking for trouble with casting and comparing etc.

5

The second option I have is that I would have multiple rows for single user. But still I need to have array in search. I do not know how to tell database to search from multiple Locations(idLokacija) and Activities(idDejavnost)?

And yes, I do understand that separating values is a bad design, but I’m looking for a solution for my problem, because the primary design of DB only provide one Location(idLokacija) and Activity(idDejavnost).

6

The solution is to use a good database design, so you don’t have to write a lot more code to account for the bad design. With the right indexes it will be way faster too.

7

I do not know how a good database design would be to assign multiple Location(idLokacija) and Activity(idDejavnost) to one user not using arrays. Can you give me an example?

8

That sounds like a MANY_MANY relationship between your users and some other table. You need a junction table for this. A junction table is made from two columns that both are a primary key (composite - multicolumn) and both are foreign keys pointing to other tables.

9

As I understand this I will still need array to get all possible Locations and Activities for my users. Correct?

10

You probably should try posting an example database schema and describe the relations you are talking about.

11

Here’s the database example:

Databases:

USER(idUser, username, name, email)

LOCATION(idLocation, nameLocation, addressLocation)

ACTIVITY(idActivity, nameActivity)

U(ser)L(ocation)A(ctivity)(idUser,idLocation,idActivity)

That is basically how the database was set up at the beginning. But now what I am trying to do is to change ULA table to accept multiple values in idLocation and idActivity variables. Understandable?

12

It looks fine, I would just adjust the naming:

  • activity would be activityType

  • ULA would become activity

Then add an id to the activity table (ULA) and it would have three BELONGS_TO relations to users, locations and activityTypes.

I know the id is not necessary as the three FK columns combined make an unique key but it makes it looks like an ordinary table not a junction table for a MANY_MANY relation, which it’s not.

13

So you agree with me, that it is good database structure? I still do not see a good way on how I can have multiple locations or activities for a single user…

14

I am still looking for some ideas or possibilities to solve this matter. Looks like I won’t be able to solve it myself.

15

In the table you mentioned:

U(ser)L(ocation)A(ctivity)(idUser,idLocation,idActivity)

you store everything as a single value, so one activity can have multiple rows for each user and each location (and vice versa for other fields).

Just adjust the naming as I suggested in the previous post.

16 

$criteria->addCondition('idDejavnost IN (SELECT idDejavnost FROM epmo.ULD WHERE idUporabnik='.Yii::app()->user->id.')');

                $criteria->addCondition('idLokacija IN (SELECT idLokacija FROM epmo.ULD WHERE idUporabnik='.Yii::app()->user->id.')','AND');

That is the code that should be working if I do the same as you suggested?

17

You may use $criteria->addInCondition(‘idLokacija’,$list);

where $list contains array of values to compare your values with.

18

Yes, it looks all right. You could do that with a join, without a subquery, but you’d have to enable fetching distinct values or enable grouping and it would probably be a little slower.

19 

$sql_dejavnost="SELECT DISTINCT idDejavnost FROM epmo.ULD WHERE idUporabnik=".Yii::app()->user->id;

$p_dejavnost=ULD::model()->findAllBySql($sql_dejavnost);

$dejavnost=array();

foreach($p_dejavnost as $project):

	$dejavnost[]=$project->idDejavnost;

endforeach;

		

$sql_lokacija="SELECT DISTINCT idLokacija FROM epmo.ULD WHERE idUporabnik=".Yii::app()->user->id;

$p_lokacija=ULD::model()->findAllBySql($sql_lokacija);

$lokacija=array();

foreach($p_lokacija as $project):

	$lokacija[]=$project->idLokacija;

endforeach;

…


$criteria->addInCondition('idDejavnost', $dejavnost);

$criteria->addInCondition('idLokacija', $lokacija);

This is now working. But now I find out that I get all possible combinations that are possible from ULD table. Example:

USER;LOCATION;ACTIVITY

0001;101;101

0001;102;101

0001;202;301

With what I have, I get all this:

0001;101;101 GOOD

0001;101;301 WRONG

0001;102;101 GOOD

0001;102;301 WRONG

0001;202;101 WRONG

0001;202;301 GOOD

That is wrong. I only would like to get options that are written in the table. Is there any chance that I could do an "array" of conditions in addInCondition clause? Like


addInCondition(array('idLocation','idActivity'), $array_location_activity)

Does not work for me and it probably is a stupid idea. Any ideas?

20

You create your conditions the wrong way. What you get now is:




idDejavnost IN (x,y,z) AND idLokacija IN (a,b,c)

What you want is:




(idDejavnost = x AND idLokacija = a) OR (idDejavnost = y AND idLokacija = <img src='http://www.yiiframework.com/forum/public/style_emoticons/default/cool.gif' class='bbc_emoticon' alt='B)' /> OR (idDejavnost = z AND idLokacija = c)

In PostgreSQL this could be shortened by using row expression, by you probably are using MySQL.

I don’t know why you fetch location and activity separately as they should be in pairs anyway.