Using Query Builder with comparisons

I am stuck trying to figure out why the following code snippet does not work as I would expect.

$sql = Yii::app()->db->createCommand()

 ->select('games.datetime, games.score_home_team, games.score_away_team, games.unit_home_team, games.unit_away_team, games.home_team_id, games.away_team_id')


 ->from('reindeer_games games')


 ->where((array('in','games.unit_home_team', ':unitIdArray') or array('in','games.unit_away_team', ':unitIdArray')) and 'games.season_id=:seasonId'), array(':unitIdArray'=>$levels, ':seasonId'=>$season_id))


 ->group('games.home_team_id, games.away_team_id')


 ->queryAll();	

The where clause always evaluates to 1 but the season_id variable is not being enforced properly. I have checked and the season_id’s in the database are not all the same but I am receiving all of the records from every season_id value.

Am I using the proper format for mixing the IN comparisons with another WHERE clause? The only documentation I saw for using the IN clauses shows what I am doing, I have not seen any examples of mixing them with other bound variables.

you could add this in config/main.php, in ‘components’ section, so you get the log of every executed SQL query. this should be helpful for debugging:

		'log'=>array(

			'class'=>'CLogRouter',

			'routes'=>array(

				array(

					'class'=>'CFileLogRoute',

					'levels'=>'error, warning',

				),

				array(

					'class'=>'CFileLogRoute',

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

                                        'categories'=>'system.db.*',

                                        'logFile'=>'sql.log',

				),                


			),

		),

and I suggest you to format your posts so other user can read easily.

In your WHERE it seems there’s a confusion with parenthesis… have you checked that?

yep! unmatched parenthesis. I’m surprised that you didn’t get a parsing error:

->where(                                                          // a

        (                                                         // b

            array('in','games.unit_home_team', ':unitIdArray') 

            or array('in','games.unit_away_team', ':unitIdArray')

        )                                                         // -b

        and 'games.season_id=:seasonId'

       ),                                                         // -a

       array(':unitIdArray'=>$levels, ':seasonId'=>$season_id)

)                                                                // unmatched parenthesis !

Sorry about the confusion, and there was an extra parenthesis in there, but it still gives me the same result:

$sql = Yii::app()->db->createCommand()

->select('games.datetime, games.score_home_team, games.score_away_team, games.unit_home_team, games.unit_away_team, games.home_team_id, games.away_team_id')


->from('reindeer_games games')


->where


(


   (


      array('in','games.unit_home_team', ':unitIdArray')


      or 


      array('in','games.unit_away_team', ':unitIdArray')


   ) 


 	   and 


   'games.season_id=:seasonId'


   , array(':unitIdArray'=>$levels, ':seasonId'=>$season_id)


)


->group('games.home_team_id, games.away_team_id')


->queryAll();	

I hope that helps with the confusion.

Here is the logged output:

Querying SQL: SELECT games.datetime, games.score_home_team,

games.score_away_team, games.unit_home_team,

games.unit_away_team, games.home_team_id, games.away_team_id

FROM reindeer_games games

WHERE 1

GROUP BY games.home_team_id, games.away_team_id. Bound with

:unitIdArray=Array, :seasonId=2

I think there’s an error in ->where() syntax. check here:

http://www.yiiframework.com/doc/api/1.1/CDbCommand#where()-detail

Again I suggest you to format your posts so people who want to help you can read code more easily.

Do not use PHP operators (and, or) in where()

These operators need to be quoted in order to be recognized by query builder, refer to the guide for more info.

I think the correct syntax for your where() is:

->where(

        array(

            'and',

            'games.season_id = :seasonId',

            array(

                'or', 

                array( 'in', 'games.unit_home_team', ':unitIdArray' ), 

                array( 'in', 'games.unit_away_team', ':unitIdArray' )

            )

        ),

        array(':unitIdArray'=>$levels, ':seasonId'=>$season_id)

)

Then I’m not an expert of SQL but I think you only use GROUP BY + HAVING when you have cumulative functions like COUNT(*)… check MySQL reference.

I didn’t catch that AND and OR had to be done that way, thanks. I have made the changes, but now I get the error:

CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens. The SQL statement executed was:

SELECT games.datetime, games.score_home_team, games.score_away_team, games.unit_home_team, games.unit_away_team, games.home_team_id, games.away_team_id

FROM reindeer_games games

WHERE (games.season_id = :seasonId) AND ((games.unit_home_team IN (’:unitIdArray’)) OR (games.unit_away_team IN (’:unitIdArray’)))

GROUP BY games.home_team_id, games.away_team_id. Bound with :unitIdArray=Array, :seasonId=2

I use both variables :unitIdArray and :seasonId in the WHERE statement so I am not sure why it says I have a mismatch. You don’t have to list every instance of a variable if its used more than once do you?

I appreciate everyone’s help by the way.

PDO::prepare()

Try to use :unitIdArray1, :unitIdArray2 instead of repeating :unitIdArray parameter marker.

Good to know, but still the problem persists even after changing it.

maybe :unitIdArray shouldn’t be a PHP array, maybe it should be a list of IDs.

An example of sql syntax is "WHERE id IN (3,5,10,11)". Is $levels an array of objects or IDs?

If it’s a simple array of IDs try changing this line:

array(':unitIdArray'=>implode(',',$levels), ':seasonId'=>$season_id)

and let us know the result

PS: I tried to decode this obscure piece of code but I couldn’t understand if you need to pass a string or an array

Tried it but still not correct.

CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens. The SQL statement executed was:

SELECT games.datetime, games.score_home_team, games.score_away_team, games.unit_home_team, games.unit_away_team, games.home_team_id, games.away_team_id

FROM reindeer_games games

WHERE (games.season_id = :seasonId) AND ((games.unit_home_team IN (’:unitIdArray1’)) OR (games.unit_away_team IN (’:unitIdArray2’)))

GROUP BY games.home_team_id, games.away_team_id. Bound with :unitIdArray1=2,3,4, :unitIdArray2=2,3,4, :seasonId=2

I wonder if the apostrophes in “IN (’:unitIdArray1’)” are causing the problem I am not sure how to get them out though.

Same problem

It appears to be the single quotes. I still don’t know how to get around this though.

This appears to have been officially answered here

still for debugging purposes, open phpmyadmin and try to run the query and see if it gives the expected results. (put parameters into the query, without binding)

SELECT games.datetime, games.score_home_team, games.score_away_team, games.unit_home_team, games.unit_away_team, games.home_team_id, games.away_team_id

FROM reindeer_games games

WHERE (games.season_id = 2) AND ((games.unit_home_team IN (‘2,3,4’)) OR (games.unit_away_team IN (‘2,3,4’)))

GROUP BY games.home_team_id, games.away_team_id

try it with and without the single quotes in the IN statement