how to write sql join query in laravel 5.2

Question

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.

Your laravel code will perform an inner join but, the query you wrote uses a cross join.. Are you getting any unexpected results when you try it? (Reference to the documentation) — user5051310
– user5051310, Commented Aug 19, 2016 at 7:03
nop.. its working perfect.. But i need to know , is that good enough for development?. — Rahul Vp
– Rahul Vp, Commented Aug 19, 2016 at 11:39

Mayank Pandeyz · Accepted Answer · 2016-08-19 06:41:49Z

3

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

answered Aug 19, 2016 at 6:41

Mayank Pandeyz

26.3k4 gold badges43 silver badges61 bronze badges

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

Collectives™ on Stack Overflow

how to write sql join query in laravel 5.2

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related