Rahul Vp Rahul Vp - 3 months ago 12
MySQL Question

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.

Answer

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