I have query that use variables when I try run it on navicat it works well. but in some reason I have issues run it on Yii.
I use this code:
$connection = Yii::app()->db;
$sql = "
select s.name, s.type
from (
select *
, (@rn := if(@cur=type, @rn+1, 1)) as rn
, @cur := type
from games
join (select @cur := '') i
order by
type
) s
where rn <= 10 ";
$command = $connection->createCommand($sql);
$results = $command->queryAll();
this query should select 10 games names from each game type. that what happens when I run it on navicat, when I try it in the site it return me only one game name for each game type. anyone know the reason why it happens? how to fix it? maybe how run it with criteria? please help me.
btw The reason I use this query is because other queries take up a lot of resources.