Skip to content

Instantly share code, notes, and snippets.

@samsch
Last active September 23, 2021 14:44
Show Gist options
  • Save samsch/668801ae6913f8ecbe28f050998d2ca9 to your computer and use it in GitHub Desktop.
Save samsch/668801ae6913f8ecbe28f050998d2ca9 to your computer and use it in GitHub Desktop.
Nested Knex query
knex
.select([
'users.*',
knex.raw('json_agg("posts") as posts')
])
.from('users')
.leftJoin(function () {
this.select(['posts.*', knex.raw('json_agg("comments") as comments')])
.from('posts')
.leftJoin('comments', { 'posts.id': 'comments.post' })
.groupBy('posts.id')
.as('posts')
}, { 'users.id': 'posts.user' })
.groupBy('users.id');
// DB Schema built from:
[
{
name: 'users',
columns: {
id: 'id_uuid',
name: 'string',
login: 'string',
password: 'string',
},
},
{
name: 'posts',
columns: {
id: 'id_uuid',
user: ['foreign', 'users'],
title: 'string',
text: 'string',
},
},
{
name: 'comments',
columns: {
id: 'id_uuid',
post: ['foreign', 'posts'],
user: ['foreign', 'users'],
text: 'string',
},
},
];
/* Knex query sql output */
select
"users".*,
json_agg("posts") as posts
from
"users"
left join (
select
"posts".*,
json_agg("comments") as comments
from
"posts"
left join "comments" on "posts"."id" = "comments"."post"
group by
"posts"."id"
) as "posts" on "users"."id" = "posts"."user"
group by
"users"."id";
[
{
"id": "5d85a42f-fea2-45f7-987d-83290e0bb6ce",
"name": "James Dean",
"login": "jdean",
"password": "",
"posts": [
{
"id": "4282206a-8949-4656-bb2a-e9fe9240a74b",
"user": "5d85a42f-fea2-45f7-987d-83290e0bb6ce",
"title": "James Dean's confusing life",
"text": "Like, this would actually be long tho.",
"comments": [
{
"id": "062ef3ca-fa45-449e-aa0f-6565d984c355",
"post": "4282206a-8949-4656-bb2a-e9fe9240a74b",
"user": "a07c08bf-da49-4718-8eaf-491adf058b5f",
"text": "Comment by Jenna on James Deans confusing Life!"
}
]
}
]
},
{
"id": "a07c08bf-da49-4718-8eaf-491adf058b5f",
"name": "Jenna Marbles",
"login": "jmarb",
"password": "",
"posts": [
{
"id": "4f041d1d-a233-40d1-b0d6-a634d032e9d8",
"user": "a07c08bf-da49-4718-8eaf-491adf058b5f",
"title": "Losing yourself to sand",
"text": "Like, this would actually be long tho.",
"comments": [
null
]
},
{
"id": "ba277a3f-48ad-4f6a-9f07-aebd4a6ebcc2",
"user": "a07c08bf-da49-4718-8eaf-491adf058b5f",
"title": "I'm not a starfish man",
"text": "Like, this would actually be long tho.",
"comments": [
null
]
},
{
"id": "dbe2141e-d8e6-494b-ab8c-bb4696578268",
"user": "a07c08bf-da49-4718-8eaf-491adf058b5f",
"title": "Smelling farts for fun and profit!",
"text": "Like, this would actually be long tho.",
"comments": [
null
]
},
{
"id": "fb919cfc-e9b0-4770-9455-ce5871728d0a",
"user": "a07c08bf-da49-4718-8eaf-491adf058b5f",
"title": "Transparency is cool",
"text": "Like, this would actually be long tho.",
"comments": [
{
"id": "30e52161-736c-4cbc-a525-9ea9c0e93af8",
"post": "fb919cfc-e9b0-4770-9455-ce5871728d0a",
"user": "a07c08bf-da49-4718-8eaf-491adf058b5f",
"text": "Comment by Jenna on Transparency is cool!"
},
{
"id": "8f93f707-78ff-4f66-ac76-207e601f5f71",
"post": "fb919cfc-e9b0-4770-9455-ce5871728d0a",
"user": "5d85a42f-fea2-45f7-987d-83290e0bb6ce",
"text": "Comment by James on Transparency is cool!"
}
]
}
]
}
]
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment