Início / dba / Perguntas / 339354
Accepted
Rob
Asked: 2024-05-09 11:35:31 +0800 CST

Depurar criação de função RPC no Supabase

  • 772

Sou muito novo em coisas de banco de dados.
Estou usando o Supabase para criar um aplicativo onde acompanho o número de curtidas em determinados itens ('cliques'). Quero filtrar os itens pela data em que as curtidas foram adicionadas ou por determinadas categorias dos itens. Até agora tenho uma função que posso chamar de javascript como:

const { data, error } = await supabase.rpc('rpc_test', { "json_params": {
  "categories": '{"fruits", "test"}',
  "start_date": "2024-04-16 00:22:35.837547+00",
} })

Que deve retornar todos os itens que possuem uma categoria correspondente à matriz que passei e o número de cliques que foram criados desde start_datee antes e end_datese fornecidos, ou zero se nenhum clique tiver sido criado nessa janela de tempo. E quase funciona, mas continuo encontrando erros que não sei como consertar.

As tabelas importantes em meu banco de dados são:

Unid:

eu ia nome
1 maçã
2 beterraba

Cliques:

item_id criado em
1 09/04/2024
2 09/04/2024

Categorias:

eu ia nome
1 vegetal
2 fruta

Categorias de itens:

item_id Categoria ID
1 2
2 1

Minha consulta de função atualmente se parece com isto:

create
or replace function public.rpc_test (json_params json default null) returns table (
  id bigint,
  created_at timestamp with time zone,
  name text,
  clicks bigint,
  categories_arr text[]
) as $$
BEGIN
  RETURN QUERY
    select
      items.id,
      items.created_at,
      items.name,
      click_counts.clicks,
      item_id_to_cat_array.categories_arr
    from
      items
      LEFT JOIN (
          SELECT item_categories.item_id AS itemid, array_agg(categories.name) AS categories_arr
          FROM   item_categories
          JOIN   categories ON categories.id = item_categories.category_id
          GROUP  BY item_categories.item_id
      ) item_id_to_cat_array ON items.id = item_id_to_cat_array.itemid
      LEFT JOIN (
          SELECT item_id as click_item_id, count(c.id) AS clicks
          FROM clicks as c
          WHERE (json_params->>'start_date' IS NULL OR c.created_at >= (json_params->>'start_date')::date)
          AND (json_params->>'end_date' IS NULL OR c.created_at <= (json_params->>'end_date')::date)
          GROUP BY c.item_id
      ) click_counts ON click_item_id = items.id
    where
        json_params->>'categories' IS NULL OR 
        (json_params->>'categories')::text[] && item_id_to_cat_array.categories_arr;
END;
$$ language plpgsql;

O único problema com isso é que categories_arrnunca há dados.

Em vários pontos, tive iterações disso que funcionaram para coletar as informações que desejo, mas sem a filtragem implementada. Tentei fazer coisas com GROUP BY e HAVING e não tenho certeza de para onde ir.

Como posso obter mais informações sobre o que está acontecendo na minha consulta?
Gostaria de ver o categories_arrque está acontecendo em cada etapa do processo, mas não sei como registrar essas informações.

postgresql

1 respostas

  1. Best Answer

    Não é simples monitorar as etapas de uma consulta SQL durante a execução. SQL é uma linguagem declarativa . Você parece pensar processualmente. Em um bloco de código PL/pgSQL real, você pode gerar qualquer coisa entre RAISE NOTICEas etapas. Existem até depuradores, como aquele embutido no pgAdmin - que quase nunca uso.

    Mas sua função é apenas um wrapper em torno de uma grande consulta SQL. Então isso não lhe daria nada.

    Assumindo um design padrão muitos para muitos como:

    Depois de mudar para simpler LANGUAGE sql, sua função poderia ficar assim:

    CREATE OR REPLACE FUNCTION public.rpc_test (_categories text[], _start_date date = NULL, _end_date date = NULL)
  RETURNS TABLE (id bigint
               , created_at timestamptz
               , name text
               , clicks bigint
               , categories_arr text[])
  LANGUAGE sql AS
$func$
SELECT i.item_id
     , items.created_at
     , items.name
     , click_counts.clicks
     , item_id_to_cat_array.categories_arr
FROM  (  -- get distinct item IDs that share any category with input array
   SELECT DISTINCT ic.item_id
   FROM   categories ca
   JOIN   item_categories ic ON ic.category_id = ca.id
   WHERE  ca.name = ANY (_categories)
   ) i
CROSS  JOIN LATERAL ( -- get full array of categories only for qualifying items
   SELECT ARRAY( -- array constructor is a bit cheaper
      SELECT ca.name
      FROM   item_categories ic
      JOIN   categories      ca ON ca.id = ic.category_id
      WHERE  ic.item_id = i.item_id
      ORDER  BY 1  -- optional, to get consistent sort order in output array
      ) AS categories_arr
   ) item_id_to_cat_array
CROSS  JOIN LATERAL (   -- get counts only for qualifying items
   SELECT count(*) AS clicks
   FROM   clicks  c
   WHERE  c.item_id = i.item_id
   AND   (_start_date IS NULL OR c.created_at >= _start_date)
   AND   (_end_date IS NULL   OR c.created_at <= _end_date)
   ) click_counts
JOIN   items ON items.id = i.item_id
WHERE  _categories IS NULL OR _categories && item_id_to_cat_array.categories_arr;
$func$;

    Você teve vários problemas de SQL. Reescrevi a lógica para fazê-la funcionar.

    1. Identifique itens que correspondam a qualquer categoria de entrada. item_categories.item_idé o suficiente por enquanto.
    2. Colete o conjunto completo de categorias apenas para aqueles selecionados, em vez de fazer isso para todos (o que é caro)
    3. O mesmo para cliques.
    4. Finalmente junte-se à tabela itemspara obter detalhes do item.

    Observe que isso espera entrada para _categories, pois a consulta não seria boa para retornar todos os itens. Se você precisar dessa opção, volte LANGUAGE plpgsqle bifurque o caso com uma consulta separada.

    • 0

relate perguntas