Issue with SQL Quotes using "IN" clause inside addSelect()

Yii 2.0 General Discussions
1

Hello Guys,

I 'm having a issue when trying to put a subquery in my search using the Yii addSelect function.
The problem is that Yii2 automatically add quotes after the second param inside the IN clause. Heres is the example:

$columns = [
‘ped_id’
];
// creating query
$query = self::find()
->select($columns)

// the issue happens here:
$query->addSelect("
(
SELECT
COUNT (DISTINCT ipm_matricula)
FROM
c24_itempedidos item5
INNER JOIN c24_itempedido_matriculas ON ipm_itempedido_id = item5.itp_id
WHERE
item5.itp_pedido_id = ped_id
AND item5.itp_oficio_id = item.itp_oficio_id
AND item5.itp_certidoes_nome_alternativo_id IN (111, 1111, 11111, 111111) )
AS qtd_matricula"
)…

the generated Sql is:

SELECT DISTINCT
“ped_id”,
(
SELECT
COUNT (DISTINCT ipm_matricula)
FROM
c24_itempedidos item5
INNER JOIN c24_itempedido_matriculas ON ipm_itempedido_id = item5.itp_id
WHERE
item5.itp_pedido_id = ped_id
AND item5.itp_oficio_id = item.itp_oficio_id
AND item5.itp_certidoes_nome_alternativo_id IN (
111,
“1111”,
“11111”,
“11111) ) AS qtd_matricula”…

while the expected result should be

( …
AND item5.itp_certidoes_nome_alternativo_id IN (111,1111,11111,111111) ) AS qtd_matricula"
…

Any suggestions?

Thanks dor your time!

2

It seems like that you have to use the array format or the db expression format for the parameter of addSelect method. The current parameter is considered as a comma separated string.

Please check the API for yii\db\Query::addSelect() and yii\db\Query::select().
https://www.yiiframework.com/doc/api/2.0/yii-db-query#addSelect()-detail
https://www.yiiframework.com/doc/api/2.0/yii-db-query#select()-detail

3

Thank you very much,

I set it as array and its solved my problem!

Just added the [] aorund the string.
The code ended up like this:

$query->addSelect(["
(
SELECT
COUNT (DISTINCT ipm_matricula)
FROM
c24_itempedidos item5
INNER JOIN c24_itempedido_matriculas ON ipm_itempedido_id = item5.itp_id
WHERE
item5.itp_pedido_id = ped_id
AND item5.itp_oficio_id = item.itp_oficio_id
AND item5.itp_certidoes_nome_alternativo_id IN (111, 1111, 11111, 111111) )
AS qtd_matricula"]
);