[odb-users] Representation of INTERSECT SQLite Query

[Sum, Eric B]

Hi,

I am using odb version 2.0 and SQLite version 3.7.13.  I am trying to represent a SQLite query in odb somehow, but I am not sure how to.  Here is an example of the type of query I would like to write:

Given the following object:

#pragma db object
struct Box {
#pragma db id
int id;

int length;
int width;
int height;
int weight;
};

Raw SQL query given the parameters lengthIn, widthIn, and maxBoxes to bind to the query:

SELECT id, height, weight from Box WHERE length = <lengthIn> INTERSECT
SELECT id, height, weight from Box WHERE width = <widthIn>
ORDER BY height DESC LIMIT <maxBoxes>;

My guess was to use a native view along the lines of

#pragma db view query ( "SELECT id, height, weight from Box WHERE length = ? INTERSECT " \
"SELECT id, height, weight from Box WHERE width = ? "\
"ORDER BY height DESC LIMIT ?")
struct BoxView {
#pragma type ("INTEGER")
int id;

#pragma type ("INTEGER")
int height;

#pragma type ("INTEGER")
int weight;
};

However, I am not sure how to provide the lengthIn, widthIn, and maxBoxes variables through the ? parameters.
I did not see an example in the manual of how to handle more than one (?) parameter, but I may have missed it.

Is this view correct?  If so, how do I perform the actual query with the three variables needed.  Any help/suggestions would be greatly appreciated.

Also, the reason I want to do an INTERSECT rather than "WHERE length = <lengthIn> AND width = <widthIn>"
is for possible performance improvements (the AND query is too slow currently).

Thanks so much,
Eric