issue_comments

Table actions

6 rows where issue = 737476423

✎ View and edit SQL

This data as json, CSV (advanced)

Suggested facets: created_at (date), updated_at (date)

id ▼ html_url issue_url node_id user created_at updated_at author_association body reactions issue performed_via_github_app
722849539 https://github.com/simonw/sqlite-utils/issues/198#issuecomment-722849539 https://api.github.com/repos/simonw/sqlite-utils/issues/198 MDEyOklzc3VlQ29tbWVudDcyMjg0OTUzOQ== simonw 9599 2020-11-06T05:39:17Z 2020-11-06T05:39:17Z OWNER I'd have to copy almost all of the code in https://github.com/simonw/sqlite-fts4/blob/master/sqlite_fts4/__init__.py so I think I will add it as a dependency instead. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support order by relevance against FTS4 737476423  
722852262 https://github.com/simonw/sqlite-utils/issues/198#issuecomment-722852262 https://api.github.com/repos/simonw/sqlite-utils/issues/198 MDEyOklzc3VlQ29tbWVudDcyMjg1MjI2Mg== simonw 9599 2020-11-06T05:41:58Z 2020-11-06T05:41:58Z OWNER Example query (from the tests): ```sql select c0, c1, rank_bm25(matchinfo(search, 'pcnalx')) as bm25 from search where search match ? ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support order by relevance against FTS4 737476423  
722895825 https://github.com/simonw/sqlite-utils/issues/198#issuecomment-722895825 https://api.github.com/repos/simonw/sqlite-utils/issues/198 MDEyOklzc3VlQ29tbWVudDcyMjg5NTgyNQ== simonw 9599 2020-11-06T06:29:17Z 2020-11-06T06:29:17Z OWNER I released a 1.0 (and 1.0.1) version of https://github.com/simonw/sqlite-fts4 {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support order by relevance against FTS4 737476423  
723143633 https://github.com/simonw/sqlite-utils/issues/198#issuecomment-723143633 https://api.github.com/repos/simonw/sqlite-utils/issues/198 MDEyOklzc3VlQ29tbWVudDcyMzE0MzYzMw== simonw 9599 2020-11-06T15:33:12Z 2020-11-06T15:33:12Z OWNER Here's the FTS5 query: ```sql with original as ( select rowid, * from [global-power-plants] ) select original.*, [global-power-plants_fts].rank as rank from [original] join [global-power-plants_fts] on [original].rowid = [global-power-plants_fts].rowid where [global-power-plants_fts] match :query order by rank desc limit 20 ``` The equivalent using `rank_bm25()` for FTS4 would be: ```sql with original as ( select rowid, * from [global-power-plants] ) select original.*, rank_bm25(matchinfo([global-power-plants_fts], 'pcnalx')) as rank from [original] join [global-power-plants_fts] on [original].rowid = [global-power-plants_fts].rowid where [global-power-plants_fts] match :query order by rank desc limit 20 ``` {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support order by relevance against FTS4 737476423  
723144893 https://github.com/simonw/sqlite-utils/issues/198#issuecomment-723144893 https://api.github.com/repos/simonw/sqlite-utils/issues/198 MDEyOklzc3VlQ29tbWVudDcyMzE0NDg5Mw== simonw 9599 2020-11-06T15:35:45Z 2020-11-06T15:35:45Z OWNER Here's a demo of that rank query. I had to sort by `rank` instead of `rank desc` - need to double-check that: https://datasette-sqlite-fts4.datasette.io/24ways-fts4?sql=with+original+as+(%0D%0A++++select%0D%0A++++++++rowid%2C%0D%0A++++++++*%0D%0A++++from+[articles]%0D%0A)%0D%0Aselect%0D%0A++++original.*%2C%0D%0A++++rank_bm25(matchinfo([articles_fts]%2C+%27pcnalx%27))+as+rank%0D%0Afrom%0D%0A++++[original]%0D%0A++++join+[articles_fts]+on+[original].rowid+%3D+[articles_fts].rowid%0D%0Awhere%0D%0A++++[articles_fts]+match+%3Aquery%0D%0Aorder+by%0D%0A++++rank%0D%0Alimit+20&query=jquery+maps {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support order by relevance against FTS4 737476423  
723145383 https://github.com/simonw/sqlite-utils/issues/198#issuecomment-723145383 https://api.github.com/repos/simonw/sqlite-utils/issues/198 MDEyOklzc3VlQ29tbWVudDcyMzE0NTM4Mw== simonw 9599 2020-11-06T15:36:47Z 2020-11-06T15:36:47Z OWNER Should I register the custom `rank_bm25` SQLite function for every connection, or should I register it against the connection just the first time the user attempts an FTS4 search? I think I'd rather register it only if it is needed. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Support order by relevance against FTS4 737476423  

Advanced export

JSON shape: default, array, newline-delimited, object

CSV options: 

CREATE TABLE [issue_comments] (
   [html_url] TEXT,
   [issue_url] TEXT,
   [id] INTEGER PRIMARY KEY,
   [node_id] TEXT,
   [user] INTEGER REFERENCES [users]([id]),
   [created_at] TEXT,
   [updated_at] TEXT,
   [author_association] TEXT,
   [body] TEXT,
   [reactions] TEXT,
   [issue] INTEGER REFERENCES [issues]([id])
, [performed_via_github_app] TEXT);
CREATE INDEX [idx_issue_comments_issue]
                ON [issue_comments] ([issue]);
CREATE INDEX [idx_issue_comments_user]
                ON [issue_comments] ([user]);