Hi, guys,
I’m trying to execute something similar to the following query:
(for simplicity, consider this query, I’m aware this is dumb, but it is only to show the PREPARE and EXECUTE commands in the query)
$sql = "
SET @sql='SELECT * FROM df_publication';
PREPARE STMT FROM @sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
";
using:
$result = Yii::app()->db->createCommand($sql)->queryAll();
Which returns
CDbCommand failed to execute the command: SQLSTATE[HY000]: General error. The SQL statement executed was:
Any ideas on how to execute a dynamic query?
The real query is the following. The idea is:
I’ve changed the Samdark’s tagBehavior to use only one table to store the tag_model relation. So I’ve created another table which is automatically handled by the behavior and holds:
model_class_name, model_class_pk_field_name, model_class_description_field_name, table_name_for_model, such as
‘Publication’, ‘id’, ‘title’, ‘df_publication’
In this way I’m able to make all my website contents related, meaning, Publications may be related to Videos, Galleries and so, just linked by tag names.
Now is the funny part:
I want to get from related tables the description of the model, so, I’m trying to use dynamic query.
SET @sql=(
SELECT group_concat(
concat(
'SELECT '',
modelclass.class_name,
'',',
modelclass.description_column,
' FROM ',
modelclass.table_name,
' WHERE ',
modelclass.pk_column,
' = ',
tagmodel.model_id
) SEPARATOR ' UNION ' )
FROM df_tag_model tagmodel
INNER JOIN df_tag tag ON tag.id = tagmodel.tag_id
INNER JOIN df_model_class modelclass ON modelclass.id = tagmodel.model_class_id
WHERE tag.name in ('futebol', 'esportes')
AND tagmodel.model_id <> 2
);
PREPARE STMT FROM @sql;
EXECUTE STMT;
DEALLOCATE PREPARE STMT;
Thanks in advance!
Regards!