How to fetchAllBySql with a WHERE IN clause using [email protected]?

Question

A legacy project uses [email protected]. I want to create a query inside a CActiveRecord.

The query in raw sql would be:

SELECT column
FROM table
WHERE some_id in (1, 32, 10)

and it works fine when I query the DB directly. I get all the results.

I map that in my active record (class MyActiveRecord extends CActiveRecord) via following method in yii:

    public function getColumn(array $someIds): array
    {
        $idList = rtrim(implode(', ', $someIds), ', ');
        $sql    = "SELECT column FROM table WHERE some_id IN (:idList)";

        $results = parent::model(__CLASS__)->findAllBySql(
            $sql,
            ['idList' => $idList]
        );

        return array_column($results, 'column');
    }

The code works but it strangely only fetches the result of the first some_id. How do I fetch all of them?

Say that my for my id 1 I have 5 results, for 32 I have 3, and for 10 I have 2 results, I expect a total of 10 records.

Yet I only get back the 5 results for the id 1 within yii. What am I doing wrong?

(When I changed the order of the id, always only the first id will be fetched, the others ignored.)

Answer 1

Your code will create query like SELECT column FROM table WHERE some_id IN ('1, 32, 10') - you're using one param and it is passed to query as one string value. To pass each ID independently, you must use multiple params:

$results = parent::model(__CLASS__)->findAllBySql(
   'SELECT column FROM table WHERE some_id IN (:id1, :id2, :id3)',
    ['id1' => 1, 'id2' => 32, 'id3' => 10]
);

Or avoid using params and use concatenation/interpolation, but you need to sanitize/quote values first, to ensure that there is no SQL Injection risk:

$ids = implode(', ', array_map('intval', $someIds);
$results = parent::model(__CLASS__)->findAllBySql(
   "SELECT column FROM table WHERE some_id IN ($ids)"
);

But probably the most safe and convenient option is to use CDbCommand to build your query:

$result = Yii::app()->db->createCommand()
    ->select('column')
    ->from('table')
    ->where(['IN', 'id', $ids])
    ->queryColumn();