Trying to convert a SQL into Laravel query using Query builder?

Level 70

@ankur_g This is what I found after conversion.

return TypeformContact::selectRaw("'Meetings Offered' AS Source, 'Meetings Chosen' AS Destination, COUNT(1) AS value, DATE(created_at) AS DATE")
    ->where('client_signed', 1)
    ->groupBy(DB::raw("DATE(created_at)"))
    ->union(LawyerOpportunityMeeting::selectRaw("'Meetings Chosen' AS Source, '4A OR 4B' AS Destination, COUNT(1) AS Value, DATE(created_at) AS DATE")
        ->where('contact_type', 'typeform')
        ->groupBy(DB::raw("DATE(created_at)")))
    ->union(DripfeedTarget::selectRaw("'4A OR 4B' AS Source, '4B Get L times' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
        ->whereIn('feed_id', [360, 361])
        ->groupBy(DB::raw("DATE(date_added)")))
    ->union(DripfeedTarget::selectRaw("'4B Get L times' AS Source, '4C - Meeting Set' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
        ->whereIn('feed_id', [361])
        ->groupBy(DB::raw("DATE(date_added)")))
    ->union(DripfeedTarget::selectRaw("'4C - Meeting Set' AS Source, '4D Follow up' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
        ->whereIn('feed_id', [362])
        ->groupBy(DB::raw("DATE(date_added)")))
    ->union(DripfeedTarget::selectRaw("'4D Follow up' AS Source, '' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
        ->whereIn('feed_id', [363])
        ->groupBy(DB::raw("DATE(date_added)")))
    ->whereBetween('DATE', ['2023-1-1', '2023-1-30'])
    ->get();

You may need some tweaks.

1 like

Level 1

@tisuchi okay thanks

Level 1

@tisuchi Sir I have also tried your approach in the following way. Is it correct:

public function meetingSankey() {
        $returnArray = array();

        $result = TypeformContact::selectRaw("'Meetings Offered' AS Source, 'Meetings Chosen' AS Destination, COUNT(1) AS value, DATE(created_at) AS DATE")
        ->where('client_signed', 1)
        ->groupBy('created_at')
        ->whereBetween('DATE', [Carbon::create(2023,1,1,0)->toDateTimeString(), Carbon::now()->toDateTimeString()])

        ->union(LawyerOpportunityMeeting::selectRaw("'Meetings Chosen' AS Source, '4A OR 4B' AS Destination, COUNT(1) AS Value, DATE(created_at) AS DATE")
        ->where('contact_type', 'typeform')
        ->groupBy('created_at')
        ->whereBetween('DATE', [Carbon::create(2023,1,1,0)->toDateTimeString(), Carbon::now()->toDateTimeString()]))

        ->union(DripfeedTarget::selectRaw("'4A OR 4B' AS Source, '4B Get L times' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
        ->whereIn('feed_id', [360, 361])
        ->groupBy('date_added')
        ->whereBetween('DATE', [Carbon::create(2023,1,1,0)->toDateTimeString(), Carbon::now()->toDateTimeString()]))

        ->union(DripfeedTarget::selectRaw("'4B Get L times' AS Source, '4C - Meeting Set' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
        ->whereIn('feed_id', [361])
        ->groupBy('date_added')
        ->whereBetween('DATE', [Carbon::create(2023,1,1,0)->toDateTimeString(), Carbon::now()->toDateTimeString()]))

        ->union(DripfeedTarget::selectRaw("'4C - Meeting Set' AS Source, '4D Follow up' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
        ->whereIn('feed_id', [362])
        ->groupBy('date_added')
        ->whereBetween('DATE', [Carbon::create(2023,1,1,0)->toDateTimeString(), Carbon::now()->toDateTimeString()]))

        ->union(DripfeedTarget::selectRaw("'4D Follow up' AS Source, '' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
        ->whereIn('feed_id', [363])
        ->groupBy('date_added')
        ->whereBetween('DATE', [Carbon::create(2023,1,1,0)->toDateTimeString(), Carbon::now()->toDateTimeString()]));


        $returnArray['data'] = $result;
        $returnArray['status'] = "success";

        return response()->json($returnArray, 200);        
    }

SIr I have applied whereBetween() for every union query and also I have changed the groupBy() condition with the column name. Is it correct? Kindly reply if possible.

tisuchi

Level 70

@ankur_g Apparently it looks good. Run and see if is there any error or not.

Level 1

@tisuchi okay sir. Sir I have added the whereBetween() method inside every union and changed the groupBy() to the particular column name.

Level 1

@tisuchi Sir the last query which I had written is throwing an error :

public function meetingSankey() {
        $returnArray = array();
        $result = typeformContact::selectRaw("'Meetings Offered' AS Source, 'Meetings Chosen' AS Destination, COUNT(1) AS value, DATE(created_at) AS DATE")
        ->where('client_signed', 1)
        ->groupByRaw('DATE(created_at)')
        ->whereBetween('DATE', [Carbon::create(2023,1,1,0)->toDateTimeString(), Carbon::now()->toDateTimeString()])

        ->union(LawyerOpportunityMeeting::selectRaw("'Meetings Chosen' AS Source, '4A OR 4B' AS Destination, COUNT(1) AS Value, DATE(created_at) AS DATE")
        ->where('contact_type', 'typeform')
        ->groupByRaw('DATE(created_at)')
        ->whereBetween('DATE', [Carbon::create(2023,1,1,0)->toDateTimeString(), Carbon::now()->toDateTimeString()]))

        ->union(dripfeedTarget::selectRaw("'4A OR 4B' AS Source, '4B Get L times' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
        ->whereIn('feed_id', [360, 361])
        ->groupByRaw('DATE(date_added)')
        ->whereBetween('DATE', [Carbon::create(2023,1,1,0)->toDateTimeString(), Carbon::now()->toDateTimeString()]))

        ->union(dripfeedTarget::selectRaw("'4B Get L times' AS Source, '4C - Meeting Set' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
        ->whereIn('feed_id', [361])
        ->groupByRaw('DATE(date_added)')
        ->whereBetween('DATE', [Carbon::create(2023,1,1,0)->toDateTimeString(), Carbon::now()->toDateTimeString()]))

        ->union(dripfeedTarget::selectRaw("'4C - Meeting Set' AS Source, '4D Follow up' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
        ->whereIn('feed_id', [362])
        ->groupByRaw('DATE(date_added)')
        ->whereBetween('DATE', [Carbon::create(2023,1,1,0)->toDateTimeString(), Carbon::now()->toDateTimeString()]))

        ->union(dripfeedTarget::selectRaw("'4D Follow up' AS Source, '' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
        ->whereIn('feed_id', [363])
        ->groupByRaw('DATE(date_added)')
        ->whereBetween('DATE', [Carbon::create(2023,1,1,0)->toDateTimeString(), Carbon::now()->toDateTimeString()]))->get();

        $returnArray['data'] = $result;
        $returnArray['status'] = "success";

        return response()->json($returnArray, 200);
    }

But now it shows an error as follows:

Illuminate\Database\QueryException: SQLSTATE[42703]: Undefined column: 7 ERROR:  column "DATE" does not exist
LINE 1: ...typeform_contacts" where "client_signed" =  and "DATE" bet...

It is considering DATE as a column over here but I have used DATE as an alias. Kindly suggest if possible where am I doing it incorrectly.

Best Answer

Level 102

A complex query like this, I wouldnt convert to eloquent or the query builder. It will just make it MUCH harder to read!. Instead just use DB::select() and replace all variables with ? and move them to an array in the second parameter

DB::select("SELECT * FROM (SELECT 'Meetings Offered' AS Source,
             'Meetings Chosen' AS Destination,
             COUNT(1) AS value,
             DATE (created_at) AS DATE
      FROM typeform_contacts
      WHERE client_signed = 1
      GROUP BY DATE (created_at)
      UNION
      SELECT 'Meetings Chosen' AS Source,
             '4A OR 4B' AS Destination,
             COUNT(1) AS Value,
             DATE (created_at) AS DATE
      FROM lawyer_opportunity_meetings
      WHERE contact_type = 'typeform'
      GROUP BY DATE (created_at)
      UNION
      SELECT '4A OR 4B' AS Source,
             '4B Get L times' AS Destination,
             COUNT(1) AS Value,
             DATE (date_added) AS DATE
      FROM dripfeed_targets
      WHERE feed_id in(?, ?)
      GROUP BY DATE (date_added)
      UNION
      SELECT '4B Get L times' AS Source,
             '4C - Meeting Set' AS Destination,
             COUNT(1) AS Value,
             DATE (date_added) AS DATE
      FROM dripfeed_targets
      WHERE feed_id in(?)
      GROUP BY DATE (date_added)
) AS TEMP 
WHERE DATE BETWEEN ? AND ?", [360, 361, 361, '2023-1-1', '2023-1-30']);

1 like

tisuchi

Level 70

@Sinnbeck 👌

Level 1

@Sinnbeck okay thanks a lot. One question what does DB refers over here? Do I need to include any namespace for accessing the DB class?

Level 102

@ankur_g You can either import it, or just do \DB

If you want to import it

use Illuminate\Support\Facades\DB;

Level 1

@Sinnbeck okay thanks again. Another question if I want the date between 1/1/2023 and today(30/1/2023) and want to make it dynamic ; so as to not specify the date manually which function can I use?

Level 102

@ankur_g You just replace those 5 items in the array with variables (that array which is in the second parameter)

[$id1, $id2, $id2, $startDate, $endDate]

Just make sure that it has the format 2023-01-01, and not 1/1/2023. You can use Carbon to convert it if needed :)

Level 1

@Sinnbeck okay

Level 102

@ankur_g Just in case you are unsure, you can do something like \Carbon\Carbon::parse($date)->toDateString()

Level 1

@Sinnbeck Now I have tried in the following way. Is this correct?

public function meetingSankey() {
        $returnArray = array();
        $result = DB::select("SELECT * FROM (SELECT 'Meetings Offered' AS Source,
                                'Meetings Chosen' AS Destination,
                                COUNT(1) AS value,
                                DATE (created_at) AS DATE
                                FROM typeform_contacts
                                WHERE client_signed = 1
                                GROUP BY DATE (created_at)
                                UNION
                                SELECT 'Meetings Chosen' AS Source,
                                        '4A OR 4B' AS Destination,
                                        COUNT(1) AS Value,
                                        DATE (created_at) AS DATE
                                FROM lawyer_opportunity_meetings
                                WHERE contact_type = 'typeform'
                                GROUP BY DATE (created_at)
                                UNION
                                SELECT '4A OR 4B' AS Source,
                                        '4B Get L times' AS Destination,
                                        COUNT(1) AS Value,
                                        DATE (date_added) AS DATE
                                FROM dripfeed_targets
                                WHERE feed_id in(?, ?)
                                GROUP BY DATE (date_added)
                                UNION
                                SELECT '4B Get L times' AS Source,
                                        '4C - Meeting Set' AS Destination,
                                        COUNT(1) AS Value,
                                        DATE (date_added) AS DATE
                                FROM dripfeed_targets
                                WHERE feed_id in(?)
                                GROUP BY DATE (date_added)
                                UNION
                                SELECT '4C - Meeting Set' AS Source,
                                        '4D Follow up' AS Destination,
                                        COUNT(1) AS Value,
                                        DATE (date_added) AS DATE
                                FROM dripfeed_targets
                                WHERE feed_id in(?)
                                GROUP BY DATE (date_added)
                                UNION
                                SELECT '4D Follow up' AS Source,
                                        '' AS Destination,
                                        COUNT(1) AS Value,
                                        DATE (date_added) AS DATE
                                FROM dripfeed_targets
                                WHERE feed_id in(?)
                                GROUP BY DATE (date_added)
                            ) AS TEMP 
                        WHERE DATE BETWEEN ? AND ?", [360, 361, 361, 362, 363, Carbon::create(2023,1,1,0)->toDateTimeString(), Carbon::now()->toDateTimeString()]);

        $returnArray['data'] = $result;
        $returnArray['status'] = "success";

        return response()->json($returnArray, 200);
            
    }

I have not changed the feed_id because they will remain static

Level 102

@ankur_g Yeah that should work :)

Level 1

@Sinnbeck okay thanks!

Level 1

@Sinnbeck Sir in connection with my previous query the last query is not working correctly so I tried to build it in a sub-query by removing the model name and using the DB instance as follows:

public function meetingSankey() {
        $returnArray = array();
        $result = DB::query()->fromSub(
            DB::table('typeform_contacts')
            ->selectRaw("'Meetings Offered' AS Source, 'Meetings Chosen' AS Destination, COUNT(1) AS value, DATE(created_at) AS DATE")
            ->where('client_signed', 1)
            ->groupByRaw('DATE(created_at)')
    
            ->union(
                    DB::table('lawyer_opportunity_meetings')
                    ->selectRaw("'Meetings Chosen' AS Source, '4A OR 4B' AS Destination, COUNT(1) AS Value, DATE(created_at) AS DATE")
                    ->where('contact_type', 'typeform')
                    ->groupByRaw('DATE(created_at)')
                )
    
            ->union(
                    DB::table('dripfeed_targets')
                    ->selectRaw("'4A OR 4B' AS Source, '4B Get L times' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
                    ->whereIn('feed_id', [360, 361])
                    ->groupByRaw('DATE(date_added)')
                )
    
            ->union(
                    DB::table('dripfeed_targets')
                    ->selectRaw("'4B Get L times' AS Source, '4C - Meeting Set' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
                    ->whereIn('feed_id', [361])
                    ->groupByRaw('DATE(date_added)')
                )
    
            ->union(
                    DB::table('dripfeed_targets')
                    ->selectRaw("'4C - Meeting Set' AS Source, '4D Follow up' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
                    ->whereIn('feed_id', [362])
                    ->groupByRaw('DATE(date_added)')
                )
    
            ->union(
                    DB::table('dripfeed_targets')
                    ->selectRaw("'4D Follow up' AS Source, '' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE")
                    ->whereIn('feed_id', [363])
                    ->groupByRaw('DATE(date_added)')
                ), 'temp')
            ->whereBetween('DATE', [Carbon::create(2023,1,1,0)->toDateTimeString(), Carbon::now()->toDateTimeString()])->get();

        $returnArray['data'] = $result;
        $returnArray['status'] = "success";

        return response()->json($returnArray, 200);
    }

Now when I am running this code it shows the following error although the column client_signed exist in the table typeform_contacts:

Illuminate\Database\QueryException: SQLSTATE[42703]: Undefined column: 7 ERROR:  column "client_signed" does not exist
LINE 1: ...reated_at) AS DATE from "typeform_contacts" where "client_si...
                                                             ^ (SQL: select * from ((select 'Meetings Offered' AS Source, 'Meetings Chosen' AS Destination, COUNT(1) AS value, DATE(created_at) AS DATE from "typeform_contacts" where "client_signed" = 1 group by DATE(created_at)) union (select 'Meetings Chosen' AS Source, '4A OR 4B' AS Destination, COUNT(1) AS Value, DATE(created_at) AS DATE from "lawyer_opportunity_meetings" where "contact_type" = typeform group by DATE(created_at)) union (select '4A OR 4B' AS Source, '4B Get L times' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE from "dripfeed_targets" where "feed_id" in (360, 361) group by DATE(date_added)) union (select '4B Get L times' AS Source, '4C - Meeting Set' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE from "dripfeed_targets" where "feed_id" in (361) group by DATE(date_added)) union (select '4C - Meeting Set' AS Source, '4D Follow up' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE from "dripfeed_targets" where "feed_id" in (362) group by DATE(date_added)) union (select '4D Follow up' AS Source, '' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE from "dripfeed_targets" where "feed_id" in (363) group by DATE(date_added))) as "temp" where "DATE" between 2023-01-01 00:00:00 and 2023-01-31 18:26:32) in file /opt/lampp/htdocs/multitenant/vendor/laravel/framework/src/Illuminate/Database/Connection.php on line 671

When I am separately executing the query which is getting built:

select * from (select 'Meetings Offered' AS Source, 'Meetings Chosen' AS Destination, COUNT(1) AS value, DATE(created_at) AS DATE from "typeform_contacts" where "client_signed" = 1 group by DATE(created_at) 
			   union (select 'Meetings Chosen' AS Source, '4A OR 4B' AS Destination, COUNT(1) AS Value, DATE(created_at) AS DATE from "lawyer_opportunity_meetings" where "contact_type" = typeform group by DATE(created_at)) 
			   union (select '4A OR 4B' AS Source, '4B Get L times' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE from "dripfeed_targets" where "feed_id" in (360, 361) group by DATE(date_added)) 
			   union (select '4B Get L times' AS Source, '4C - Meeting Set' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE from "dripfeed_targets" where "feed_id" in (361) group by DATE(date_added)) 
			   union (select '4C - Meeting Set' AS Source, '4D Follow up' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE from "dripfeed_targets" where "feed_id" in (362) group by DATE(date_added)) 
			   union (select '4D Follow up' AS Source, '' AS Destination, COUNT(1) AS Value, DATE(date_added) AS DATE from "dripfeed_targets" where "feed_id" in (363) group by DATE(date_added)) as "temp" )
			   where "DATE" between '2023-01-01 00:00:00' and '2023-01-31 18:12:57';

It throws an error as shown below:

ERROR:  syntax error at or near "as"
LINE 6: ...ere "feed_id" in (363) group by DATE(date_added)) as "temp" ...

Kindly suggest if possible where am I going wrong.

jlrdw

Level 75

@ankur_g Example here https://laracasts.com/discuss/channels/laravel/sql-native-to-query-builder

Also you can use the pdo instance getPdo(), a the link I gave covers it as well.

Level 1

@jlrdw okay; it is still not clear to me. Could you kindly state the use of Pdo() using the above query which I have written. If you could the first select statement I will try to complete the others

jlrdw

Level 75

@ankur_g an example link is there. How are you writing the query now, And where, is this in a mysql front end?

The getPdo() instance would be using 100% straight PDO and PHP.

A good tutorial on PDO is: https://phpdelusions.net/pdo

If you are new to PDO, I suggest some practice with the tutorial.

Edit:

I don't understand the hard coded numbers in your query, you need parameters.

And the above tutorial look here https://phpdelusions.net/pdo#prepared

Also another option is use a stored procedure.