CODEWITHSUNDEEP
phpsqlpostgresqlyiiwhere-clause
user2045099
user2045099

Reputation: 37

YII query Builder wont show results

I have a problem using two different sintax of yii query builder Using this, works perfectly (but i cannot use variables inside the SQL)

$connection = Yii::app()->db;

$sql='SELECT id_activo, nom_activo, valor_activo, cod_amenaza, tipo_activo, deg_amenaza, 
frec_amaneza FROM activo, amenazas WHERE amenazas.activos_amenaza ILIKE "%"|| activo.tipo_activo ||"%" AND id_activo=1';
$query = str_replace('"',"'",$sql);

$command = $connection->createCommand("$query");
$rows = $command->queryAll();

Using this wont show anything

$i=1;
$rows = Yii::app()->db->createCommand()
->select('id_activo, nom_activo, valor_activo, cod_amenaza, tipo_activo, deg_amenaza, frec_amaneza')
->from('activo, amenazas')
->where('amenazas.activos_amenaza LIKE :substr', array(':substr' => '%activo.tipo_activo%'))
->andWhere('id_activo=:id', array(':id'=>$i))
->queryAll();

if i remove the where condition with the LIKE works fine

Upvotes: 0

Views: 67

Answers (1)

voter
voter

Reputation: 358

Problem is that '%activo.tipo_activo%' is not treated as data from database but just as plain string.

You can rewrite join condition like this: $rows = Yii::app()->db->createCommand() ->select('id_activo, nom_activo, valor_activo, cod_amenaza, tipo_activo, deg_amenaza, frec_amaneza') ->from('activo') ->join('amenazas', "amenazas.activos_amenaza LIKE '%'||activo.tipo_activo||'%' ")
->where('id_activo=:id', array(':id'=>$i)) ->queryAll();

Upvotes: 1

Related Questions