Mysql – Find next entity with ordered junction table

many-to-manyMySQL

I have guides which are linked to series (many to many) with a junction table like so:

guides

id  etc.. (more columns)
-- 
1  
13 
21
30

series

id  etc.. (more columns)
--
1
3
4

series_guides (primary key consists of these 3 columns) also guide_id is unique, a guide can only link to a series once.

PK series_id  PK guide_id  PK order
------------  --------     -----
1             13           1
1             21           3
1             1            2
3             30           2

Now my question is: How do I get the next guide when I know the ID of some other guide?

Example: Someone is reading guide 13 (guide_id), what query do I need to run to get the next guide from the series. Which is guide 1.

This is what I tried so far, and I actually think it works. However maybe there is a better way to do this?

SELECT
    sg2.`order`,
    sg2.guide_id
FROM series_guides sg
JOIN series_guides sg2 ON sg2.series_id = sg.series_id
WHERE sg.guide_id = 13 AND sg2.order > sg.`order`
ORDER BY sg2.`order`
LIMIT 1;

Any help is much appreciated

Best Answer

Your query is correct and does work. I think you can make the intent a little bit more clear by writing it in a slightly diffent way (although this is open for debate). The execution plans (on MariaDB) are exactly the same.

The comments in the middle should be explanatory:

SELECT
    sg.series_id, sg.guide_id, sg.ord
FROM
    series_guides sg
    JOIN
    (-- We get the current `series_id`, `ord`, based on `guide_id`
    SELECT
        series_id, ord
    FROM
        series_guides sg
    WHERE
        guide_id = 13
    ) AS sg0
    -- We want to be in the same series_id, but greater ord
    ON sg.series_id = sg0.series_id AND sg.ord > sg0.ord
ORDER BY
    sg.ord  
LIMIT
    1 -- We just want one result
;

This will give you:

series_id | guide_id | ord
--------: | -------: | --:
        1 |        1 |   2

If you change then the guide_id to be 1, you'll get the next value as:

series_id | guide_id | ord
--------: | -------: | --:
        1 |       21 |   3

Check everything at dbfiddle here

Remarks and (simplified) table definitions:

I've defined the tables with different namings, to avoid confusions (and not to clash with the ORDER keyword, just in case):

CREATE TABLE guides
(
    guide_id integer PRIMARY KEY,
    guide_stuff varchar(100)
) ;

CREATE TABLE series
(
    series_id integer PRIMARY KEY,
    series_stuff varchar(100)
);

If guide_id must be UNIQUE (and NOT NULL, or otherwise, it doesn't make any sense) it's already a candidate key. Being the simplest candidate key, it's the one I'd use as primary key:

CREATE TABLE series_guides
(
    series_id integer NOT NULL REFERENCES series(series_id),
    -- If this is unique, make it just the primary key. It's simpler
    guide_id integer PRIMARY KEY REFERENCES guides(guide_id),
    ord integer NOT NULL,
    -- This covering index will prove useful
    UNIQUE (guide_id, series_id, ord)
) ;

Probably, you would want to have also another restriction: UNIQUE (guide_id, ord) as well, to avoid situations where your order might not we well defined.

Related Question