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)
| id | parent_id | foreign_key | start_date | end_date |
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
| id | start_date | end_date | number_of_objects |
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:
objectGroup. The item in the new view consists of
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
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).
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;
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.)