Advertisement
Guest User

Untitled

a guest
Oct 12th, 2017
85
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.32 KB | None | 0 0
  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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement