daily pastebin goal
17%
SHARE
TWEET

Untitled

a guest Oct 12th, 2017 60 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE VIEW view_recipe_list as
  2.   SELECT
  3.     rc.id as id, dd.id as dish_id, dd.name as dish_name, dp.id as place_id, dp.name as place_name, rc.recipe_id, start_date, end_date, rc.subvension, rc.active as recipe_active, rc.user_id,
  4.     json_agg((select x from (select dc.id as comp_id, dc.components_name as comp_name, r.weight, dcc.price_pk, dcc.price_pu, dc.subvension) x) order by dc.components_name asc) as components_json,
  5.     json_agg((select id from (select dc.id) x) order by dc.components_name asc ) as components_id_json,
  6.     case when rc.subvension = true THEN 'Tak' ELSE 'Nie' END as recipe_subvension,
  7.     case when rc.active = true THEN 'Aktywna' ELSE 'Nieaktywna' END as recipe_status,
  8.     ddg.name as dish_group_name,
  9.     ddg.id as dish_group_id
  10.   from
  11.     recipe_config rc
  12.     JOIN dict_dishes dd on rc.dishes_id = dd.id
  13.     JOIN dict_dishes_group ddg on dd.dict_dishes_group_id = ddg.id
  14.     JOIN dict_places dp on rc.place_id = dp.id
  15.     JOIN recipe r on rc.recipe_id = r.recipe_id
  16.     JOIN dict_components dc on r.component_id = dc.id
  17.     JOIN dict_components_config dcc on dc.id = dcc.component_id
  18.   WHERE
  19.     dcc.place_id = rc.place_id
  20.   GROUP BY
  21.     rc.id, dd.id, dp.id, rc.active, dd.name, dp.name, rc.recipe_id, start_date, end_date, rc.subvension, ddg.name, ddg.id
  22.   ORDER BY
  23.     recipe_id DESC;
RAW Paste Data
Top