Jump to content

In laravel 5.5 retriving rows from postgresql function


mstdmstdd
 Share

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

This thread is more than a year old.

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.

 Share

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