Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

express complex query with json_agg with objection.js #2369

Open
codelover2k opened this issue Mar 24, 2023 · 0 comments
Open

express complex query with json_agg with objection.js #2369

codelover2k opened this issue Mar 24, 2023 · 0 comments

Comments

@codelover2k
Copy link

hi!
I have the tables users, contacts, permissions, roles, users_roles and roles_permissions.
A user have exactly one contact.
A user can have many roles.
A role can have many permissions.
This is the raw SQL that I am using to get the users, its contact data and all permissions of the user:

SELECT
	users.id,
	users.username,
	contacts.id,
	contacts.salutation,
	contacts.first_name,
	contacts.last_name,
	json_agg(DISTINCT permissions.name)
FROM
	users
	LEFT JOIN contacts
	       ON contacts.user_id = users.id
	LEFT JOIN users_roles
	       ON users_roles.user_id = users.id
	LEFT JOIN roles_permissions
	       ON roles_permissions.role_id = users_roles.role_id
	LEFT JOIN permissions
	       ON permissions.id = roles_permissions.permission_id
WHERE
	users.id = 2
GROUP BY
    users.id,
    contacts.id;

can someone please help me to express this with objection.js?

thanks!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants