Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE VIEW view_recipe_list as
- SELECT
- 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,
- 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,
- json_agg((select id from (select dc.id) x) order by dc.components_name asc ) as components_id_json,
- case when rc.subvension = true THEN 'Tak' ELSE 'Nie' END as recipe_subvension,
- case when rc.active = true THEN 'Aktywna' ELSE 'Nieaktywna' END as recipe_status,
- ddg.name as dish_group_name,
- ddg.id as dish_group_id
- from
- recipe_config rc
- JOIN dict_dishes dd on rc.dishes_id = dd.id
- JOIN dict_dishes_group ddg on dd.dict_dishes_group_id = ddg.id
- JOIN dict_places dp on rc.place_id = dp.id
- JOIN recipe r on rc.recipe_id = r.recipe_id
- JOIN dict_components dc on r.component_id = dc.id
- JOIN dict_components_config dcc on dc.id = dcc.component_id
- WHERE
- dcc.place_id = rc.place_id
- GROUP BY
- 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
- ORDER BY
- recipe_id DESC;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement