1

I'm trying to get the next sibling in a list according to an integer position column. Rows are siblings when they have the same parent_id.

Given that sibling foo.id = 192 and foo.position = 0, I want to get sibling bar that has a position of foo.position + 1

The below query returns the right result except that the AND a.position = 0+1 should not be hardcoded.

SELECT a.title as taskTitle, b.title as projectTitle
FROM tasks a 
INNER JOIN tasks b
ON a.parent_id = (
    SELECT parent_id FROM tasks WHERE id = 192
)
AND a.position = 0+1
AND a.root_id = b.id;

The following query won't work of course but shows what I'm trying to do.

SELECT a.title as taskTitle, b.title as projectTitle
FROM tasks a 
INNER JOIN tasks b
ON a.parent_id = (
    SELECT parent_id, position as pos FROM tasks WHERE id = 192
)
AND a.position = pos+1
AND a.root_id = b.id;

Is this possible without first querying the needed information and saving it into a variable to use in a second query?

Would it be faster than saving the result of a "pre-query" to use in a second query?

EDIT:

Here's some test queries that set up a demo

Setup

CREATE TABLE tasks (
    id int unsigned NOT NULL AUTO_INCREMENT,
    root_id int unsigned,               /* top level task */
    parent_id int unsigned,             /* the task id that is the parent of this task */
    `title` varchar(255) NOT NULL,
    position int unsigned,              /* the index in the array that will contain this task */
    FOREIGN KEY (root_id) REFERENCES tasks(id),
    FOREIGN KEY (parent_id) REFERENCES tasks(id),
    PRIMARY KEY(id)
);

INSERT INTO tasks (root_id, parent_id, `title`, position) VALUES 
(NULL, NULL, 'Foo Project', 0),
(NULL, NULL, 'Bar Project', 1),
(NULL, NULL, 'Baz Project', 2);

INSERT INTO tasks (root_id, parent_id, `title`, position) VALUES 
(1, 1, 'Foo Child A', 0),
(1, 1, 'Foo Child B', 1),
(2, 2, 'Bar Child A', 0);

INSERT INTO tasks (root_id, parent_id, `title`, position) VALUES 
(2, 6, 'Bar Child A Grandchild 1', 0),
(1, 5, 'Foo Child B Grandchild 1', 0),
(1, 5, 'Foo Child B Grandchild 2', 1);

Query for needed info

The following query is suboptimal but retrieves the info needed.

SELECT a.title as taskTitle, b.title as projectTitle
FROM tasks a 
INNER JOIN tasks b
ON a.parent_id = (
    SELECT parent_id FROM tasks WHERE id = 8
)
AND a.position = (SELECT position FROM tasks WHERE id = 8)+1
AND a.root_id = b.id;
2
  • Given that sibling foo.id = 192 and foo.position = 0, I want to get sibling bar that has a position of foo.position + 1 Does you have a guarantee that foo.position + 1 value is present in the table (i.e. no holes in this field values)?
    – Akina
    Sep 26, 2019 at 6:02
  • No, it can be null or an empty result set.
    – slanden
    Sep 26, 2019 at 14:49

1 Answer 1

1

It seems you need

SELECT t1.title taskTitle, 
       t2.title projectTitle
FROM tasks t1
JOIN tasks t2 ON t1.parent_id = t2.parent_id
             AND t1.position + 1 = t2.position
WHERE t1.id = 192
5
  • This doesn't quite return the result that my query returns. Your query returns both sibling tasks, where the taskTitle column holds the title of sibling at position 0 and projectTitle holds sibling at position 1. What I need is the sibling at position 1 to be in the taskTitle column and the title of the siblings' root task, noted by root_id in my query, to be in the projectTitle column.
    – slanden
    Sep 26, 2019 at 15:17
  • @slanden Does you want to say it is enough to swap output fields names?
    – Akina
    Sep 27, 2019 at 5:05
  • Swapping taskTitle and projectTitle won't do because even after the swap, the result put into projectTitle is not right. It's just a direct sibling in the nested structure of tasks and I need the root task's title.
    – slanden
    Sep 27, 2019 at 14:50
  • @slanden It seems I cannot understand the whole algo you need to realize. Maybe you can to add a small sample data (5-6 records) and the result for it?
    – Akina
    Sep 27, 2019 at 16:42
  • I edited the post with some queries.
    – slanden
    Sep 27, 2019 at 22:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.