How to pass array in CASE WHEN

Question

I want to pass an array in CASE WHEN statement

$friendlist = [];
    foreach ($data as $datas) 
    {
        if ($datas['friend_one'] == $id) 
        {
            $friendlist[] = $datas['friend_two'];
        }
        else
        {
            $friendlist[] = $datas['friend_one'];
        }
    }

$query->select('(CASE WHEN (u1.user_id = u2.user_id) THEN "Added" ELSE (CASE WHEN (u1.user_id = '.$friendlist.') THEN "Paid" ELSE "Received" END) END) as trans_type')

for a single value is ok but when I pass array than it give me "Array to string conversion" error. any solution how to pass an array.

Answer 1

You can't compare array to string or vice versa, at first you should implode your array into string then use in IN:

try this:

$query->select(
  '(CASE WHEN (u1.user_id = u2.user_id) THEN "Added"
  ELSE (CASE WHEN (u1.user_id IN ('. implode(",", $friendlist). ')) THEN "Paid"
  ELSE "Received" END) END) as trans_type');

Answer 2

Simple implode() may open you to SQL Injection - you should escape values before imploding them:

$friendlist = array_map(function ($data) {
    return Yii::$app->db->quoteValue($data);
}, $friendlist);

$query->select([
    'trans_type' => new \yii\db\Expression(
        'CASE WHEN (u1.user_id = u2.user_id) THEN "Added"
            ELSE (
                CASE WHEN (u1.user_id IN (' . implode(',', $friendlist) . ')) THEN "Paid"
                ELSE "Received" END
            ) END'
    ),
]);

Or you can use QueryBuilder and InCondition - this will use prepared statements and params to build IN statement:

$params = [];
$condition = Yii::$app->db->queryBuilder
    ->buildCondition(new InCondition('u1.user_id', 'IN', $friendlist), $params);

$query->select([
    'trans_type' => new \yii\db\Expression(
        'CASE WHEN (u1.user_id = u2.user_id) THEN "Added"
        ELSE (
            CASE WHEN (' . $condition . ') THEN "Paid"
            ELSE "Received" END
        ) END',
        $params
    ),
]);