Created
November 26, 2017 19:13
-
-
Save benbonnet/39020f0812e4e192e7dbc5d1109e04aa to your computer and use it in GitHub Desktop.
Recursive postgres
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
const _ = require('lodash') | |
const { Client } = require('pg') | |
// CONFIGURATION | |
const client = new Client({ | |
host: process.env.PG_HOST, | |
port: process.env.PG_PORT, | |
user: 'admin', | |
password: process.env.PG_PASSWORD, | |
database: 'app-development' | |
}) | |
client.connect() | |
const query = (q,cb) => { | |
client.query(q).then(res => { | |
cb(res) | |
}).catch( (err) => { | |
cb(null,err) | |
}) | |
} | |
// NORMALIZERS | |
const unflatten = function(a, b, c) { | |
(c = "undefined" == typeof c ? [] : c), | |
(b = "undefined" == typeof b ? { id: 0 } : b); | |
var d = _.filter(a, function(e) { | |
return e.parent_id == b.id; | |
}); | |
return ( | |
_.isEmpty(d) || | |
(0 == b.id ? (c = d) : (b.children = d), | |
_.each(d, function(e) { | |
unflatten(a, e); | |
})), | |
c | |
); | |
} | |
const queries = { | |
login: (email,passord,cb) => { | |
query(`SELECT * FROM users WHERE email = "${email}"`, (res,err) => { | |
}) | |
}, | |
item: (slug, cb) => { | |
const q = ` | |
WITH RECURSIVE tree AS ( | |
SELECT | |
"items".* | |
0::bigint as parent_id | |
FROM "items" | |
INNER JOIN "friendly_id_slugs" | |
ON "friendly_id_slugs"."sluggable_id" = "items"."id" AND "friendly_id_slugs"."sluggable_type" = "Item" | |
WHERE | |
"friendly_id_slugs"."sluggable_type" = 'Item' | |
AND | |
"friendly_id_slugs"."slug" = "${slug}") | |
ORDER BY "items"."id" ASC LIMIT 1 | |
UNION ALL | |
SELECT | |
it.*, | |
t.id as parent_id | |
FROM items as it, tree as t | |
INNER JOIN item_children as itc | |
ON t.id = itc.item_id | |
WHERE itc.child_id = it.id | |
) | |
SELECT * FROM tree | |
` | |
query( q, (res,err) => { | |
cb(unflatten(res.rows)) | |
}) | |
} | |
} | |
module.exports = queries |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment