Postgres / SQL count across multiple tables based on multiple tables

Question

I have 4 tables, and the goal is a simple true/false (or count if it's easier) for whether there exists either an AccountLink or a ResourceLink based on given User.uuid and Resource.uuid.

Minimal working example

http://sqlfiddle.com/#!17/09144/1

Goal

True/false response based on User.uuid and Resource.uuid, which are unrelated.

Schema

User
- id
- uuid

Resource
- id
- account_id
- uuid

AccountLink
- id
- account_id
- user_id

ResourceLink
- id
- resource_id
- user_id

Currently working

Gives id list instead of true/false, which can be handled in application code, but the query itself is also inefficient:

SELECT id
FROM "AccountLinks"
WHERE user_id = (SELECT id FROM "Users" WHERE uuid = ?) 
  AND account_id = (SELECT account_id FROM "Resources" WHERE uuid = ?)
UNION
SELECT id
FROM "ResourceLinks"
WHERE user_id = (SELECT id FROM "Users" WHERE uuid = ?)
  AND resource_id = (SELECT id FROM "Resources" WHERE uuid = ?)

Is there a way to find the correct user and resource once based on their uuid, and then use those id properties to count across multiple tables?

You mention count and true/false, but your query return a list of id. so not sure what result you want — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented May 28, 2019 at 16:53
Is user.uuid related to resource.uuid? or are two separated inputs? — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented May 28, 2019 at 16:56

Tyler · Accepted Answer · 2019-05-28 18:20:10Z

1

Hmmm . . . I think you want a cross join with additional information brought in:

select 
  (exists (select 1
     from "AccountLinks" al
     where al.user_id = u.id and al.account_id = r.account_id
  )) as has_accountlink,
  (exists (select 1
     from "ResourceLinks" rl
     where rl.user_id = u.id and rl.resource_id = r.id
  )) as has_resourcelink
from "Users" u cross join
     "Resources" r 
where u.uuid = ? and r.uuid = ?

edited May 28, 2019 at 18:20

Tyler

11.5k10 gold badges72 silver badges107 bronze badges

answered May 28, 2019 at 16:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Juan Carlos Oropeza Over a year ago

You miss the LEFT JOIN ON condition. Also resource.uuid seem to have an input variable too.

Tyler Over a year ago

I'll look into cross join. Meanwhile uuid is different for User and for Resource. I have updated my question to reflect this more clearly.

Collectives™ on Stack Overflow

Postgres / SQL count across multiple tables based on multiple tables

Minimal working example

Goal

Schema

Currently working

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Minimal working example

Goal

Schema

Currently working

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related