[odb-users] Representation of INTERSECT SQLite Query

[Boris Kolpackov]

Hi Eric,

Sum, Eric B <eric.b.sum at lmco.com> writes:

> 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>;
> 
> [...]
> 
> However, I am not sure how to provide the lengthIn, widthIn, and maxBoxes 
> variables through the ? parameters.

ODB does not use placeholders for parameter binding. Instead, you bind
variables directly. Because your query have two WHERE clauses, the only
way to handle this case is by providing the complete query at runtime:

#pragma db view
struct BoxView
{
  int id;
  int height;
  int weight;
};

typedef odb::query<BoxView> query;

int length = ...;
int width = ...;
int limit = ...;

db.query<BoxView> ("SELECT id, height, weight from Box "
                   "WHERE length =" + query::_val (length) + 
                   "INTERSECT SELECT id, height, weight from Box "
                   "WHERE width =" + query::_val (width) + 
                   "ORDER BY height DESC LIMIT" + query::_val (limit));

If you upgrade to 2.2.0, then you could also use prepared queries to
tidy this up.


> 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).

>From your use of the word "possible" I reckon you don't have any evidence
that it is actually faster ;-). I also don't see a reason why it would.
On the other hand, adding an index for length and width should definitely
help (#pragma db index is available since 2.1.0):

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

  #pragma db index
  int length;

  #pragma db index
  int width;

  int height;
  int weight;
};

Boris