CODEWITHSUNDEEP
sqlpostgresql
qubyte
qubyte

Reputation: 17748

Select a row, and the rows either side of it

I'm putting together a blog with posts which implement JSON API. To that end, when I retrieve a post from the database, I want to include links to the next and previous posts. For the database this means that when I retrieve a row from a posts table, I'd like to also retrieve the row before and the row after it.

I'm a bit of a noob with SQL (specifically postgres). At the moment I have the following:

SELECT * 
  FROM posts 
  WHERE id >= (
    SELECT id 
    FROM posts 
    WHERE id < (
      SELECT id
      FROM posts
      WHERE slug = 'the-slug'
    )
    AND published = TRUE
    ORDER BY id DESC
    LIMIT 1
  )
  AND published = TRUE
  ORDER BY id ASC
  LIMIT 3;

(posts have serial id primary key, a published boolean, and a slug varchar)

All I have access to is the slug of the centre post. This works, but obviously breaks if the-slug represents the first published row. It also seems quite naive. Is there a better way to go about this?

EDIT:

I'm trying to avoid this question being too specific to my problem so that any answers might be of more use to many. However, the above is quite loose. Consider the table to be created with the following:

CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    slug VARCHAR(255) UNIQUE NOT NULL,
    published BOOLEAN NOT NULL DEFAULT FALSE
);

There are more columns, but they should be of no relevance to this question. I'm trying to find, given a slug, the row that matches that slug and the rows before and after it (where they exist). I'm trying to achieve:

Upvotes: 2

Views: 488

Answers (3)

qubyte
qubyte

Reputation: 17748

A solution using LAG and LEAD, where 'the-slug' if the slug to centre around:

SELECT *
FROM (
  SELECT 
    id, 
    slug,
    LAG(id) OVER (ORDER BY id) AS prev_id,
    LEAD(id) OVER (ORDER BY id) AS next_id,
    LAG(slug) OVER (ORDER BY id) AS prev_slug,
    LEAD(slug) OVER (ORDER BY id) AS next_slug
  FROM posts
  WHERE published = TRUE
) t
WHERE slug = 'the-slug'
;

It returns a single row (if the slug matches a row at all), with some additional columns with data borrowed from the published row before and published row after (where they exist); prev_id, prev_slug, next_id, and next_slug.

This answer is very slightly adapted from a comment on the question given by a_horse_with_no_name which points to this sqlfiddle (if you post the answer I'll mark it as the correct one).

Upvotes: 0

klin
klin

Reputation: 121634

Use row_number() function:

  • number all published rows (add rn to posts in the first query),
  • find row number of the searched slug (row_found in the second query),
  • select rows with rn >= row_found- 1 and rn <= row_found+ 1.
with numbered_posts as (        
    select *, row_number() over (order by id) rn
    from posts
    where published),
post_found as (
    select rn row_found
    from numbered_posts
    where slug = 'the-slug')
select id, slug, published
from numbered_posts
cross join post_found
where rn >= row_found- 1 and rn <= row_found+ 1

Upvotes: 1

Caullyn
Caullyn

Reputation: 166

Here's a very hacky way to do it:

SELECT * 
  FROM posts 
 WHERE id IN (
    SELECT id
      FROM posts
     WHERE slug = 'the-slug'
    UNION

    SELECT id+1
      FROM posts
     WHERE slug = 'the-slug'
    UNION

    SELECT id-1
      FROM posts
     WHERE slug = 'the-slug'
    )
   AND published = TRUE
 ORDER BY id ASC
 LIMIT 3;

There may be a more efficient way with LAG and LEAD options.

Upvotes: 1

Related Questions