Jump to content
Sign in to follow this  
mstdmstdd

In laravel 5.5 retriving rows from postgresql function

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!        

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

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.