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

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • 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.