Я получил ошибку с подзапросом для таблицы перевода

Использование Postgressql 10 У меня была функция для группировки данных для подсчета голосов и группировки по имени artist_name (table.field artist.name)

CREATE OR REPLACE FUNCTION public.rt_reports_artists_votes_ratings(p_artist_list integer[], p_created_at_from timestamp without time zone, p_created_at_till timestamp without time zone, p_sort_type character varying DEFAULT 'by_sum_asc'::character varying, p_limit integer DEFAULT NULL::integer)
 RETURNS TABLE(created_at date, artist_name character varying, artist_id integer, vote_sum bigint, vote_qty bigint)
 LANGUAGE sql
AS $function$

SELECT cast( av.created_at as date) AS created_at, a.name as artist_name,
   av.artist_id,
   sum( av.vote ) AS vote_sum,
   count( av.id ) AS vote_qty
  from rt_artist_votes as av join
      rt_artists as a on a.id = av.artist_id
  WHERE ( CASE when p_artist_list IS NOT NULL THEN av.artist_id = ANY (p_artist_list) else true END ) AND

       ( av.created_at BETWEEN coalesce(p_created_at_from,rt_f_min_timestamp()) AND coalesce(p_created_at_till,rt_f_max_timestamp()) )


  group by cast( av.created_at as date), av.artist_id, artist_name
  ORDER by cast( av.created_at as date) asc,
     CASE WHEN p_sort_type = 'by_sum_asc' THEN
      sum( av.vote )
        end ASC,
     CASE WHEN p_sort_type = 'by_sum_desc' THEN
      sum( av.vote )
        end DESC,
     CASE WHEN p_sort_type = 'by_sum_artist_name' THEN
      a.name
  end ASC

  LIMIT p_limit ;

$function$

он работал нормально, но я переделал структуру db для многоязычной поддержки, поэтому я добавил таблицу:

CREATE TABLE public.rt_artist_translations (
    id serial NOT NULL,
    artist_id int4 NOT NULL,
    "name" varchar(50) NOT NULL,
    info text NOT NULL,
    locale varchar(2) NOT NULL,

и я пытаюсь переделать эту функцию, когда я пытаюсь получить имя_страницы из подзапроса:

CREATE OR REPLACE FUNCTION public.rt_reports_artists_votes_ratings(p_artist_list integer[], p_created_at_from timestamp without time zone, p_created_at_till timestamp without time zone, p_sort_type character varying DEFAULT 'by_sum_asc'::character varying, p_limit integer DEFAULT NULL::integer)
 RETURNS TABLE(created_at date, artist_name character varying, artist_id integer, vote_sum bigint, vote_qty bigint)
 LANGUAGE sql
AS $function$

SELECT cast( av.created_at as date) AS created_at, ( SELECT a_t.name FROM rt_artist_translations AS a_t WHERE a_t.artist_id = av.artist_id AND a_t.locale= 'en' ) as artist_name,

   av.artist_id,

   sum( av.vote ) AS vote_sum,

   count( av.id ) AS vote_qty

  from rt_artist_votes as av join


      rt_artists as a on a.id = av.artist_id

  WHERE ( CASE when p_artist_list IS NOT NULL THEN av.artist_id = ANY (p_artist_list) else true END ) AND

       ( av.created_at BETWEEN coalesce(p_created_at_from,rt_f_min_timestamp()) AND coalesce(p_created_at_till,rt_f_max_timestamp()) )


  group by cast( av.created_at as date), av.artist_id, artist_name

  ORDER by cast( av.created_at as date) asc,

        CASE WHEN p_sort_type = 'by_sum_asc' THEN

            sum( av.vote )

        end ASC,

        CASE WHEN p_sort_type = 'by_sum_desc' THEN

            sum( av.vote )

        end DESC,

        CASE WHEN p_sort_type = 'by_sum_artist_name' THEN

            artist_name

        end ASC

  LIMIT p_limit ;

$function$

Но я получил ошибку:

SQL Error [42703]: ERROR: column "artist_name" does not exist

Поскольку я использую имя_страницы в порядке / в конце функции.

Как это можно исправить?

Спасибо!

Всего 1 ответ


Как правило, в SQL вы не можете ссылаться на псевдоним столбца, такой как имя_странителя в предложениях GROUP BY и ORDER BY . Вам нужно указать базовый именованный источник, который в этом случае является подзапросом. Вызвать SELECT иронии судьбы, поскольку первое указанное предложение обычно является последним (или рядом последним) порядком операций между большинством движков баз данных.

Тем не менее, будет более эффективно перемещать подзапрос в производную таблицу (или CTE) для вычисления столбца один раз, а не отдельно для каждой строки! При таком подходе вы можете ссылаться на имя столбца во всех предложениях внешнего запроса. Рассмотрим следующую настройку:

 SELECT CAST( av.created_at as date) AS created_at,
        tr.artist_name,    
        av.artist_id,    
        SUM( av.vote ) AS vote_sum,    
        COUNT( av.id ) AS vote_qty

 FROM rt_artist_votes AS av 
 JOIN rt_artists AS a on a.id = av.artist_id
 JOIN 
     -- DERIVED TABLE
     (SELECT a_t.artist_id, a_t.name as artist_name
      FROM rt_artist_translations AS a_t 
      WHERE a_t.locale = 'en'
     ) AS tr
   ON tr.artist_id = av.artist_id

 WHERE (CASE 
            WHEN p_artist_list IS NOT NULL 
            THEN av.artist_id = ANY (p_artist_list) 
            ELSE true 
        END) 
   AND
       ( av.created_at BETWEEN coalesce(p_created_at_from, rt_f_min_timestamp()) 
                           AND coalesce(p_created_at_till, rt_f_max_timestamp()) 
       )    

 GROUP BY CAST( av.created_at as date), 
          av.artist_id, 
          tr.artist_name

 ORDER by CAST(av.created_at as date) ASC,
          CASE WHEN p_sort_type = 'by_sum_asc' 
               THEN SUM( av.vote )
          END ASC,
          CASE WHEN p_sort_type = 'by_sum_desc' 
               THEN SUM( av.vote )
          END DESC,
          CASE WHEN p_sort_type = 'by_sum_artist_name' 
               THEN tr.artist_name
          END ASC

Вы можете просто просто присоединиться к таблице переводов, но здесь во всех предложениях должно быть указано во всех предложениях не его псевдоним, artist_name .

 ...
 SELECT ... tr.name AS artist_name ...
 FROM rt_artist_votes AS av 
 JOIN rt_artists AS a on a.id = av.artist_id
 JOIN rt_artist_translations AS tr ON tr.artist_id = av.artist_id AND tr.locale = 'en'
 WHERE ...
 GROUP BY ... tr.name ...

Есть идеи?

10000