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

Make a select with max and min passing condition to each of the two – Laravel

When a post is accessed, I need, in addition to returning the information of this posts, to return the previous one if it exists and the next one.

I would like to know if there is a way to select MAX(id) and MIN(id) in a single query/select, passing a condition for each one of them. Example of what I’m trying to do in Laravel and I’ll write it in SQL to make it easier too

Laravel:

$query = Post::query();
$query = $query->from('posts')->select(DB::raw('MAX(id), MIN(id)'))->whereRaw("id < {$id} and id > {$id}")->first();

SQL:

select MAX(id), MIN(id) from `posts` where id < 5 and id > 5 limit 1

The id variable is the post id value. In this example, it has the value 5. The query I’m doing is to get the MAX and MIN referring to this id, but I also need to get the info of the post that the user accessed.

The DB has post id number 4 and number 6. That is, I need to get the information from posts number 4, 5 and 6 in this example.

The where condition will never be true, but I cannot use or. The first condition is for MAX and the second for MIN. If I use the or, the biggest id will come of the DB.

I need to get the min and max value compared to a value. That is, as I explained above. If the id is 5, I need to get the largest existing id() below that value and I need to get the smallest value above it. In my case, from the information I have in the DB, it would be id 4, 5 and 6

Is it possible in a single consultation or do I really have to do more than one?

Tags:

3

Answers


  1. 0

    The LEAD() and LAG() function in MySQL are used to get preceding and succeeding value of any row within its partition.

    Try this:

      SELECT ID,
         LAG (id) OVER (ORDER BY NULL) ONE_SHIFT_FORWARD,
         LEAD (id) OVER (ORDER BY NULL) ONE_SHIFT_BACKWARD
    FROM POSTS
ORDER BY ID ASC;

    enter image description here

    SELECT *
  FROM (  SELECT ID,
                 LAG (id) OVER (ORDER BY NULL) ONE_SHIFT_FORWARD,
                 LEAD (id) OVER (ORDER BY NULL) ONE_SHIFT_BACKWARD
            FROM POSTS
        ORDER BY ID ASC)
 WHERE id = 5;

    enter image description here

    Login or Signup to reply.
  2. 0

    Yes, you can do it with case-when

    select MAX(
           CASE
               WHEN id < 5 THEN id
               ELSE NULL
           END
       ), MIN(
           CASE
               WHEN id > 5 THEN id
               ELSE NULL
           END
       )
from `posts` 
where id <> 5

    EDIT

    Laravel equivalent, as shared by Gabriel Edu in the comment-section:

    $query = Post::query();
$query = $query->from('posts')->
         select(DB::raw("MAX(CASE WHEN id < {$id} THEN id ELSE null END), MIN(CASE WHEN id > {$id} THEN id ELSE null END)"))->first();
    Login or Signup to reply.
  3. 0

    You may use lead and lag to access the values before and after the current row.
    You may then use those to select the post with a given id and the values before and after in a single select.

    The following query

    select * 
    from (
    select 
        p.*,
        lead(id) over(order by id) _lead,
        lag(id) over(order by id) _lag
    from post p
) x
where 23 in (id, _lead, _lag);

    results in

    id text _lead _lag
    15 fifteen 23 10
    23 twentythree 24 15
    24 twentyfour 50 23

    With the following setup:

    Schema (MySQL v8.0)

    create table post (
    id integer,
    text varchar(50)
);
    
insert into post(id, text)
values
( 10, 'ten'),
( 15, 'fifteen'),
( 23, 'twentythree'),
( 24, 'twentyfour'),
( 50, 'fifty');

    View on DB Fiddle

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search