OR from array

[EmileSpecs]

Hi,

Works great, just one little addition that would make life much easier... Would it be difficult, given:

select().from('table').where({ this: 'test', that: ['test1', 'test2']});

to generate:

SELECT * FROM table WHERE this='test' AND (that='test1' OR that='test2')

I don't see how it's possible to use "or" when using dynamic data such as that.
If there is a way that I could do that without any changes please help!

Thanks!

[prust]

@EmileSpecs: There are two ways to do this (currently) with SQLBricks: (1) switch to an IN statement or (2) Use a .map() to convert the array to a set of eq() ("equals") criteria.

select().from('table').where({ this: 'test' }).and($in('that', ['test1', 'test2']));
// SELECT * FROM "table" WHERE this = 'test' AND that IN ('test1', 'test2')
select().from('table').where({ this: 'test' }).and(or(['test1', 'test2'].map(function(val) { return eq('that', val); })));
// SELECT * FROM "table" WHERE this = 'test' AND (that = 'test1' OR that = 'test2')

Before 0.9.0, or() only took separate arguments, it didn't take an array. I fixed this just now to make it more flexible & more consistent with SQLBricks' other APIs.

The idea of { that: ['test1', 'test2'] } implicitly creating an OR isn't a bad one, though I would prefer IN... Certainly the current behavior (serializing to a comma-delimited string) isn't expected -- I'm a little surprised the fix for #12 didn't fix this, I'll have to look into it.

[prust]

@EmileSpecs: Ah, @RandyPearson just reminded me that the { that: ['test1', 'test2'] } syntax more directly maps to SQL-95 array syntax. In fact, I just merged #32 so your example now generates this SQL:

select().from('table').where({ this: 'test', that: ['test1', 'test2']});
// SELECT * FROM "table" WHERE this = 'test' AND that = {'test1', 'test2'}

[EmileSpecs]

@prust, thanks for the help, I'll try and see what I can figure out.

Since I'm just using a single handler per statement type (SELECT, INSERT...) for all my basic CRUD operations and throwing an object at it, it's dynamic and does not have predetermined structure.
That's why I'm using SQLBricks actually, I couldn't find anything else I could just use objects with.

So to use any of the initial suggestions isn't quite trivial. I'm using MYSQL so this syntax:

that = {'test1', 'test2'}

...unfortunately doesn't work. That would have been nice!

It would still be a really nice feature to OR array values together or automatically construct the IN part. Like you said it's not a bad idea, and I think logically implicitly creating OR or IN for array values makes sense. An array as a value for a field can't really mean anything else...

Is this a non-trivial feature to implement?

[prust]

@EmileSpecs:

Is this a non-trivial feature to implement?

No, it's trivial -- I'm just not sure it's unambiguous or that it wouldn't conflict with any legitimate uses of SQL arrays.

For starters, I would encourage layering it on top of sql-bricks like this:

function arraysToOrs(criteria) {
  var where = and();
  for (var col in criteria) {
    var val = criteria[col];
    var expr;
    if (_.isArray(val))
      expr = or(val.map(function(val) { return eq(col, val); }));
    else
      expr = eq(col, val);
    where.expressions.push(expr);
  }
  return where;
}

Then you can call .where(arraysToOrs(...)) like this:

select().from('table').where(arraysToOrs({ this: 'test', that: ['test1', 'test2']}));
// SELECT * FROM "table" WHERE this = 'test' AND (that = 'test1' OR that = 'test2')

Or you can make this the default where() behavior for select, insert, update and delete, like this:

var stmts = ['select', 'update', 'insert', 'delete'];
stmts.forEach(function(stmt) {
  var proto = sql[stmt].prototype;
  var orig = proto.where;
  proto.where = function(criteria) {
    return orig.call(this, arraysToOrs(criteria));
  };
});

I went ahead and added a couple of tests to the suite to ensure that this customization works and so that we'll know if it ever breaks: 89ff9d9. Note that arraysToOrs() depends on and(), or() and eq() being globals, if they're not you can just replace them with sql.and(), sql.or() and sql.eq().

Also note that in order to dynamically add to where (an and() expression), we had to break encapsulation and append to where.expressions. This isn't ideal, I've been meaning to expose a public way to dynamically add to and/or expressions, but haven't gotten around to it yet (#10).

Let me know if this works for you!

[prust]

@EmileSpecs: Just realized that INSERT doesn't have a WHERE clause, so the above code should just be var stmts = ['select', 'update', 'delete'];

[EmileSpecs]

@prust: Fantastic! Thanks for your help, that function is working perfectly on the few tests I've written for my code.

I did have to add:

if (_.isEmpty(criteria)) return null;

Added that as first line of function otherwise if there were no WHERE criteria it would produce:

SELECT * FROM "table" WHERE

Also wondering while we're at it, the double quotes around the table name breaks the queries, MYSQL doesn't like double quotes. I fixed that with:

select().from(sql(operation.table))

Don't know if that's correct or intended use but it took the quotes away...

Thanks again for the help!