Jump to content

In laravel 5.5 retriving rows from postgresql function


mstdmstdd

Recommended Posts

  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!        

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.