recursive query postgresql

Question

I am having trouble doing a recursive query with sql. I wanted to know what is the best way to solve this.

I have the following model

enter image description here

where basically there are versions of applications, which are installed in different environments. It is important to note that each version has a previous version, so a model similar to the hotfix can be applied. To know the tickets that are in an environment, it is necessary to search for the latest version of the environment and make recursion

my goal is to build a table as follows:

ticket | arrival_date_to_enviroment | arrival_version_to_enviroment | enviroment

I suppose I have to do a recursive query, to know which tickets are in which version. For this I think that the first step would be to know the versions that a version includes I can do this part of the problem by posing something in the following way:

 WITH RECURSIVE version_recursive AS (
 select v.name, v.version_from, v.application from versions v 
 where v.name='21.1204.0'
 UNION
 select v.name, v.version_from, v.application from versions v 
 JOIN version_recursive s ON s.version_from = v.name AND s.application = v.application
 )
 SELECT *
 FROM version_recursive;

the next step would be to find the latest deploy in the environment and join with tickets.

But one of my main problems is that I don't know how to complete arrival_version_to_enviroment using sql

I really appreciate any help and advice. I need to do this using sql, without programming logic

Answer 1

If I understand your meaning of arrival, something like this might do it:

1. iterate over the versions (recursively), but keep the base version with each related prior version.
2. join in the various required detail

I included more detail in the result than was requested, and more is probably suggested. For instance, the initial environment a ticket is introduced is useful to know, but so is the current environment for this deployment. I decided to include just the initial environment introduced. It's trivial to add more detail.

The SQL:

WITH RECURSIVE versionx (base, name, version_from, application, lv) AS (
 SELECT name, name, version_from, application, 1 FROM versions
 UNION ALL
 SELECT v0.base, v1.name, v1.version_from, v1.application, lv+1
 FROM versions AS v1
 JOIN versionx AS v0
 ON v0.version_from = v1.name
 AND lv < 20 -- Temporary guard against bad data. Remove when ready.
 )
SELECT t.id AS ticket_id -- The ticket
 , d.date AS arrival_date -- Initial date ticket was introduced.
 , x.name AS arrival_version -- Initial version ticket was introduced.
 , x.base AS version -- Current version.
 , d0.date AS cur_date -- Date of deployment of current version.
 , e.name AS env_name -- Initial deployment environment
 FROM versionx AS x
 JOIN tickets AS t
 ON t.version = x.name
 JOIN deployments AS d
 ON d.version = x.name
 JOIN environments AS e
 ON e.id = d.environment_id
 JOIN deployments AS d0
 ON d0.version = x.base
;

The result, based on given test data:

+-----------+--------------+-----------------+---------+------------+----------+
| ticket_id | arrival_date | arrival_version | version | cur_date | env_name |
+-----------+--------------+-----------------+---------+------------+----------+
| 1 | 2021年12月07日 | ver01 | ver01 | 2021年12月07日 | env01 |
| 1 | 2021年12月07日 | ver01 | ver02 | 2021年12月08日 | env01 |
| 2 | 2021年12月08日 | ver02 | ver02 | 2021年12月08日 | env02 |
+-----------+--------------+-----------------+---------+------------+----------+

The test data:

SELECT * FROM versions;
+-------+--------------+-------------+
| name | version_from | application |
+-------+--------------+-------------+
| ver01 | NULL | app01 |
| ver02 | ver01 | app02 |
+-------+--------------+-------------+
SELECT * FROM deployments;
+------+----------------+------------+---------+
| id | environment_id | date | version |
+------+----------------+------------+---------+
| 1 | 1 | 2021年12月07日 | ver01 |
| 2 | 2 | 2021年12月08日 | ver02 |
+------+----------------+------------+---------+
SELECT * FROM environments;
+------+-------+-------------+-------+
| id | name | application | url |
+------+-------+-------------+-------+
| 1 | env01 | app01 | url01 |
| 2 | env02 | app02 | url02 |
+------+-------+-------------+-------+
SELECT * FROM tickets;
+------+---------+
| id | version |
+------+---------+
| 1 | ver01 |
| 2 | ver02 |
+------+---------+

• user60108: I took as a base your answer to make the query, posing the query in that way really served me. Thank you. I should buy you a beer

Answer 2

Different Example:

-- creating table 
CREATE TABLE versions (
 "name" text NULL,
 version_from text NULL,
 aqpplication text NULL
);
-- insert sample data for test 
INSERT INTO versions ("name", version_from, aqpplication) VALUES('1.0', NULL, 'app1');
INSERT INTO versions ("name", version_from, aqpplication) VALUES('1.1', '1.0', 'app1');
INSERT INTO versions ("name", version_from, aqpplication) VALUES('1.2', '1.1', 'app1');
INSERT INTO versions ("name", version_from, aqpplication) VALUES('1.3', '1.21', 'app1');
INSERT INTO versions ("name", version_from, aqpplication) VALUES('5.5', NULL, 'app2');
INSERT INTO versions ("name", version_from, aqpplication) VALUES('5.6', '5.5', 'app2');
INSERT INTO versions ("name", version_from, aqpplication) VALUES('5.7', '5.6', 'app2');
INSERT INTO versions ("name", version_from, aqpplication) VALUES('5.8', '5.7', 'app2');
INSERT INTO versions ("name", version_from, aqpplication) VALUES('1.4', '1.3', 'app1');
-- recursive select query 
WITH RECURSIVE version_recursive(name, version_from, application, version_history) AS (
 SELECT vn.*, vn."name"::text
 FROM versions AS vn 
 WHERE vn.version_from is null 
UNION ALL
 SELECT v.*, 
 (v_rec.version_history || '->' || v."name"::TEXT) 
 FROM version_recursive as v_rec, versions AS v 
 WHERE v.version_from = v_rec."name"
)
SELECT * FROM version_recursive ORDER BY "name";
Result: 
+--------+--------------+-------------+--------------------+
| name | version_from | application | version_history |
+--------+--------------+-------------+--------------------+
| 1.0 | NULL | app1 | 1.0 |
| 1.1 | 1.0 | app1 | 1.0->1.1 |
| 1.2 | 1.1 | app1 | 1.0->1.1->1.2 |
| 5.5 | NULL | app2 | 5.5 |
| 5.6 | 5.5 | app2 | 5.5->5.6 |
| 5.7 | 5.6 | app2 | 5.5->5.6->5.7 |
| 5.8 | 5.7 | app2 | 5.5->5.6->5.7->5.8 |
+--------+--------------+-------------+--------------------+