0

I have a table in my database that looks vaguely like this:

CREATE TABLE locations (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(255),
  group_id BIGINT,
  type VARCHAR(255)
  FOREIGN KEY (group_id) REFERENCES locations(id)
);

In other words, a hierarchy of locations. Each location is either a group (type = 'group') or a regular location (type IS NULL). Both groups and regular locations can belong to a "parent" group, referenced by group_id. Only groups may serve as a parent; regular locations cannot. So, given these records:

INSERT INTO locations (id, name, group_id, type) VALUES
  (1, 'Top-level 1', NULL, 'group'),
  (2, 'Mid-level', 1, 'group'),
  (3, 'Location 1', 2, NULL),
  (4, 'Location 2', 2, NULL),
  (5, 'Location 3', 1, NULL),
  (6, 'Top-level 2', NULL, 'group'),
  (7, 'Location 4', 6, NULL);

I'd like to query for locations that have "Top-level 1" anywhere in their parent chain. Record IDs 3, 4, and 5 should be returned; 7 should not.

If this were just a single optional parent, this would be easy: a single INNER JOIN. The problem I'm facing is that the structure can have an arbitrary number of parents: this example has two levels of parents, but it could be anything. I also need the solution to work with non-top-level parents - if I queried instead for locations with "Mid-level" in their parent chain, that also needs to work.

I'm sort of expecting the answer to be no - or at least certainly not with JOINs and WHEREs - but is there a way to do this?

ArtOfCode
  • 5,702
  • 5
  • 37
  • 56
  • [Mansging hierarchical data in MySQL](https://web.archive.org/web/20181221162916/http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/) – Barmar Jan 24 '19 at 16:03
  • Without knowing how many nodes per child, that's easier to do in MySql 8 or MariaDb 10. [Old SO post](https://stackoverflow.com/a/33737203/4003419) – LukStorms Jan 24 '19 at 16:11
  • @LukStorms `select version()` says I'm on 5.7 right now, but I may well be able to upgrade it. If you want to add that comment as an answer, I'll throw it an upvote at least – ArtOfCode Jan 24 '19 at 16:16
  • @ArtOfCode Thanks, but I'll pass. :) A suggestion to upgrade is not a good answer anyway. But I do believe that the [Adjacency List](http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/) Model is kinda a bad fit for MySql 5.x. Sure, if you know the maximum node depth is fixed, then you could use the same number of left joins as that max depth. But that won't be pretty. – LukStorms Jan 24 '19 at 16:27
  • @LukStorms Aye, agreed. That, unfortunately, I can't change, so one way or another I have to work with it. – ArtOfCode Jan 24 '19 at 16:39
  • Example test [here](https://rextester.com/PSDK22697), which assumes that there are maximum 3 levels of child-parent. – LukStorms Jan 24 '19 at 17:10

0 Answers0