Trying to make this query work in a Lumen project

Asked Aug 26 '19 at 20:06

Active Aug 26 '19 at 21:06

Viewed 57 times

I'm working on a Lumen project and I'm stumped getting this to work. This is my first project in php using Lumen. I tested the query on Workbench and get the correct result but I have no idea how to get it to work.

with top as
(
    select tbl.*, row_number() over (partition by sensor order by timestamp desc) as parted from sometable as tbl
)
select * from top where parted <=3

working through the Laravel query builder documentation as well as using other similar questions to get it to work. This gets me close:

$result = Mydb::select(DB::raw("* row_number() over (partition by sensor order by timestamp desc) as parted"))
->get()->where('parted', '<=', 3);

But the json is poorly formatted with some added index I don't know where it comes from.

edited Aug 26 '19 at 21:06

asked Aug 26 '19 at 20:06

TunaMacDinner

Try to read this: https://stackoverflow.com/questions/1382573/how-do-you-use-the-with-clause-in-mysql – Abdes Aug 26 '19 at 20:15
What version of MySQL are you using? CTE requires 8.0 or higher. – Barmar Aug 26 '19 at 20:18
I am using a version 8.0+ I just need the most recent temperature readings from each of my Pi. – TunaMacDinner Aug 26 '19 at 20:30
Try reading this post... https://stackoverflow.com/questions/54194241/how-to-do-common-table-expressions-in-laravel-eloquent-for-mysql-8 – Diego Restrepo Aug 26 '19 at 20:52

Trying to make this query work in a Lumen project

0 Answers0