How to use squel - 10 common examples
To help you get started, we’ve selected a few squel examples, based on popular ways it is used in public projects.
Secure your code as it's written. Use Snyk Code to scan source code in minutes - no build needed - and fix issues immediately.
bulktrade / SMSC / modules / admin / src / app / dashboard / dashboard.service.ts View on Github 
public getDashboard(): Observable {
let query = squel.select()
.from('Dashboard')
.field('*')
.limit(1)
.toString();
return Observable.create((observer: Observer) => {
// this.databaseService.query(query, 50, '*:3').subscribe((res: Response) => {
// let data = res.json().result[0];
// let dashboard: Dashboard = new Dashboard(
// new MetaData(
// data['@class'],
// data['@rid'],
// data['@version']
// ),
// data['icon'],
// data['name'],async function getSongs (params = {}) {
const songs = {
result: [],
entities: {}
}
const q = squel.select()
.field('songs.*')
.from('songs')
Object.keys(params).map(key => {
if (key === 'providerData' && typeof params.providerData === 'object') {
Object.keys(params.providerData).map(i => {
q.where(`json_extract(providerData, '$.${i}') = ?`, params.providerData[i])
})
} else {
q.where(`${key} = ?`, params[key])
}
})
// starred count
q.field('COUNT(stars.userId) AS stars')
.left_join('stars USING(songId)')function createQuery(queryParts, sq) {
var result = {};
var multiquery = "";
var multuvalues = [];
var tableAlias = 't1';
if (queryParts['alias'])tableAlias = queryParts['alias'];
if (queryParts['indexExpression'])tableAlias += ' '+queryParts['indexExpression'];
var s;
if (sq) {
s = squel.select()
.from(sq, tableAlias);
} else {
s = squel.select();
if (queryParts['indexExpression']) s = squel.select({autoQuoteAliasNames: false });
s.from(queryParts.table, tableAlias);
}
_.each(queryParts['joins'], function (join) {
if (join.joinedQuerParts) {
var joinedParts = join.joinedQuerParts
var sq = '';
if (joinedParts.nestedParts !== undefined && joinedParts.nestedParts !== '') {
// This is for cases where joined reports has some a sub query
sq = createQuery(joinedParts, createQuery(joinedParts.nestedParts));
} else {
sq = createQuery(joinedParts);
}
_updateJoins(join, s, sq);
} else {
_updateJoins(join, s);url: function () {
var filters = this.config.baseFilters.concat(this.getFilters())
var query = squel.select()
query.from(this.config.dataset)
// Aggregate & group by
if (this.config.valueField || this.config.aggregateFunction || this.config.groupBy) {
// If valueField specified, use it as the value
if (this.config.valueField) {
query.field(this.config.valueField + ' as value')
// Otherwise use the aggregateFunction / aggregateField as the value
} else {
// If group by was specified but no aggregate function, use count by default
if (!this.config.aggregateFunction) this.config.aggregateFunction = 'count'
// Aggregation
query.field(this.config.aggregateFunction + '(' + (this.config.aggregateField || '*') + ') as value')
}async function getProviders () {
const providers = {
result: [],
entities: {},
}
try {
const q = squel.select()
.from('providers')
.order('priority')
const { text, values } = q.toParam()
const res = await db.all(text, values)
// normalize
res.forEach(row => {
row.isEnabled = row.isEnabled === 1
providers.entities[row.name] = row
providers.result.push(row.name)
// parse JSON in prefs column
providers.entities[row.name].prefs = JSON.parse(row.prefs)
})
} catch (err) {async function getLibrary () {
const artists = {
result: [],
entities: {}
}
const songs = {
result: [],
entities: {}
}
// First query: artists
try {
const q = squel.select()
.from('artists')
.order('name')
// log(q.toString())
const { text, values } = q.toParam()
const rows = await db.all(text, values)
// normalize results
for (const row of rows) {
artists.result.push(row.artistId)
artists.entities[row.artistId] = row
// prep LUT for songIds
artists.entities[row.artistId].songIds = []
}
} catch (err) {
log(err.message)static posts( req, callback ) {
const { page, perPage } = InaturalistAPI.paginationData( req, { default: 10, max: 30 } );
const ids = _.filter( req.params.id.split( "," ), _.identity );
let numericIDs = _.filter( ids, id => Number( id ) );
if ( _.isEmpty( numericIDs ) ) { numericIDs = [-1]; }
const query = squel.select( ).field( "posts.*, count(*) OVER() AS total_count" )
.from( "posts" )
.join( "projects", null, "posts.parent_id = projects.id AND parent_type='Project'" )
.where( "projects.id IN (?) OR projects.slug IN (?)", numericIDs, ids )
.where( "posts.published_at IS NOT NULL" )
.order( "posts.published_at", false )
.limit( perPage )
.offset( perPage * ( page - 1 ) );
pgClient.connection.query( query.toString( ), ( err, result ) => {
if ( err ) { return void callback( err ); }
const results = result.rows;
const total = _.isEmpty( results ) ? 0 : Number( results[0].total_count );
_.each( results, r => ( delete r.total_count ) );
ESModel.fetchBelongsTo( results, User, { }, () => {
callback( null, {
total_results: total,
page,static followers( req, callback ) {
const { page, perPage } = InaturalistAPI.paginationData( req, { default: 10, max: 100 } );
const ids = _.filter( req.params.id.split( "," ), _.identity );
let numericIDs = _.filter( ids, id => Number( id ) );
if ( _.isEmpty( numericIDs ) ) { numericIDs = [-1]; }
const query = squel.select( ).field( "s.user_id, count(*) OVER() AS total_count" )
.from( "projects p" )
.join( "subscriptions s", null, "s.resource_type='Project' AND s.resource_id=p.id" )
.join( "users u", null, "s.user_id=u.id" )
.where( "s.resource_type='Project'" )
.where( "p.id IN (?) OR p.slug IN (?)", numericIDs, ids )
.order( "u.login" )
.limit( perPage )
.offset( perPage * ( page - 1 ) );
pgClient.connection.query( query.toString( ), ( err, result ) => {
if ( err ) { return void callback( err ); }
const results = result.rows;
const total = _.isEmpty( results ) ? 0 : Number( results[0].total_count );
_.each( results, r => ( delete r.total_count ) );
ESModel.fetchBelongsTo( results, User, { source: { includes: ["id", "login", "icon"] } },
( ) => {
callback( null, {pgClient.connection.query( listQuery.toString( ), ( err, listResult ) => {
if ( err ) { return void reject( err ); }
let query = squel
.select( )
.fields( [
"listed_taxa.taxon_id",
"listed_taxa.list_id",
"lists.comprehensive",
"listed_taxa.observations_count",
"sources.url"
] )
.field( "lists.title", "list_title" )
.field( "sources.in_text", "source_in_text" )
.from( "listed_taxa" )
.join( "lists", null, "lists.id = listed_taxa.list_id" )
.left_join( "sources", null, "sources.id = lists.source_id" )
.limit( limit );
if ( req.query.place_id ) {
query = query.where( "listed_taxa.place_id = ?", req.query.place_id );members( options, callback ) {
options = options || { };
options.page = options.page || 1;
options.per_page = options.per_page || 30;
let query = squel.select( )
.field( "project_users.*, COUNT(project_users.id) OVER() as total_count" )
.from( "project_users" )
.join( "users u", null, "project_users.user_id=u.id" )
.where( "project_users.project_id = ?", this.id );
if ( options.role === "manager" ) {
query = query.join( "projects", null, "project_users.project_id = projects.id" )
.where( "project_users.role = 'manager' OR projects.user_id = project_users.user_id" );
} else if ( options.role === "curator" ) {
query = query.join( "projects", null, "project_users.project_id = projects.id" )
.where( "project_users.role IN ? OR projects.user_id = project_users.user_id",
["manager", "curator"] );
}
if ( options.order_by === "login" ) {
query = query.order( "u.login" );
} else {
query = query.order( "observations_count", false );
