Re: [sqlite] Feature request: dynamically referenced bind parameters
Oooo this is really neat. Thanks! > On Mar 27, 2019, at 5:12 PM, Richard Hipp wrote: > > See https://www.sqlite.org/carray.html > > -- > D. Richard Hipp > d...@sqlite.org > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: dynamically referenced bind parameters
See https://www.sqlite.org/carray.html -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: dynamically referenced bind parameters
Yes, but the problem is that I need to also retrieve the articles themselves. If I were to embed the articles query inside the staff query (as you’ve shown), the database would have to execute the article query twice. > On Mar 27, 2019, at 4:42 PM, Keith Medcalf wrote: > > > You mean something like this: > > SELECT staff.* FROM staff, contributions > WHERE contributions.staff = staff.email > AND contributions.article IN (SELECT id FROM articles > WHERE publish_date <= CURRENT_TIMESTAMP > ORDER BY publish_date DESC LIMIT ?); > > --- > The fact that there's a Highway to Hell but only a Stairway to Heaven says a > lot about anticipated traffic volume. > >> -Original Message- >> From: sqlite-users [mailto:sqlite-users- >> boun...@mailinglists.sqlite.org] On Behalf Of Joshua Thomas Wise >> Sent: Wednesday, 27 March, 2019 14:22 >> To: SQLite mailing list >> Subject: [sqlite] Feature request: dynamically referenced bind >> parameters >> >> I’ve commonly encountered cases where I have a many-to-many >> relationship, and I would like to retrieve those relationships in a >> single query. >> >> For example: >> >> CREATE TABLE staff ( >> email TEXT PRIMARY KEY, >> name TEXT >> ); >> CREATE TABLE articles ( >> id INTEGER PRIMARY KEY, >> title TEXT, >> body TEXT, >> publish_date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, >> ); >> CREATE TABLE contributions( >> article INTEGER REFERENCES articles(id), >> staff TEXT REFERENCES staff(email), >> PRIMARY KEY(article, staff), >> ); >> >> First, I select the N most recently published articles: >> >> SELECT * FROM articles >> WHERE publish_date <= CURRENT_TIMESTAMP >> ORDER BY publish_date DESC LIMIT ?; >> >> Then, I’ll build a query like this to retrieve the staff that are >> responsible for writing those articles: >> >> SELECT staff.* FROM staff, contributions >> WHERE contributions.staff = staff.email >> AND contributions.article IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); -- >> assuming N was 12 in the first query >> >> However, I need to prepare a new statement every time I do this, >> depending on the value of N in the first query. >> >> SQLite3 already allows us to create a large number of bind parameters >> without explicitly declaring each one, via ?999 syntax. Now, if we >> had the ability to reference those bind parameters dynamically, the >> second query above could be something like this: >> >> WITH ids(id, n) AS ( >> SELECT param(1), 1 >> UNION ALL >> SELECT param(n + 1), n + 1 FROM ids WHERE n < >> param_count()) >> SELECT DISTINCT ids.id, staff.* FROM staff, contributions, ids >> WHERE contributions.staff = staff.email >> AND contributions.article = ids.id; >> >> >> >> >> ___ >> sqlite-users mailing list >> sqlite-users@mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Feature request: dynamically referenced bind parameters
You mean something like this: SELECT staff.* FROM staff, contributions WHERE contributions.staff = staff.email AND contributions.article IN (SELECT id FROM articles WHERE publish_date <= CURRENT_TIMESTAMP ORDER BY publish_date DESC LIMIT ?); --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users- >boun...@mailinglists.sqlite.org] On Behalf Of Joshua Thomas Wise >Sent: Wednesday, 27 March, 2019 14:22 >To: SQLite mailing list >Subject: [sqlite] Feature request: dynamically referenced bind >parameters > >I’ve commonly encountered cases where I have a many-to-many >relationship, and I would like to retrieve those relationships in a >single query. > >For example: > >CREATE TABLE staff ( > email TEXT PRIMARY KEY, > name TEXT >); >CREATE TABLE articles ( > id INTEGER PRIMARY KEY, > title TEXT, > body TEXT, > publish_date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, >); >CREATE TABLE contributions( > article INTEGER REFERENCES articles(id), > staff TEXT REFERENCES staff(email), > PRIMARY KEY(article, staff), >); > >First, I select the N most recently published articles: > >SELECT * FROM articles >WHERE publish_date <= CURRENT_TIMESTAMP >ORDER BY publish_date DESC LIMIT ?; > >Then, I’ll build a query like this to retrieve the staff that are >responsible for writing those articles: > >SELECT staff.* FROM staff, contributions >WHERE contributions.staff = staff.email >AND contributions.article IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); -- >assuming N was 12 in the first query > >However, I need to prepare a new statement every time I do this, >depending on the value of N in the first query. > >SQLite3 already allows us to create a large number of bind parameters >without explicitly declaring each one, via ?999 syntax. Now, if we >had the ability to reference those bind parameters dynamically, the >second query above could be something like this: > > WITH ids(id, n) AS ( > SELECT param(1), 1 > UNION ALL > SELECT param(n + 1), n + 1 FROM ids WHERE n < >param_count()) > SELECT DISTINCT ids.id, staff.* FROM staff, contributions, ids > WHERE contributions.staff = staff.email > AND contributions.article = ids.id; > > > > >___ >sqlite-users mailing list >sqlite-users@mailinglists.sqlite.org >http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Feature request: dynamically referenced bind parameters
I’ve commonly encountered cases where I have a many-to-many relationship, and I would like to retrieve those relationships in a single query. For example: CREATE TABLE staff ( email TEXT PRIMARY KEY, name TEXT ); CREATE TABLE articles ( id INTEGER PRIMARY KEY, title TEXT, body TEXT, publish_date TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, ); CREATE TABLE contributions( article INTEGER REFERENCES articles(id), staff TEXT REFERENCES staff(email), PRIMARY KEY(article, staff), ); First, I select the N most recently published articles: SELECT * FROM articles WHERE publish_date <= CURRENT_TIMESTAMP ORDER BY publish_date DESC LIMIT ?; Then, I’ll build a query like this to retrieve the staff that are responsible for writing those articles: SELECT staff.* FROM staff, contributions WHERE contributions.staff = staff.email AND contributions.article IN (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?); -- assuming N was 12 in the first query However, I need to prepare a new statement every time I do this, depending on the value of N in the first query. SQLite3 already allows us to create a large number of bind parameters without explicitly declaring each one, via ?999 syntax. Now, if we had the ability to reference those bind parameters dynamically, the second query above could be something like this: WITH ids(id, n) AS ( SELECT param(1), 1 UNION ALL SELECT param(n + 1), n + 1 FROM ids WHERE n < param_count()) SELECT DISTINCT ids.id, staff.* FROM staff, contributions, ids WHERE contributions.staff = staff.email AND contributions.article = ids.id; ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
