Laravel DB:raw with Union

I have the following querys which I need to unoin them but I'm getting the following error:

"message": "Method Illuminate\Support\Collection::getBindings does not exist."

The idea is that first query returns records grouped by month and the last query to union to first one, has the total values for each column grouped per month.

This is my expected output which is working correct the query in mysql:

SELECT USERS.ID AS ID, PROPERTIES.PROP_TYPE AS TIPO
,SUM(IF(MONTH(PROPERTIES.created_at) = 1, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as ENE
,SUM(IF(month(PROPERTIES.created_at) = 2, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS FEB
,SUM(IF(month(PROPERTIES.created_at) = 3, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as MAR
,SUM(IF(month(PROPERTIES.created_at) = 4, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS ABR
,SUM(IF(month(PROPERTIES.created_at) = 5, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS MAY
,SUM(IF(month(PROPERTIES.created_at) = 6, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as JUN
,SUM(IF(month(PROPERTIES.created_at) = 7, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as JUL
,SUM(IF(month(PROPERTIES.created_at) = 8, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS AGO
,SUM(IF(month(PROPERTIES.created_at) = 9, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as SEP
,SUM(IF(month(PROPERTIES.created_at) = 10, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as OCT
,SUM(IF(month(PROPERTIES.created_at) = 11, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as NOV
,SUM(IF(month(PROPERTIES.created_at) = 12, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as DIC
FROM PROPERTIES
LEFT JOIN USERS ON PROPERTIES.PROP_CAPPER_EMAIL = USERS.EMAIL_LOCAL_CRM
WHERE   USERS.ID = 755
GROUP BY PROPERTIES.PROP_TYPE, USERS.ID
UNION
SELECT USERS.ID AS ID, 'Total'
,SUM(IF(MONTH(PROPERTIES.created_at) = 1, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as ENE
,SUM(IF(month(PROPERTIES.created_at) = 2, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS FEB
,SUM(IF(month(PROPERTIES.created_at) = 3, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as MAR
,SUM(IF(month(PROPERTIES.created_at) = 4, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS ABR
,SUM(IF(month(PROPERTIES.created_at) = 5, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS MAY
,SUM(IF(month(PROPERTIES.created_at) = 6, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as JUN
,SUM(IF(month(PROPERTIES.created_at) = 7, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as JUL
,SUM(IF(month(PROPERTIES.created_at) = 8, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) AS AGO
,SUM(IF(month(PROPERTIES.created_at) = 9, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as SEP
,SUM(IF(month(PROPERTIES.created_at) = 10, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as OCT
,SUM(IF(month(PROPERTIES.created_at) = 11, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as NOV
,SUM(IF(month(PROPERTIES.created_at) = 12, PROPERTIES.PROP_EXCLUSIVE = 'Exclusiva',0)) as DIC
FROM PROPERTIES
LEFT JOIN USERS ON PROPERTIES.PROP_CAPPER_EMAIL = USERS.EMAIL_LOCAL_CRM
WHERE   USERS.ID = 755
GROUP BY USERS.ID

This is the code:

public function exclusive_details (Request $request) {
        $exclusive_details = DB::table('properties')
                            ->leftjoin('users', 'properties.prop_capper_email', '=', 'users.email_local_crm')
                            ->select(DB::raw("users.id
                            ,properties.prop_type
                            ,SUM(IF(MONTH(properties.created_at) = 1, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) as ENE
                            ,SUM(IF(month(properties.created_at) = 2, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) AS FEB
                            ,SUM(IF(month(properties.created_at) = 3, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) as MAR
                            ,SUM(IF(month(properties.created_at) = 4, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) AS ABR
                            ,SUM(IF(month(properties.created_at) = 5, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) AS MAY
                            ,SUM(IF(month(properties.created_at) = 6, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) as JUN
                            ,SUM(IF(month(properties.created_at) = 7, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) as JUL
                            ,SUM(IF(month(properties.created_at) = 8, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) AS AGO
                            ,SUM(IF(month(properties.created_at) = 9, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) as SEP
                            ,SUM(IF(month(properties.created_at) = 10, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) as OCT
                            ,SUM(IF(month(properties.created_at) = 11, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) as NOV
                            ,SUM(IF(month(properties.created_at) = 12, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) as DIC
                            "))
                            ->where('users.id', $request['query']['id'])
                            ->groupBy('properties.prop_type', 'users.id')
                            ->orderBy('properties.prop_type', 'asc');

        $exclusive_details_total = DB::table('properties')
                            ->leftjoin('users', 'properties.prop_capper_email', '=', 'users.email_local_crm')
                            ->select(DB::raw("users.id
                            ,'TOTAL'
                            ,SUM(IF(MONTH(properties.created_at) = 1, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) as ENE
                            ,SUM(IF(month(properties.created_at) = 2, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) AS FEB
                            ,SUM(IF(month(properties.created_at) = 3, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) as MAR
                            ,SUM(IF(month(properties.created_at) = 4, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) AS ABR
                            ,SUM(IF(month(properties.created_at) = 5, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) AS MAY
                            ,SUM(IF(month(properties.created_at) = 6, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) as JUN
                            ,SUM(IF(month(properties.created_at) = 7, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) as JUL
                            ,SUM(IF(month(properties.created_at) = 8, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) AS AGO
                            ,SUM(IF(month(properties.created_at) = 9, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) as SEP
                            ,SUM(IF(month(properties.created_at) = 10, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) as OCT
                            ,SUM(IF(month(properties.created_at) = 11, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) as NOV
                            ,SUM(IF(month(properties.created_at) = 12, properties.PROP_EXCLUSIVE = 'Exclusiva',0)) as DIC
                            "))
                            ->where('users.id', $request['query']['id'])
                            ->union($exclusive_details)
                            ->groupBy('users.id')
                            ->get();
        dump($exclusive_details);
        return response()->json($exclusive_details);
    }

How can I fix the union issue?

Regards

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/3cht00Q
via IFTTT