Rahul Vp Rahul Vp - 1 year ago 166
MySQL Question

how to 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 Source

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')

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


// Your query

$queries = DB::getQueryLog();
print_r($queries);  // it will print raw sql query in prepared statement style
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download