mstdmstdd Posted September 12, 2017 Share Posted September 12, 2017 Hello,In laravel 5.5 retriving rows from postgresql function using DB::select($sql) where sql : select pd_report_orders_sum_by_categories( p_category_list := ARRAY[102,17]::integer[], p_last_operation_date_from := '2012-09-20', p_last_operation_date_till := '2017-09-29', p_status := 'O', p_sort_categories := 'sum', p_limit := null ) for function defined as: CREATE OR REPLACE FUNCTION public.pd_report_orders_sum_by_categories(p_category_list integer[], p_last_operation_date_from timestamp without time zone, p_last_operation_date_till timestamp without time zone, p_status type_order_status, p_sort_categories character varying DEFAULT 'by_sum_asc'::character varying, p_limit integer DEFAULT NULL::integer) RETURNS TABLE(category_name character varying, category_slug character varying, category_id smallint, sold_sum numeric, qty_sum bigint) LANGUAGE sql AS $function$ SELECT c.name as category_name, c.slug as category_slug, pc.category_id, sum( oi.qty*oi.price ) AS sold_sum, sum( oi.qty ) AS qty_sum from pd_order_item as oi join pd_product_category as pc on pc.product_id = oi.product_id join pd_order as o on o.id = oi.order_id join pd_category as c on c.id = pc.category_id WHERE ( CASE when p_category_list IS NOT NULL THEN pc.category_id = ANY (p_category_list) else true END ) AND ( o.last_operation_date BETWEEN coalesce(p_last_operation_date_from,pd_f_min_timestamp()) AND coalesce(p_last_operation_date_till,pd_f_max_timestamp()) ) AND ( CASE when p_status IS NOT NULL THEN o.status = p_status else true END ) group by pc.category_id, category_name, category_slug ORDER BY CASE WHEN p_sort_categories = 'by_sum_asc' THEN sum( oi.qty*oi.price ) end ASC, CASE WHEN p_sort_categories = 'by_sum_desc' THEN sum( oi.qty*oi.price ) end DESC, CASE WHEN p_sort_categories = 'by_sum_category_name' THEN c.name end ASC LIMIT p_limit ; $function$ I got resulting rows like : Array ( [0] => stdClass Object ( [pd_report_orders_sum_by_categories] => (Music,music,4,2.43,1) ) [1] => stdClass Object ( [pd_report_orders_sum_by_categories] => ("Computer Accessories",computer-accessories,1,25.75,1) ) I can all data I need but I would like to know if there is a way to get associative data in any subarray ?Sure I can write some kind of parser, but if Laravel some tools for this?Thanks! Quote Link to comment https://forums.phpfreaks.com/topic/304958-in-laravel-55-retriving-rows-from-postgresql-function/ Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.