How to write normal sql queries inside controller in laravel

Question

I want to know how to write down normal sql queries inside laravel controller?

SELECT 

       vd.photo,
       CONCAT(vd.firstName," ",vd.lastName) as FullName,
       v.photo,
       v.vehicleModel,
       sr.tripType,
       sr.schoolTime,
       GROUP_CONCAT( rs.schoolName SEPARATOR ' ') as School_Name,
       GROUP_CONCAT( cp.checkPoint SEPARATOR ' ') as CheckPoints

FROM 

     vehicles v,
     school_van_routes sr,
     routes_schools rs,
     school_route_check_points cp,
     van_drivers vd,
     van_owners vo

WHERE

      v.numberPlate=sr.selectVehicle 
      AND sr.id=rs.routeId 
      AND sr.id= cp.routeId
      AND v.vanOwnerEmail=vo.email 
      AND vo.email =vd.email

GROUP BY v.id

JTinkers · Accepted Answer · 2019-06-11 10:37:15Z

3

Welcome to StackOverflow.

If you want to execute raw SQL rather than using a query builder, you can try:

$results = DB::select("SELECT * ..")

More here: https://laravel.com/docs/5.8/database#running-queries

answered Jun 11, 2019 at 10:37

JTinkers

1,80911 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vikash Pathak · Accepted Answer · 2019-06-11 11:33:08Z

0

Here is how you can convert your sql query.

$result = DB::table(DB::raw('vehicles v, school_van_routes sr, routes_schools rs, school_route_check_points cp, van_drivers vd, van_owners vo')) 
        ->select(DB::raw("vd.photo, CONCAT(vd.firstName,' ',vd.lastName) as FullName, v.photo, v.vehicleModel, sr.tripType, sr.schoolTime, GROUP_CONCAT( rs.schoolName SEPARATOR ' ') as School_Name, GROUP_CONCAT( cp.checkPoint SEPARATOR ' ') as CheckPoints"))
        ->whereRaw('v.numberPlate=sr.selectVehicle')
        ->whereRaw('sr.id=rs.routeId')
        ->whereRaw('sr.id= cp.routeId')
        ->whereRaw('v.vanOwnerEmail=vo.email')
        ->whereRaw('vo.email =vd.email')
        ->groupBy('v.id', 'v.photo', 'vd.photo', 'v.vehicleModel', 'sr.tripType', 'sr.schoolTime')
        ->get();

dd($result);

edited Jun 11, 2019 at 11:33

answered Jun 11, 2019 at 10:54

Vikash Pathak

3,6221 gold badge22 silver badges33 bronze badges

7 Comments

Gayal Seneviratne Over a year ago

syntax error, unexpected '') as School_Name, GROUP_CONCA' (T_CONSTANT_ENCAPSED_STRING), expecting ',' or ')'

Vikash Pathak Over a year ago

@GayalSeneviratne try now.

Gayal Seneviratne Over a year ago

"SQLSTATE[42000]: Syntax error or access violation: 1055 'schoolvan.vd.photo' isn't in GROUP BY (SQL: select vd.photo, CONCAT(vd.firstName,' ',vd.lastName) as FullName, v.photo, v.vehicleModel, sr.tripType, sr.schoolTime, GROUP_CONCAT( rs.schoolName SEPARATOR ' ') as School_Name, GROUP_CONCAT( cp.checkPoint SEPARATOR ' ') as CheckPoints from vehicles v, school_van_routes sr, routes_schools rs, school_route_check_points cp, van_drivers vd, van_owners vo where v.numberPlate=sr.selectVehicle and sr.id=rs.routeId and sr.id= cp.routeId and v.vanOwnerEmail=vo.email and vo.email =vd.email

Gayal Seneviratne Over a year ago

group by v.id)

Vikash Pathak Over a year ago

you need to include rest of the aggregated fields in groupBy clause.. like ->groupBy('v.id', 'v.photo', 'vd.photo', 'v.vehicleModel', 'sr.tripType', 'sr.schoolTime')

|

Collectives™ on Stack Overflow

How to write normal sql queries inside controller in laravel

2 Answers 2

Comments

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related