Question: Self referential query?

[luke-momentusresearch]

Hi, my table schema is as follows

user {
    id
    role = 'admin'|'manager'|'guest'
    org_key
}

What would be the best way to create a query similar to select * from user where org_key =(select org_key from user where id=$user_id)

Have tried:

  - name: users
    source: default
    columns:
      - name: role
        related_to: users.role
...
where: {role:{in:  [{id: {eq: $id}}]}}

but result set show all the rows in the DB and it isn't filtered