search

yahoo / squidb

SquiDB is a SQLite database library for Android and iOS
https://github.com/yahoo/squidb/wiki
Apache License 2.0
1.31k stars 132 forks source link

Negating an "In" clause #116

Closed matthew-reilly closed 8 years ago

matthew-reilly commented 8 years ago

How would one construct a query like the following? Doesn't seem to be currently possible. 

SELECT *
FROM employees
WHERE department NOT IN ('Accounting', 'Finance', 'HR');
sbosley commented 8 years ago

Easy! One of two ways:

Criterion.not(Employee.DEPARTMENT.in("Accounting", "Finance", "HR"));
// or...
Employee.DEPARTMENT.in("Accounting", "Finance", "HR").negate();

Hope that helps!

matthew-reilly commented 8 years ago

Ah, stupid simple. Gracias. :shipit:

sbosley commented 8 years ago

No problem, happy to help! In case you haven't seen them yet, for future reference most of the common use cases for the query builder are documented on this wiki page, or for more advanced things you can check out this page. They may not cover 100% of the possible capabilities of Query (which is pretty extensive), but they're a good reference for most use cases.

matthew-reilly commented 8 years ago

It would seem that generates a query that looks like this:

( NOT (employee.department IN ('Accounting','Finance','HR')))

Which, from what I know about sqlite, isn't valid? Shouldn't it be NOT IN?

sbosley commented 8 years ago

"NOT IN" is indeed acceptable SQL, but the form it generates is also valid and equivalent -- SQLite describes "NOT" as a valid unary prefix operator on this page. I just tested in a SQLite shell for sanity, and it returns equivalent results in all my test cases.

I think that SQLite will always choose equivalent query plans for these two forms. We can consider an enhancement to make the generated SQL more readable though, for debugging purposes, and/or add a notIn(...) method to Field as a counterpart to in(...).

matthew-reilly commented 8 years ago

Ah, will trust you then! Will continue my testing.

A notIn would be helpful, perhaps if I have some extra time I'll make a PR.

Cheers.