issue_comments

Table actions

4 rows where issue = 944870799

✎ 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
880278256 https://github.com/simonw/datasette/issues/1394#issuecomment-880278256 https://api.github.com/repos/simonw/datasette/issues/1394 MDEyOklzc3VlQ29tbWVudDg4MDI3ODI1Ng== simonw 9599 2021-07-14T23:35:18Z 2021-07-14T23:35:18Z OWNER The challenge here is that faceting doesn't currently modify the inner SQL at all - it wraps it so that it can work against any SQL statement (though Datasette itself does not yet take advantage of that ability, only offering faceting on table pages). So just removing the order by wouldn't be appropriate if the inner query looked something like this: ```sql select * from items order by created desc limit 100 ``` Since the intent there would be to return facet counts against only the most recent 100 items. In SQLite the `limit` has to come after the `order by` though, so the fix here could be as easy as using a regular expression to identify queries that end with `order by COLUMN (desc)?` and stripping off that clause. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Big performance boost on faceting: skip the inner order by 944870799  
880287483 https://github.com/simonw/datasette/issues/1394#issuecomment-880287483 https://api.github.com/repos/simonw/datasette/issues/1394 MDEyOklzc3VlQ29tbWVudDg4MDI4NzQ4Mw== simonw 9599 2021-07-15T00:01:47Z 2021-07-15T00:01:47Z OWNER I wrote this code: ```python _order_by_re = re.compile(r"(^.*) order by [a-zA-Z_][a-zA-Z0-9_]+( desc)?$", re.DOTALL) _order_by_braces_re = re.compile(r"(^.*) order by \[[^\]]+\]( desc)?$", re.DOTALL) def strip_order_by(sql): for regex in (_order_by_re, _order_by_braces_re): match = regex.match(sql) if match is not None: return match.group(1) return sql @pytest.mark.parametrize( "sql,expected", [ ("blah", "blah"), ("select * from foo", "select * from foo"), ("select * from foo order by bah", "select * from foo"), ("select * from foo order by bah desc", "select * from foo"), ("select * from foo order by [select]", "select * from foo"), ("select * from foo order by [select] desc", "select * from foo"), ], ) def test_strip_order_by(sql, expected): assert strip_order_by(sql) == expected ``` But it turns out I don't need it! The SQL that is passed to the facet class is created by this code: https://github.com/simonw/datasette/blob/ba11ef27edd6981eeb26d7ecf5aa236707f5f8ce/datasette/views/table.py#L677-L684 And the only place that uses that `sql_no_limit` variable is here: https://github.com/simonw/datasette/blob/ba11ef27edd6981eeb26d7ecf5aa236707f5f8ce/datasette/views/table.py#L733-L745 So I can change that to `sql_no_limit_no_order` and fix the bug that way instead. {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Big performance boost on faceting: skip the inner order by 944870799  
880900534 https://github.com/simonw/datasette/issues/1394#issuecomment-880900534 https://api.github.com/repos/simonw/datasette/issues/1394 MDEyOklzc3VlQ29tbWVudDg4MDkwMDUzNA== simonw 9599 2021-07-15T17:58:03Z 2021-07-15T17:58:03Z OWNER Started a conversation about this on the SQLite forum: https://sqlite.org/forum/forumpost/2d76f2bcf65d256a?t=h {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Big performance boost on faceting: skip the inner order by 944870799  
881129149 https://github.com/simonw/datasette/issues/1394#issuecomment-881129149 https://api.github.com/repos/simonw/datasette/issues/1394 IC_kwDOBm6k_c40hPa9 simonw 9599 2021-07-16T02:23:32Z 2021-07-16T02:23:32Z OWNER Wrote about this in the annotated release notes for 0.58: https://simonwillison.net/2021/Jul/16/datasette-058/ {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} Big performance boost on faceting: skip the inner order by 944870799  

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]);