iTopiaBlog

Hi Volks,

today I had a lot of testing with the symfony criteria object. And everyone who knows about the problem to get the raw SQL query out of the criteria object.

There is a toString method, but the result just looks like this:



SELECT FROM media, media_category WHERE media.FEATURED=? AND media.MEDIATYPE IN (?,?) AND 1=1 AND media_category.CID NOT IN (?,?,?,?) AND media.MID=media_category.MID GROUP BY media.MID
Parameters to replace: array (
0 =>
array (
'table' => 'media',
'column' => 'FEATURED',
'value' => true,
),
1 =>
array (
'table' => 'media',
'column' => 'MEDIATYPE',
'value' => 'video',
),
2 =>
array (
'table' => 'media',
'column' => 'MEDIATYPE',
'value' => 'audio',
),
3 =>
array (
'table' => 'media_category',
'column' => 'CID',
'value' => 10,
),
4 =>
array (
'table' => 'media_category',
'column' => 'CID',
'value' => 9,
),
5 =>
array (
'table' => 'media_category',
'column' => 'CID',
'value' => 4,
),
6 =>
array (
'table' => 'media_category',
'column' => 'CID',
'value' => 7,
)
)

And it is very strange to fill the question marks by hand with the related values from the parameters array.

So I wrote a function which does this for me:

function getCriteriaSQL($c, $select = '*')
{
$params = array();
$r = BasePeer::createSelectSql($c, $params);
$o = str_replace('?', '%s', $r);

$ggg = array();
foreach($params AS $e)
{
if(is_integer($e['value']) OR is_bool($e['value']))
{
$ggg[] = $e['value'];
}
else
{
$ggg[] = "'".$e['value']."'";
}
}

$sql = vsprintf($o, $ggg);

$sql = str_replace('SELECT ', 'SELECT '.$select, $sql);

return $sql;
}

I just wanted to share this with you and I appreciate your comments.