Question 15344 in Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel CTE (WITH) query builder for union all

I’m using Laravel package staudenmeir/laravel-cte but am not able to articulate the query I want

MY SQL query :

WITH T AS (
  SELECT Id, DeviceType, DeviceId, ENERGY_Total, `Time`
  FROM devices_sensor_data
  WHERE `Time` BETWEEN '2023-03-28 00:00:00' AND '2023-03-27 20:40:00'
   AND `DeviceId` IN ('esp8266/meter3Phase2' , 'esp8266/meter3Phase3')
) 
(SELECT * FROM T ORDER BY Time LIMIT 1)
UNION ALL
(SELECT * FROM T ORDER BY Time DESC LIMIT 1);

My articulated laravel query

$realQuery = DB::table('devices_sensor_data')
            ->select(DB::raw('DeviceId, '.$chartType.', Time'))
            ->whereIn('DeviceId', array_keys($devicesArr))
            ->whereBetween('Time', [$from, $to]);

$a = DB::table('T')
    ->select(DB::raw('*'))
    ->orderBy('Time')
    ->limit(1);

$sensor_data = DB::query()
    ->withExpression('T', $realQuery)
    ->select(DB::raw('T.*'))
    ->orderBy('Time', 'DESC')
    ->limit(1)
    ->unionAll($a)
    ->get();

This query is giving me syntax error

I’m using staudenmeir/laravel-cte : 1.5.5 version

Edits:-

I’m getting MySQL syntax error as

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '`T` as (select DeviceId, ENERGY_Total, Time from `devices_sensor_data` where `De' at line 1 (SQL: (with `T` as (select DeviceId, ENERGY_Total, Time from `devices_sensor_data` where `DeviceId` in (esp8266/meter3Phase2, esp8266/meter3Phase3) and `Time` between 2023-03-27 09:30:00 and 2023-03-28 09:29:00) select * from `T` order by `Time` asc limit 1) union all (select * from `T` order by `Time` desc limit 1))
Tags:

2

Answers


  1. 0

    The issue with your query is that you’re trying to reference a CTE(T) in your subquery, but you haven’t actually defined the CTE in the query.To fix this, you need to define the T CTE in your Laravel query using the with() method before you reference it in your subquery.

    Like this

    $realQuery = DB::table('devices_sensor_data')
    ->select(DB::raw('Id, DeviceType, DeviceId, ENERGY_Total, Time'))
    ->whereIn('DeviceId', ['esp8266/meter3Phase2', 'esp8266/meter3Phase3'])
    ->whereBetween('Time', ['2023-03-28 00:00:00', '2023-03-27 20:40:00']);

$sensor_data = DB::query()
    ->withExpression('T', $realQuery)
    ->select(DB::raw('*'))
    ->from('T')
    ->orderBy('Time')
    ->limit(1)
    ->unionAll(
        DB::query()
            ->select(DB::raw('*'))
            ->from('T')
            ->orderBy('Time', 'DESC')
            ->limit(1)
    )
    ->get();
    Login or Signup to reply.
  2. 0

    Use (not tested)

    $startTime = '2023-03-28 00:00:00';
$endTime = '2023-03-27 20:40:00';
$deviceIds = ['esp8266/meter3Phase2', 'esp8266/meter3Phase3'];

$query = CteQueryBuilder::withExpression('T', function ($query) use ($startTime, $endTime, $deviceIds) {
    $query->select('Id', 'DeviceType', 'DeviceId', 'ENERGY_Total', 'Time')
        ->from('devices_sensor_data')
        ->whereIn('DeviceId', $deviceIds)
        ->whereBetween('Time', [$endTime, $startTime]);
});

$firstResult = $query->from('T')->orderBy('Time')->limit(1);

$secondResult = $query->from('T')->orderByDesc('Time')->limit(1);

$results = $firstResult->unionAll($secondResult)->get();

    add this at top of the file

    use StaudenmeirLaravelCteQueryBuilder as CteQueryBuilder;

    Or in a generic way (not tested)

    $query = DB::table('devices_sensor_data')
    ->select(DB::raw('Id, DeviceType, DeviceId, ENERGY_Total, Time'))
    ->whereIn('DeviceId', array_keys($devicesArr))
    ->whereBetween('Time', [$from, $to]);

$firstResult = DB::table('T')
    ->select(DB::raw('*'))
    ->orderBy('Time')
    ->limit(1);

$secondResult = DB::table('T')
    ->select(DB::raw('*'))
    ->orderBy('Time', 'desc')
    ->limit(1);

$results = DB::query()
    ->withExpression('T', $query)
    ->fromSub($firstResult, 'first')
    ->unionAll($secondResult)
    ->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search