dresende / node-sql-query Goto Github PK

I don't know if it's correct, but when it's impossible to create IS NOT NULL condition (just <> NULL, which is always false). Updating lib/Where.js on line 95 helped:

case "ne" : op = where.w[k].val === null?"IS NOT":"<>"; break;

One of my postgresql table column is of type json;

{
   id: 4350,
   name: "hello world",
   setting: { notification: 'false', language: 'malay'}
 }

Passing the above value to the code

John.save(data, function(err){
})

Will result in crash

//console.log('value', val, typeof val)
val value 4350 string
val value { notification: 'false', language: 'malay' } object
/home/paragasu/abby/dashboard/node_modules/sql-query/lib/Dialects/postgresql.js:67
    return "'" + val.replace(/\'/g, "''") + "'";
                     ^

TypeError: val.replace is not a function

This syntax is not supported. https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html

It would be nice to add SQL_CALC_FOUND_ROWS to select queries in order to subsequently call SELECT FOUND_ROWS(). I use this for pagination and it would be really nice to add to the mix! What do you think?

I can not fine document for OR WHERE. How can I use it.

const sql = sqlQuery.select()
      .from('machine_log')
      .select('when', 'machine_id', 'event')
      .from('machines', 'machine_id ', 'machine_log', 'machine_id')
      .select('name')
      .order('when', 'Z')
      .limit(limit)
      .build();

And it produce this SQL that so wrong

SELECT `t1`.`when`, `t1`.`machine_id`, `t1`.`event`, `t2`.`name` FROM `machine_log` `t1` JOIN `machines` `t2` ON `t2`.`machines` = `t1`.`machine_id` ORDER BY `when` DESC LIMIT 10

ON t2.machines must be ON t2.machine_id
like this

SELECT `t1`.`when`, `t1`.`machine_id`, `t1`.`event`, `t2`.`name` FROM `machine_log` `t1` JOIN `machines` `t2` ON `t2`.`machine_id` = `t1`.`machine_id` ORDER BY `when` DESC LIMIT 10

Would you mind adding those? These are quite handy for erasing certain ranges of data in logs and such.

http://dev.mysql.com/doc/refman/5.0/en/delete.html

Having in select clause appends before WHERE clause that cause an synax error. And it inserts column name only, but it could be an expression too.

Hi,

I can see activity in the changelog, but also some unmerged PRs.
Also, orm2 is no longer maintained.

So... are you actively maintaining this repository? It would be perfect for me but I am likely to send PRs etc.

Please let us know!

Merc.

Feature request: I want to count items grouped by a DATETIME field. To group on the day I am doing this in sqlite3:

SELECT COUNT(id) AS sum, strftime("%Y-%m-%d", date) AS year_month_date FROM myTable GROUP BY strftime("%Y-%m-%d", date);

I would like to create an UPDATE query where I update a column using a function like so

UPDATE table SET col=GETDATE();

Is that possible already? I checked the tests and the code, but I couldn't find anything. It seems trivial to have (and to implement).

An issue was raised in the node-orm2 project where the OP wants to be able to use the server side default column definitions. For instance, in the following table:

CREATE TABLE table1(
    id SERIAL NOT NULL,
    stuff TEXT,
    CONSTRAINT table1_pk PRIMARY KEY (id)
);

He wants to be able to use the series attached to the id key. This is possible in javascript (unlike other languages) by utilizing the javascript undefined object. This would require that undefined and null be treated differently in the PostgreSQL Dialect.

Once this is done, a defaultValue property of undefined in the node-orm2 model would use the server defined default.

Like I said in Topic
How to get result from calculateFoundRows with "node-sql-query"?

Trying to insert a model with a Date type in Postgres results in the following error:

TypeError: Object Tue May 21 2013 13:12:53 GMT+0200 (CEST) has no method 'replace'
    at Object.exports.escapeVal (/Users/mike/dev/search-analytics/node_modules/orm/node_modules/sql-query/lib/Dialects/postgresql.js:35:19)
    at Object.InsertQuery.build (/Users/mike/dev/search-analytics/node_modules/orm/node_modules/sql-query/lib/Insert.js:26:24)
    at Driver.insert (/Users/mike/dev/search-analytics/node_modules/orm/lib/Drivers/DML/postgres.js:194:21)
    at /Users/mike/dev/search-analytics/node_modules/orm/lib/Instance.js:120:17
    at checkNextValidation (/Users/mike/dev/search-analytics/node_modules/orm/lib/Instance.js:42:12)
    at Instance.handleValidations (/Users/mike/dev/search-analytics/node_modules/orm/lib/Instance.js:66:10)
    at Instance.saveInstance (/Users/mike/dev/search-analytics/node_modules/orm/lib/Instance.js:95:3)
    at Object.Instance.Object.defineProperty.value (/Users/mike/dev/search-analytics/node_modules/orm/lib/Instance.js:336:7)
    at createNext (/Users/mike/dev/search-analytics/node_modules/orm/lib/Model.js:384:19)
    at Function.Model.model.create (/Users/mike/dev/search-analytics/node_modules/orm/lib/Model.js:413:3)

Looking in the source code, it seems that lib/Dialects/postrgresql.js method escapeVal doesn't handle Date objects properly yet.

Modifying the switch statement to look more like this:

switch (typeof val) {
        case "number":
            return val;
        case "boolean":
            return val ? "true" : "false";
        case "object": 
            if (val instanceof Date)
                return "'" + dateToString(val) + "'";
    }

seems to work - but I had to steal dateToString method from the node pg/lib/utils.js. This works but probably isn't the nicest solution so I won't send a PR.

Ideally this library would be a true dependency maybe and you could re-use their methods directly? (doesn't seem like your ORM library should be doing the escaping itself, that seems a little like the job of the core driver maybe?)

You can build a query SELECT * FROM table1 WHERE col IN (1, 2, 3), but there is no option for the reverse. I would appreciate an comparator to query SELECT * FROM table1 WHERE col NOT IN (1, 2, 3).

why about local time think to +00:00 timezone and not is server local timezone in no-mysql sql?

I thought it was missing in the documentation, but isn't the OR statement missing?

So you can build a query like this
WHERE (col_1 !=1 AND col_2 !=12) OR (col_1 !=1 AND col_2 !=13)

Or even just
WHERE (col_1 !=1) OR (col_2 !=13)

having a strange one with nodejs and express and just displaying the output as string

thedb.sql = require('sql-query');
thedb.sqlquery = thedb.sql.Query('sqlite');
thedb.sqlupdate = thedb.sqlquery.update();
var query = thedb.sqlupdate.into("metrics").set({ metrics_name: req.body.name }).where({ metrics_id: req.body.id }).build();

output:

UPDATE `metrics` SET `metrics_name` = 'my metric name' WHERE `metrics_id` = '24'

but refresh webpage

output:

UPDATE `metrics` SET `metrics_name` = 'my metric name' WHERE (`metrics_id` = '24') AND (`metrics_id` = '24')

why is it adding to the where instead of clearing it and starting a fresh?

Hello,

I have to make a request like " SELECT * FROM [database].[table] ". But I don't understand how to create the query.
What function should I use to make such a request ?

Thx

Would you mind adding column alias support? Something similar to what you have in functions. Basically I would like to be able to create queries as such:

SELECT `name` AS `title` FROM `people` 

How to Select .. Where with OR operator?

Using the following as part of Select
.fun('dbo.fnBalance', [80, null, null], 'balance')
returns this error Cannot read property 'type' of null

To prevent this, add the following lines just before if (typeof el.type == "function") { at line 261 in lib/Select.js

if (!el) {
    return Dialect.escapeVal(el);
}

This builds the correct sql which is
DBO.FNBALANCE(80, NULL, NULL)

I don't bother forking for something small as this, so hopefully you're willing to get this in?

Just like this:
common.Select().from('table1')
.from('table2', 'id2', 'table1', 'id1', 'LEFT').fun('AVG', 'col').build();

"SELECT AVG(t2.col) FROM table1 t1
LEFT JOIN table2 t2 ON t2.id2 = t1.id1"

thanks

There is no having method in select query prototype

Let's join forces guys!

I'd love some help on any/all of these projects:

• https://github.com/balderdashy/waterline-sequel (used by https://github.com/balderdashy/sails)
• https://github.com/tgriesser/knex
• https://github.com/brianc/node-sql