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

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

Jon Armstrong · Accepted Answer · 2021-12-08 01:42:47Z

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

iterate over the versions (recursively), but keep the base version with each related prior version.
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   |
+------+---------+

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

Ramin Faracov · Accepted Answer · 2021-12-08 04:27:53Z

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 |
+--------+--------------+-------------+--------------------+

Collectives™ on Stack Overflow

recursive query postgresql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related