MySQL - Create View That Gets the Min/Max values of a collection of results

Question

Delving into some complex SQL here. I want to create a view/virtual table.

I have an object table. Which looks something like this (all values are int)

CREATE TABLE objects (
    id int AUTO_INCREMENT PRIMARY KEY,
    parent_id INT(11),
    foreign_key INT(11),
    start_date INT(11),
    end_date INT(11)
);

INSERT INTO objects 
VALUES 
(1, 0, 1, 1638930577, 1638930578),
(2, 1, 1, 1638930578, 1638930578),
(3, 2, 1, 1638930578, 1638930578),
(4, 0, 2, 1638930576, 1638930578),
(5, 4, 2, 1638930578, 1638930578),
(6, 5, 2, 1638930578, 1638930578),
(7, 0, 1, 1638930574, 1638930578),
(8, 7, 1, 1638930578, 1638930578),
(9, 7, 1, 1638930578, 1638930579)

I want to create a view that looks like

I've never used SQL views before, and would be ideal in my situation rather than writing code. Is this possible? My main qualm is that when I do this in code, I need to give it an object_id to start, really not sure how to just get a group of objects with the same parent id that isn't 0.

Thank you

P.S Using MySQL/Maria most recent version but dialect probably isn't super important.

SQL Fiddle: http://sqlfiddle.com/#!9/429570/1

The return result from the new view, provided from the schedule should look like

id	foreign_key	start_date	end_date	number_of_objects
1	1	1638930577	1638930578	3
2	2	1638930576	1638930578	3
3	1	1638930574	1638930579	3

The return result should be built something like this:

Gets all objects with parent = 0.
For each of those objects, get all the child objects (WHERE parent = parent.id)
The parent and its children consist of one objectGroup. The item in the new view consists of
1. id = an autogenerated id. Probably optional but feels good to have.
2. foreign_key = the same foreign key value as all objects in the objectGroup should have.
3. start_date - min() of all start dates in the objectGroup.
4. end_date - max() of all end dates in the objectGroup.
5. number_of_objects - count() of all objects in the objectGroup

user_87015 · Answer

Looks like your data is three levels deep (may be deeper). You can use recursion to traverse from parent to child to grandchildren. This requires MySQL 8 or later unfortunately:

with recursive rcte as (
    /***
      select all topmost level rows
      the id and foreign_key of the parent will be "copied" to all children and grandchildren
      the last id column is needed to link the next set of rows with this one
    ***/
    select id as group_id, foreign_key, start_date, end_date, id
    from objects
    where parent_id = 0
    union all
    /***
      p is the set of rows from previous iteration (this is how recursive cte works)
      c is the set of rows that are direct children of p
      p.group_id and p.foreign_key are copied from parent (which in turn were copied from their parent and so on)
    ***/
    select p.group_id, p.foreign_key, c.start_date, c.end_date, c.id
    from rcte as p
    join objects as c on c.parent_id = p.id
)
/***
  all "object groups" now have same group_id and foreign_key
  we just need to group by
***/
select group_id, foreign_key, min(start_date) as start_date, max(end_date) as end_date, count(*) as number_of_objects
from rcte
group by group_id, foreign_key

db<>fiddle

You might want to consider other models for storing the data. Adjacency list (the one you're using) is simpler to maintain (insert, update, delete) but difficult to query (e.g find subtree of given node) without recursion. Nested set model is a good alternate which is simpler to query but difficult to maintain. Materialized paths model is another candidate which is simple to query but difficult to maintain (easier to implement with array datatype but no referential integrity).

user_5283663 · Answer

If I understood correctly every ObjectGroup has only one parent, wich is the record where parent_id = 0 so basically a parent is identifying a group which means you have already an ObjectGroup.id On each Object group the number of members is the count of all children with same foreign_key plus its parent (+1) Start_date and end_date of the parent have to be taken in account to identify min and max on the ObjectGroup

CREATE OR REPLACE VIEW v AS 
 SELECT  p.id, p.foreign_key, 
   MIN(LEAST(p.start_date,c.start_date)) AS obj_start_date, 
   MAX(GREATEST(p.end_date,c.end_date)) AS obj_end_date, 
   COUNT(c.id) + 1 AS obj_count
 FROM objects AS p 
 INNER JOIN myobjects AS c ON c.parent_id = p.id AND c.foreign_key = p.foreign_key 
 WHERE p.parent_id = 0
 GROUP BY p.id, p.foreign_key  ;

SELECT * FROM v;

user_1766831 · Answer

Make your life easier. Add a group_id column. Then the query is simply:

SELECT group_id, MIN(start_date), MAX(end_date), COUNT(*)
    FROM objects
    GROUP BY group_id;

(Plus a wrapper to turn it into a VIEW.)

MySQL - Create View That Gets the Min/Max values of a collection of results

3 Answers