Результат запроса не работает с типом массива в PostgreSQL

#sql #postgresql

Вопрос:

У меня есть две таблицы, которые содержат столбец с типом данных array в PostgreSQL. Структура выглядит следующим образом:

tbl_tour_packages

tbl_header_images

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

 SELECT 
    t1.tour_id AS pid,
    t1.tour_name AS title,
    t1.tour_duration AS nights,
    t1.tour_price_full AS price,
    t1.discount AS discount,
    t1.tour_seo_title AS seo,
    t3.category AS category,
    t4.image_names[1] AS image_url,
    CASE WHEN max(s.state_name) IS NULL THEN NULL ELSE array_agg(s.state_name) END AS state,
    CASE WHEN max(o.destination) IS NULL THEN NULL ELSE array_agg(o.destination) END AS destinations
FROM tbl_tour_packages t1
    LEFT JOIN tbl_countries t2 ON t1.tour_country_iso = t2.iso
    LEFT JOIN tbl_categories t3 on t1.tour_category_id = t3.id
    LEFT JOIN tbl_header_images t4 ON t1.tour_id = t4.package_id
    LEFT JOIN tbl_states AS s ON (t1.tour_state @> array[s.state_code])
    LEFT JOIN tbl_destinations AS o ON (t1.tour_destination @> array[o.id])
WHERE t1.tour_status = 1
GROUP BY 1,7,8
ORDER BY view_count ASC LIMIT 6

Я хочу получить ‘image_name’ из tbl_header_images. Любая быстрая помощь или предложение будут оценены.

1. Возможно, в image_names нет данных? Это обнуляемое значение. Возможно, пользователь еще не сохранил в нем какие-либо данные, поэтому он равен null. Или, если он не равен null и в нем есть массив, есть ли в нем хотя бы один элемент?

2. Не связано, но: dba.stackexchange.com/questions/154251

Ответ №1:

перед предложением WHERE вы должны быть в состоянии сделать что-то вроде:

, unnest(image_names) _image_names

а затем в операторе select агрегируйте это обратно в массив

array_agg(_image_names) AS image_names

Я не совсем понимаю t4.image_names[1] AS image_url попытку, но я уверен, что вы можете забрать ее отсюда.

таким образом, весь запрос будет выглядеть примерно так:

редактировать: я удалил дополнительную группировку

 SELECT 
    t1.tour_id AS pid,
    t1.tour_name AS title,
    t1.tour_duration AS nights,
    t1.tour_price_full AS price,
    t1.discount AS discount,
    t1.tour_seo_title AS seo,
    t3.category AS category,
    (array_agg(_image_names))[1] AS image_url,
    CASE WHEN max(s.state_name) IS NULL THEN NULL ELSE array_agg(s.state_name) END AS state,
    CASE WHEN max(o.destination) IS NULL THEN NULL ELSE array_agg(o.destination) END AS destinations
FROM tbl_tour_packages t1
    LEFT JOIN tbl_countries t2 ON t1.tour_country_iso = t2.iso
    LEFT JOIN tbl_categories t3 on t1.tour_category_id = t3.id
    LEFT JOIN tbl_header_images t4 ON t1.tour_id = t4.package_id
    LEFT JOIN tbl_states AS s ON (t1.tour_state @> array[s.state_code])
    LEFT JOIN tbl_destinations AS o ON (t1.tour_destination @> array[o.id])
    , unnest(t4.image_names) AS _image_names
WHERE t1.tour_status = 1
GROUP BY 1,7
ORDER BY view_count ASC LIMIT 6

в качестве альтернативы я бы выбрал подвыбор:

 SELECT t1.*,
    (SELECT image_names[1] FROM tbl_header_images WHERE package_id = t1.tour_id) AS image_url
FROM t1, t2, t3
WHERE ...