php - 如何在 Laravel 5.2 中编写 sql 连接查询

Question

为了制作报告，我需要编写一个连接查询。我现在在 sql 中编写了连接查询，我需要在 laravel 5.2 中编写相同的查询。下面给出了我的 sql 查询。

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

我也试着用 laravel 写它。查询在下面给出

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():

这样对吗？谁能告诉我并帮助我编写正确的查询。

Answer 1

laravel 5.2 中的连接语法是:

$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();

和你用的一样。如果您遇到任何问题，您可以使用以下命令打印原始 sql 查询:

DB::enableQueryLog();

// Your query

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