Using : in a SQL statement passed to buildQuery

Yii 2.0 General Discussions
1

Hello,

I have a problem: in the following SQL statement, the :* part (required for partial fulltext search in Postgresql) gets evaluated as a parameter, causing a "incorrect parameter number" exception:




SELECT ga.geonameid AS id, ga.alternatename AS text

FROM geonames_alternatenames ga 

   INNER JOIN geonames_countryinfo gac ON gac.geonameid=ga.geonameid 

   INNER JOIN geonames_countryinfo ci ON ci.geonameid=ga.geonameid 

   WHERE ga.isolanguage='{$l}' AND ga.alternatename_tsvector @@ to_tsquery(':q:*');

Is it possible to escape this :* part? Or any workaround? Note: the :q is a valid parameter.

Thanks.

2

Solved:




$q = str_replace(' ', '&', $q) . ':*';


$sql =<<<EOP

SELECT ga.geonameid AS id, ga.alternatename AS text

FROM tienda.geonames_alternatenames ga 

   INNER JOIN tienda.geonames_countryinfo gac ON gac.geonameid=ga.geonameid 

   INNER JOIN tienda.geonames_countryinfo ci ON ci.geonameid=ga.geonameid 

   WHERE ga.isolanguage='{$l}' AND ga.alternatename_tsvector @@ to_tsquery(?);

EOP;

     	$command = $db->createCommand($sql, [1=>$q]);