Support for Variable Length Queries

[neuralmer]

I need to allow my users to optionally specify several different attributes to match on and I would like to construct a query that varies in length. I present the following as an example of the idea, though this won't compile as written:

class SKU {
    int id;
    std::string sku; // This is fine.
    std::string maker;
    std::string product;
    int intval;
    std::string flavin;
};

void doSomething(std::string *maker, std::string *product) {
    Storage storage = constructStorage(filename);

    auto conditional;
    if (maker)
        conditional = conditional and (c(&SKU::maker) = *maker);
    if (product)
        conditional = conditional and (c(&SKU::product) = *product);
    auto matchingSKUs = storage.select(&SKU::sku, where(conditional));
    ...
}

I have tried compiling something like this and got errors when trying to assign conditional = conditional and ... because of mismatched template types.

For ordered values like a date or an integer, working around this is straightforward: compare the column with a value that is either desired value or one that will always match if no value was specified. E.g.:

int comparableValue = value ? *value : int_max;
...(where(lesser_than(c(&SKU::intval), comparableValue)));

For strings I could do something like:

std::string comparableValue = maker ? *maker : "%";
...(where(like(&SKU::intval, comparableValue)));

But this introduces some interpretation of *maker that would produce undesirable results, e.g. if the desired value to match on had characters that were unintentionally treated as special by like()/LIKE. These characters could be escaped, etc., but it would be simpler and potentially more efficient to avoid unnecessary clauses in the first place.

Is there a way to do this with sqlite_orm? I have spent some time looking through the examples, but haven't found something like this.

[fnc12]

Hi. You can do it easily and you don't need to have dynamic SQL string (I mean it). Just add a bool flag for every option and set it to true when you want to avoid it and set it to false to enable this condition. Example #671 (comment)

[neuralmer]

I will give that a shot. Thanks!

[fnc12]

@neuralmer did it work?

[neuralmer]

Yes, the approach you describe works, and that is what I plan to use in my code.

No, it doesn't allow construction of a query of non-static length: the "shape" of the query must be defined at compile time.

I would like you to consider adding documentation about how to do that conditional bit, if that is not described in the readme (I didn't see it, but I know the readme is already long). There or in the wiki. In that case maybe this is a "documentation bug". Otherwise I would consider this issue closed.

When looking for an ORM and when requirements are still uncertain, it is hard to accept that a query with a shape that is fixed at compile time will be sufficient. It almost certainly will work for me now and in the future. Through the design of this api, sql injection risk can be avoided. Coming out and saying upfront that queries are fixed at compile-time, however, might drive some people away from this library and toward less secure solutions.

Thanks

[fnc12]

dynamic queries are overrated actually and they reduce performance. Static query can be compiled once to prepared statement and executed bunch of times

[fnc12]

@neuralmer did it work?

[fnc12]

I am assuming @neuralmer 's code works perfectly. BTW one more details about static length queries: it is very important feature cause it allows static binding of any values to prepared statement when using sqlite_orm. I mean if you statement has let's say three bindable parameters you may accidentally write 'bind parameter number 4' and you'll get compilation error with sqlite_orm. With raw SQLite or any other ORMs you will get only run-time error.