search

rockdai / sql-bricks

Transparent, Schemaless SQL Generation
http://csnw.github.io/sql-bricks
MIT License
213 stars 25 forks source link

Composite "WHERE IN" Support #72

Closed nickschwab closed 9 years ago

nickschwab commented 9 years ago

Support for composite "IN" conditions where two or more fields are specified along with a multidimensional array or array of objects.

e.g.

sql.in("(col1,col2)", [[1,2],[3,4]]);

Currently invalid example result: SELECT * FROM beacons WHERE (major, minor) IN ('64404,20884','47805,16497')

Expected example result: SELECT * FROM beacons WHERE (major, minor) IN (('64404','20884'),('47805','16497'))

Unsure if it's explicitly part of the SQL-92 standard.

Originated on Twitter: https://twitter.com/nickschwab/status/629024436532944896

Current work-around (after validating the multidimensional array): sql.in("(major, minor)", sql(myMultidimensionalArray.map(function(pair, index) {return '(' + pair[0] + ',' + pair[1] + ')';}).join(',')))

See also: http://stackoverflow.com/questions/6672665/doing-a-where-in-on-multiple-columns-in-postgresql

prust commented 9 years ago

hey @nickschwab, sorry for the delay here. I haven't been able to find anything about this in the SQL-92 spec, so I'm guessing it's a postgres-specific extension & should go in https://github.com/Suor/sql-bricks-postgres.

nickschwab commented 9 years ago

Bummer, thanks for checking into it @prust, I couldn't find any mention of it in the SQL-92 Standards Document either (really hard to search for such a generic term in a massive document lol). I'll create an issue under the sql-bricks-postgres project!