Выборка MySQL с подзапросом значений из массива

Question

Есть массив:

 $array = [
     '0' => 'Красный',
     '1' => 'Синий',
     '2' => 'Зеленый',
 ];

Соответственно $array[0] это имеет значение "Красный"

Есть SQL запрос:

SELECT id, number, color FROM users WHERE status=1

В Yii2 выглядит так:

$query = User::find()
            ->select(['id','number', 'color'])
            ->where(['status'=>1]);

В таблице users колонка color имеет циферные значения (0,1,2 или 3 и т.д.).
Каким образом мне подставить автоматически значения, чтобы color выводил не 0 а "красный"? т.е. чтобы было нечто подобное:

 SELECT id, number, $array[`color`] AS color_2 FROM users WHERE status=1

Пробовал нечто подобное:

SELECT id, number, CASE color 
      WHEN 0 THEN 'Красный'
      WHEN 1 THEN 'Синий'
      WHEN 2 THEN 'Зеленый'
      ELSE 'Unknown'
FROM users;

Но если массив большой, то CASE вручную прописывать слишком затратно, т.к. начальный массив может дополняться.

Answer 1

Создать вторую таблицу

colors (id, name)

0, Красный
1, Синий
2, Зеленый

в запросе LEFT JOIN сделать

SELECT 
    users.id, users.number, IF(colors.name iS NULL,"Unknown", colors.name) AS color_2
FROM 
    users
LEFT JOIN 
    colors
ON 
    users.color = colors.id
WHERE 
    users.status=1

Answer 2

Преобразуйте колонку в тип ENUM - будут возвращаться текстовые значения, соответствующие кодам. При этом числовые будут обрабатываться правильно. Для получения чисел - прибавляйте ноль.

create table test (id INT, color INT);
INSERT INTO test select 1,1 union select 2,2;
SELECT * FROM test;


id | color
-: | ----:
 1 |     1
 2 |     2

ALTER TABLE test CHANGE COLUMN color color ENUM('red', 'green', 'blue');
select id, color, color + 0 color_code FROM test;


id | color | color_code
-: | :---- | ---------:
 1 | red   |          1
 2 | green |          2

db<>fiddle here

Answer 3

Создаешь класс для хранения ассоциации номера с текстовым названием цвета и метод для получения названия по номеру.

class ColorHelper
{
    public const COLOR_DEFAULT = 0;
    public const COLOR_RED = 1;
    public const COLOR_GREEN = 2;
    
    public const COLOR_MAP = [
        self::COLOR_DEFAULT => 'yellow',
        self::COLOR_RED => 'red',
        self::COLOR_GREEN => 'green',
    ];

    /**
     * @param int $color
     * @return string
     */
    public static function getColor(int $color): string
    {
        return self::COLOR_MAP[$color] ?? self::COLOR_MAP[self::COLOR_DEFAULT];
    }
}

В классе модели добавляешь метод, который будет возвращать текстовое название цвета

/**
 * @return string
 */
public function getColorAsText(): string
{
    return ColorHelper::getColor($this->color);
}

В клиентском коде получаешь название цвета.

$model->getColorAsText();

БЛОГ НА HUSL

Выборка MySQL с подзапросом значений из массива

Ответы (3 шт):