Need to create a View for the following

user_733213 0
Viewed 20

Here are three tables

Table users {
  id uuid [pk, default: `gen_random_uuid()`]
  auth_id uuid [ref: - auths.id]
  org_id uuid [ref: - orgs.id]
  access_group text [default: 'DEFAULT']
  created_at int [default: `now()::int`]
  updated_at int
  age int
  status text
}

Table op_user_access_groups {
  op_id uuid [pk, ref: > ops.id]
  access_group text [pk, default: 'DEFAULT']
}

Table op_users {
  op_id uuid [pk, ref: > ops.id]
  user_id uuid [pk, ref: > users.id]
  access boolean [default: false]
}
  1. The table users has user info and he/she belongs to certain organization (org_id)
  2. The table op_user_access_groups has the information regarding an operator having access to what all access_groups. The op_id belongs to the org_id
  3. The table op_users has information about users (user_id) that can be accessed by op_id irrespective of which group a user belongs to.

I want to create a view such that if I do

select * from <that view> where op_id = ?

I should get the users the operator has access to.

Any help is appreciated :)

cockroachdb join view sql database
1 Answers

You should use a JOIN query to create such a view. Something like this:

CREATE VIEW v AS
SELECT * FROM users
JOIN op_users ON users.id=op_users.op_id
JOIN op_user_access_groups ON op_user_access_groups.access_group=users.access_group
WHERE op_users.access = true

It's not exactly clear how your data model works from your question, but creating a view with the JOIN that answers your question is the correct response here.

user_736320
Related