CODEWITHSUNDEEP
phpmysqllaravel-5
Rahul Vp
Rahul Vp

Reputation: 127

how to write sql join query in laravel 5.2

To make the report i need to write a join query. I wrote the join query in sql now, i need to write the same query in laravel 5.2. My sql query is given below.

SELECT a.accountID, a.deviceID, b.description, a.timestamp, a.latitude, a.longitude, a.speedKPH as speed, a.heading, a.altitude, a.address, a.distanceKM as distance, a.odometerKM as odometer, a.IbatVolts, a.EbatVolts, a.ITempr, a.fuelLevel, a.inputState, a.IgnRuntime, a.GPSFixType, a.GPSPDOP, a.AlertType, a.speedLimitKPH, a.isTollRoad
FROM eventdata as a, device as b 
WHERE a.deviceID = '$deviceID'
  AND a.accountID = '$accountID'
  AND a.timestamp >= $dt1
  AND a.timestamp <= $dt2
  AND a.deviceID=b.deviceID
ORDER BY timestamp DESC

and i tried to write it in laravel also. the query is given below

DB::table('device as b')
   ->join('eventdata as a', 'a.deviceID', '=', 'b.deviceID')
   ->where('a.deviceID', '=', '$deviceID')
   ->where('a.accountID', '=', '$accountID')
   ->where('a.timestamp', '>=', '$dt1')
   ->where('a.timestamp', '<=', '$dt2')
   ->select('a.accountID', 'a.deviceID', 'b.description', 'a.timestamp',
            'a.latitude', 'a.longitude', 'a.speed', 'a.heading', 'a.altitude', 'a.address', 'a.distanceKM as distance', 'a.odometerKM as odometer', 'a.IbatVolts', 'a.EbatVolts', 'a.ITempr', 'a.fuelLevel', 'a.inputState', 'a.IgnRuntime', 'GPSFixType', 'a.GPSPDOP', 'a.AlterType', 'a.speedLimitKPH', 'a.isTollRoad')->get():

Is this right? Can anyone tell me and help me to write the correct query.

Upvotes: 1

Views: 2414

Answers (1)

Mayank Pandeyz
Mayank Pandeyz

Reputation: 26258

The join syntax in laravel 5.2 is:

$users = DB::table('users')
            ->join('contacts', 'users.id', '=', 'contacts.user_id')
            ->join('orders', 'users.id', '=', 'orders.user_id')
            ->select('users.*', 'contacts.phone', 'orders.price')
            ->get();

and you are using the same. In case you are facing any issue than you can print the raw sql query by using:

DB::enableQueryLog();

// Your query

$queries = DB::getQueryLog();
print_r($queries);  // it will print raw sql query in prepared statement style

Upvotes: 3

Related Questions