asked    Michael     2018-07-01       sql       163 view        1 Answer

[SOLVED] Recursive CTE including unions inside anchor and recursive expression

So i have three tables with the following schema,

Users(id, name)

Colleagues(id1, id2)

Friends(id1, id2)

And i need to write a query that returns every pair of id's so that id_2 can be reached from id_1 using an arbitrary number of connections between colleagues and friends. I worked out a query that gives me every connection using either Colleagues or Friends, but not both. This is is what i came up with trying to use both tables in the same CTE:

WITH RECURSIVE Reachable (id_1, id_2)
AS (
SELECT
    *
FROM (
    SELECT
        id,
        FRIENDS.id2
    FROM
        USERS,
        FRIENDS
    WHERE
        FRIENDS.id1 = USERS.id
    UNION
    SELECT
        id,
        COLLEAGUES.id2
    FROM
        USERS,
        COLLEAGUES
    WHERE
        COLLEAGUES.id1 = USERS.id)
UNION
SELECT
    *
FROM (
    SELECT
        REACHABLE.id_1,
        FRIENDS.id2
    FROM
        REACHABLE,
        FRIENDS
    WHERE
        REACHABLE.id_2 = FRIENDS.id1
    UNION
    SELECT
        REACHABLE.id_1,
        COLLEAGUES.id2
    FROM
        REACHABLE,
        COLLEAGUES
    WHERE
        REACHABLE.id_2 = COLLEAGUES.id1));

But i'm getting this error:

Error: near line 1: recursive reference in a subquery: Reachable

Does that mean i can't/shouldn't use subqueries in a recursive call in general? is it even possible to perform this query inside the same CTE? if so, how could i do it? Thanks in advance!

  1 Answer  

        answered    Adonis     2018-07-01      

The reference to the recursive CTE must not be in a subquery, and the two parts separated with UNION (ALL) must be a the top level of the WITH.

If there is no difference between friends and colleagues for this query, just merge the two tables before doing the recursive CTE:

WITH RECURSIVE
Connections AS (
  SELECT id_1, id_2 FROM Colleagues
  UNION ALL 
  SELECT id_1, id_2 FROM Friends
),
Reachable(id_1, id_2) AS (
  SELECT ...
  FROM Users, Connections
  ...
  UNION
  ...
)
SELECT * FROM Reachable;




Your Answer





 2018-07-01         Tyrone

how to split a monorepo accross multiple teams each has access to only a slice of repo?

there are a lot of good advantages in monorepo, we also read that large companies like google and facebook use this tech to keep all source code in single repo.but question is how do you manage to limit access of a certain team to projects he is working on only in monorepo ?example we have a microservice infrastructureservice Aservice BApi Gatewayservice A here is developer using Team A, and service B is developed by different team (team b) while Api Gateway is common repo in this project.now how can we limit access of each team to his own service only ?also since a monorep...
 git                     1 answers                     83 view
 2018-07-01         Sebastian

Spring boot 2 with OAUTH 2.0 implementation in own authorization server

I created a new spring boot 2 project in a view to create microservices with eureka server, api gateway using zuul proxy and tried inter communication between microservices.But, coming to security, tried to implement oauth for the api gateway. But now the 'oauth/token' endpoint which i used in spring 1.x versions to get the access token is not working in spring boot 2. As i googled, spring boot 2 undergone some changes with spring security 5 to default encryption and decryption forclient-idclient-secretuser password as well and changed that too in mysql database with bcrypt...
 spring                     1 answers                     85 view
 2018-07-01         Fay

How to authenticate the request in this microservice?

I have my web application(App1) storing user credential in DB. App1 needs to talk to microservice app2(using same same DB).I am not getting how to authenticate the request sent by app1 on app2 ?My Approach :-Send user name/password (sent by client to app1) to app2. App2 will authenticate in DB, if success generate new jsessionId and send it back to app1.Now whwn app1 needs to communicate with app2, it will use same jsession which app2 will validate and allow. Is this approach looks good or there can be some other better approach ? There are several approaches each havi...
 java                     1 answers                     86 view