Mysql If With Query Builder

Yii 2.0 General Discussions
1

Hi

How would I execute this command




$query->select('i.id, i.title, i.slug, IF(i.display = '1', 'Public', 'Private') as info,.. rest of query');

Manually in my console it works, but I think due to quoting it fails in Yii2. and says

"your MySQL server version for the right syntax to use near \'AS info"

Jonny

2 

$query->select('i.id, i.title, i.slug, IF(i.display = \'1\', \'Public\', \'Private\') as info,.. rest of query');

Or


$query->select('i.id, i.title, i.slug, IF(i.display = "1", "Public", "Private") as info,.. rest of query');

Or


$query->select("i.id, i.title, i.slug, IF(i.display = '1', 'Public', 'Private') as info,.. rest of query");
3

I actually tried all 3 types of escaping to no avail before posting. It always generates this as the query:




IF(i.display = '1', `'Public'`, `'Private')`
4

When nothing works on escaping characters and you are confused… the HEREDOC syntax can sometime help… B)




$sql = <<< EOT

i.id, i.title, i.slug, IF(i.display = '1', 'Public', 'Private') as info, /* rest of the query */

EOT;


/* or */


$sql = <<< EOT

i.id, i.title, i.slug, IF(i.display = "1", "Public", "Private") as info, /* rest of the query */

EOT;


$query->select($sql);
5

Thanks for that, I am still getting the same error though. I’ll keep looking into this

6

Use an array when your select contains expression: select([column1, column2, ...])

7

Thank you :)