Is the "IN" operator supported? I don't see an example

[BrannonKing]

I wanted do some queries like

db << "SELECT name FROM nodes WHERE name IN ?" << myList;

Is this supported? Do I need parentheses around the '?' ?

As a further request, would it be possible to define a custom "table-valued" method such that you could do something like this:

db.define("split_on_commas", ...);
db << "SELECT name FROM nodes WHERE name IN split_on_commas(?)" << "a,b,c";

[aminroosta]

Unfortunately i think the answer to both is no.

The first case, looking at sqlite bind_value* docs, seems impossible to implement.

db << "SELECT name FROM nodes WHERE name IN ?" << myList;

---

I'm not sure about the second approach, @zauguin knows more than me. Maybe something like this would work? however not a great idea, because of extra memory allocations.

db.define("is_one_of", [](std::string value, std::string values) {
     // split the comma separated "values" and check if it contains value
     return true;
});
db << "SELECT name FROM nodes WHERE is_one_of(name, ?)" << "a,b,c";

[BrannonKing]

Thanks for the response. I have changed my code to do a recursive subselect. I had thought this in substitution was possible because sqlite_orm has something for the in operator. However, I can see that it would require a higher-level construct. You would have to detect the "in ?" scenario and replace it with "(?,?,...)" before the SQL was parsed. I don't know of a performant way to achieve that.

[zauguin]

@BrannonKing About defining a function like this: It would have to be a table-valued function, which in SQLite has to be implemented using virtual tables. Currently sqlite_modern_cpp does not support defining virtual tables, but we could add limited support to at least support table-valued functions... I will have to think about it.

Anyway are you aware that you could use LIKE instead? Try

db << "SELECT name FROM nodes WHERE ',' || ? || ',' LIKE '%,' || name || ',%';" << "a,b,c";

[zauguin]

@BrannonKing I made some experiments with virtual tables. In https://gist.github.com/zauguin/7e327d9b5edf5a5002382c933308913c Iyou can find a minimal C++ mapping of the interface and an implementation of your split_on_commas function.

I'm not sure how we can make an easier to use interface, especially BestIndex feels very unnatural in C++ but it also provides too many features to fit into a more traditional form.

Disclaimer: I have written the gist after not writing a single line of C++ for a year, so it almost certainly could be improved a lot...

[zauguin]

@aminroosta Do we want to add support for virtual tables to the library? It's kind of special, has a peculiar API and probably isn't used a lot, but it would expose additional parts of SQLite API and definitely simplifies the C interface.

[aminroosta]

@zauguin

The only c++ i wrote in 2019 was to fix the visual studio compile issue we had. Now I'm doing rust 🦀 !

Anyways, honestly i am neutral on this. I think, the majority of users won't use the virtual tables. If you decide to implement them, you'll be the only one capable of maintaining it.

At the same time, the gist above has the bulk of the job done... I don't want to bury it in an issue.

I think we could copy it to the repo, and mention it in the readme ... as an example of how one could support virtual tables.

OR, if you decide to work on it, we could add it in a new header, which is optional to include, and adds the vtables api.