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

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!

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

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"]
);