Skip to content

Instantly share code, notes, and snippets.

@psypersky

psypersky/query.js

Last active Apr 5, 2017
Embed
What would you like to do?
squel.js (postgres) and pg-promise
const squel = require('squel').useFlavour('postgres');
const baseQuery = squel.select()
.field('s.id')
.field('s.user_id')
.field('s.created')
.field('s.title')
.field('s.description')
.field('s.game_id')
.field('s.army_id')
.field('s.bucket_id')
.field('s.url')
.field('s.image')
.field('s.attachments')
.field('s.likes_count')
.field('s.views_count')
.field('u.username')
.from('shots as s')
.left_join('users', 'u', 'u.id = s.user_id')
.offset(offset)
.limit(limit);
if (userId) {
baseQuery
.left_join('user_shots_likes', 'l', 'l.shot_url = s.url AND l.user_id = $1')
.field('l.liked');
}
if (gameId) {
baseQuery.where('s.game_id = $2');
}
if (armyId) {
baseQuery.where('s.army_id = $3');
}
if (time) {
baseQuery.where(`s.created > (current_time - interval '$4 days')`);
}
switch (sort) {
case 'most_recent':
baseQuery.order('s.created', false);
break;
case 'least_recent':
baseQuery.order('s.created', true);
break;
case 'most_likes':
baseQuery.order('s.likes_count', false);
break;
case 'least_likes':
baseQuery.order('s.likes_count', true);
break;
default:
throw new Error('Invalid sort option');
}
// Connect to first DB
const pgp = require('pg-promise')({});
const db = pgp({});
const shots = await db.any(baseQuery.toString(), [
userId,
gameId,
armyId,
time,
]);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.