issue_comments
5 rows where issue = 440222719 and "updated_at" is on date 2021-11-15
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 |
---|---|---|---|---|---|---|---|---|---|---|---|
969436930 | https://github.com/simonw/datasette/issues/448#issuecomment-969436930 | https://api.github.com/repos/simonw/datasette/issues/448 | IC_kwDOBm6k_c45yG8C | simonw 9599 | 2021-11-15T23:31:58Z | 2021-11-15T23:31:58Z | OWNER | I think this SQL recipe may work instead: ```sql select * from ads_with_targets where 'people_who_match:interests:African-American Civil Rights Movement (1954—68)' in ( select value from json_each(target_names) ) and 'interests:Martin Luther King III' in ( select value from json_each(target_names) ) ``` https://json-view-facet-bug-demo-j7hipcg4aq-uc.a.run.app/russian-ads?sql=select%0D%0A++*%0D%0Afrom%0D%0A++ads_with_targets%0D%0Awhere%0D%0A++%27people_who_match%3Ainterests%3AAfrican-American+Civil+Rights+Movement+%281954%E2%80%9468%29%27+in+%28%0D%0A++++select%0D%0A++++++value%0D%0A++++from%0D%0A++++++json_each%28target_names%29%0D%0A++%29%0D%0A++and+%27interests%3AMartin+Luther+King+III%27+in+%28%0D%0A++++select%0D%0A++++++value%0D%0A++++from%0D%0A++++++json_each%28target_names%29%0D%0A++%29&interests=&African=&Martin= | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | _facet_array should work against views 440222719 | |
969440918 | https://github.com/simonw/datasette/issues/448#issuecomment-969440918 | https://api.github.com/repos/simonw/datasette/issues/448 | IC_kwDOBm6k_c45yH6W | simonw 9599 | 2021-11-15T23:40:17Z | 2021-11-15T23:40:35Z | OWNER | Applied that fix to the `arraycontains` filter but I'm still getting bad results for the faceting: <img width="915" alt="russian-ads__ads_with_targets__172_rows_where_where_target_names_contains__people_who_match_interests_African-American_culture__and_datasette_—_pipenv_shell_▸_python_—_80×24" src="https://user-images.githubusercontent.com/9599/141869800-0092e0e1-005e-43bc-bac5-bf0e159224a8.png"> Should never get 182 results on a page that faceting against only 172 items. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | _facet_array should work against views 440222719 | |
969442215 | https://github.com/simonw/datasette/issues/448#issuecomment-969442215 | https://api.github.com/repos/simonw/datasette/issues/448 | IC_kwDOBm6k_c45yIOn | simonw 9599 | 2021-11-15T23:42:03Z | 2021-11-15T23:42:03Z | OWNER | I think this code is wrong in the `ArrayFacet` class: https://github.com/simonw/datasette/blob/502c02fa6dde6a8bb840af6c4c8cf858aa1db687/datasette/facets.py#L357-L364 | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | _facet_array should work against views 440222719 | |
969446972 | https://github.com/simonw/datasette/issues/448#issuecomment-969446972 | https://api.github.com/repos/simonw/datasette/issues/448 | IC_kwDOBm6k_c45yJY8 | simonw 9599 | 2021-11-15T23:46:13Z | 2021-11-15T23:46:13Z | OWNER | It looks like the problem here is that some of the tags occur more than once in the documents: <img width="1146" alt="russian-ads__ads_with_targets__172_rows_where_where_target_names_contains__people_who_match_interests_African-American_culture_" src="https://user-images.githubusercontent.com/9599/141870312-5ffe7151-826e-42e0-b1cc-36c46de6b618.png"> So they get counted more than once, hence the 182 count for something that couldn't possibly return more than 172 documents. | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | _facet_array should work against views 440222719 | |
969449772 | https://github.com/simonw/datasette/issues/448#issuecomment-969449772 | https://api.github.com/repos/simonw/datasette/issues/448 | IC_kwDOBm6k_c45yKEs | simonw 9599 | 2021-11-15T23:48:37Z | 2021-11-15T23:48:37Z | OWNER | Given this query: https://json-view-facet-bug-demo-j7hipcg4aq-uc.a.run.app/russian-ads?sql=select%0D%0A++j.value+as+value%2C%0D%0A++count%28*%29+as+count%0D%0Afrom%0D%0A++%28%0D%0A++++select%0D%0A++++++id%2C%0D%0A++++++file%2C%0D%0A++++++clicks%2C%0D%0A++++++impressions%2C%0D%0A++++++text%2C%0D%0A++++++url%2C%0D%0A++++++spend_amount%2C%0D%0A++++++spend_currency%2C%0D%0A++++++created%2C%0D%0A++++++ended%2C%0D%0A++++++target_names%0D%0A++++from%0D%0A++++++ads_with_targets%0D%0A++++where%0D%0A++++++%3Ap0+in+%28%0D%0A++++++++select%0D%0A++++++++++value%0D%0A++++++++from%0D%0A++++++++++json_each%28%5Bads_with_targets%5D.%5Btarget_names%5D%29%0D%0A++++++%29%0D%0A++%29%0D%0A++join+json_each%28target_names%29+j%0D%0Agroup+by%0D%0A++j.value%0D%0Aorder+by%0D%0A++count+desc%2C%0D%0A++value%0D%0Alimit%0D%0A++31&p0=people_who_match%3Ainterests%3AAfrican-American+culture ```sql select j.value as value, count(*) as count from ( select id, file, clicks, impressions, text, url, spend_amount, spend_currency, created, ended, target_names from ads_with_targets where :p0 in ( select value from json_each([ads_with_targets].[target_names]) ) ) join json_each(target_names) j group by j.value order by count desc, value limit 31 ``` How can I return a count of the number of documents containing each tag, but not the number of total tags that match including duplicates? | {"total_count": 0, "+1": 0, "-1": 0, "laugh": 0, "hooray": 0, "confused": 0, "heart": 0, "rocket": 0, "eyes": 0} | _facet_array should work against views 440222719 |
Advanced export
JSON shape: default, array, newline-delimited, object
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]);