node-sql-query build complex query

Question

I use Node.js with sails.js

I'm trying to build a query which have a sub-query inside like this:

SELECT 
    `id`, `col1`, `col2`, `col3`,
    (SELECT COUNT(*) FROM `tablename` 
        WHERE (`col1` = 'someValue') AND (`col2` = `t`.`col2`)) AS `total` 
FROM `tablename` `t` 
WHERE `col1` = 'value2' AND `col3` = 'value3' 
ORDER BY `total` ASC

I know that I can write in my model:

//./api/models/tablename.js
module.exports = {
    ...
    functionName() {
        this.query(aboveSQLQuery)
            .then(found => {
                doSomething();
            })
            .catch(err => {
                handleError(err);
            });
    }
}

But I don't want to use that, so I found the node-sql-query module so I can build query like this:

let
    sql = require('sql-query'),
    sqlQuery = sql.Query(),
    sqlSelect = sqlQuery.select(),
    subQuery = sqlSelect
        .from('tablename')
        .where({col1: "someValue"}, {__sql: [['col2 = t.col2']]})
        .count()
        .build(),

    /* subQuery = "SELECT COUNT(*) FROM `tablename` 
                   WHERE (`col1` = 'someValue') AND (`col2` = `t`.`col2`)"
    */

    masterQuery = sqlSelect
        .from('tablename')
        .select(['id', 'col1', 'col2', 'col3', {a: "total", sql: subQuery}])
        .where({col1: "value2", col3: "value3"})
        .order('total', 'A')
        .build();

    /* masterQuery = "SELECT `id`, `col1`, `col2`, `col3`, 
                          (SELECT COUNT(*) FROM `tablename` 
                          WHERE (`col1` = 'someValue') AND (`col2` = `t`.`col2`)) 
                          AS `total` 
                      FROM `tablename` 
                      WHERE `col1` = 'value2' AND `col3` = 'value3' 
                      ORDER BY `total` ASC"
    */

The masterQuery not working as expected, but if I change the 2nd FROM from

FROM `tablename`

to

FROM `tablename` `t`

it works.

Any help will be appreciated

Ziki · Accepted Answer · 2016-02-04 11:03:10Z

2

I just found knex, and manage to accomplish this:

let
    knex = require('knex')({
        client: "mysql"
    }),
    subQuery, masterQuery;

subQuery = knex('tablename')
    .count()
    .where({col1: "someValue"})
    .andWhereRaw('`col2` = `t`.`col2`')
    .select()
    .from('tablename')
    .as('total');

masterQuery = knex('tablename')
    .select(['id', 'col1', 'col2', 'col3', subQuery])
    .from(knex.raw('`tablename` `t`'))
    .where({col1: "value2", location: "value3"})
    .orderBy('total', 'asc')
    .limit(1)
    .toString();

Then evaluating the query using waterline with:

this.query(masterQuery, (err, found) => {
    doSomething();
});

answered Feb 4, 2016 at 11:03

Ziki

1,4412 gold badges14 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alexander Ostapenko · Accepted Answer · 2016-02-28 17:38:10Z

0

Waterline use node-mysql package, so you can perform any pure sql queries for your models, like:

Model.query(query, params, callback);

answered Feb 28, 2016 at 17:38

Alexander Ostapenko

11 bronze badge

Collectives™ on Stack Overflow

node-sql-query build complex query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related