Getting rows from a database around the row selected

Asked Feb 07 '14 at 09:14

Active Feb 07 '14 at 09:14

Viewed 43 times

I get a row from my database, this row has a field 'sort'. I also wish to return the rows around this row, i.e. if sort of this row is 10, I want rows with sort 9 and 11.

Here's my eloquent:

return Products::wherePublished(1)->findOrFail($id, array('title', 'image', 'description','sort'));

Is it possible do do this with eloquent or would a regular query have to be made?

asked Feb 07 '14 at 09:14

panthro

22,779
66
183
324

Is there a unique key in that table? – Raghunandan Krishnamurthy Feb 07 '14 at 09:22
Yes, 'id' is the unique key – panthro Feb 07 '14 at 09:24
Select the row in mysql query and add 2 subqueries... 1 to get all rows where id < current_id and LIMIT 1. 2 to to get all rows where id > current_id and LIMIT 1. – Raghunandan Krishnamurthy Feb 07 '14 at 09:27
Could you show me an SQL example? Is there no way to do this in eloquent? – panthro Feb 07 '14 at 09:33
I wrote a query on my table.... worked............................. Select * from TABLE where ID = 552065 or ID = ( Select max(ID) from TABLE where ID < 552065 LIMIT 1) or ID = ( Select min(ID) from TABLE where ID > 552065 LIMIT 1) order by ID desc; – Raghunandan Krishnamurthy Feb 07 '14 at 09:41
but that selects ids? I need to do it on sort? – panthro Feb 07 '14 at 09:43
Replace ID = (Select ID from TABLE where Sort = 10 LIMIT 1); – Raghunandan Krishnamurthy Feb 07 '14 at 09:46
Works only when your sort is unique!!. I know query is complex now... but now you can try to optimize – Raghunandan Krishnamurthy Feb 07 '14 at 09:49
hmm sort is not unique. – panthro Feb 07 '14 at 09:51
we need to list all 'id' + 'sort' then apply the logic.. just think – Raghunandan Krishnamurthy Feb 07 '14 at 09:54
We can try one option... select * from Table a, (select id, sort from Table) b where a.id=b.id and a.sort=b.sort and OUR BIG QUERY – Raghunandan Krishnamurthy Feb 07 '14 at 09:56
Sorry, you are not making much sense. – panthro Feb 07 '14 at 09:57
To answer your question: no, Eloquent does not provide a way to select a row + nearby rows. Yes, you will have to do a "regular" query. Various solutions are discussed here: http://stackoverflow.com/questions/1813641/select-a-row-and-rows-around-it – Dave Feb 07 '14 at 15:45

Getting rows from a database around the row selected

0 Answers0