search

dmfay / massive-js

A data mapper for Node.js and PostgreSQL.
2.49k stars 158 forks source link

[feature request] support for array intersections for complex types #608

Closed AmazingTurtle closed 6 years ago

AmazingTurtle commented 6 years ago

See here https://stackoverflow.com/questions/50899547/postgresql-array-intersections-on-jsonb

dmfay commented 6 years ago

This would definitely be cool, but I don't think it's practical. This isn't a simple predicate operation as with the simple array &&. You have to join and process the array elements themselves in order to detect an intersection, and right now the query builder doesn't account for joins at all -- the idea being it's better to formalize a complex query with a view, script, or function, and decompose the resultset into something friendlier. And that's without getting into wrapping it back up to return the original matching record, behavior with or, and so forth.

If you don't need absolute maximum efficiency, you can check containment with a criteria object like {studios: [{name: 'Warner'}]}, and use the or key to do multiple tests. Or just use a script file! The query builder can't cover every eventuality, and this one's a lot more complicated than it might seem at first blush.