very powerful. While canwe have some buttoms for some most used queries? You know, not so many people know how these commonds should be like I think.
Folks have been collecting and sharing useful queries in this topic:
Why I did this but still find nothing. Where should I find the query window?
Can you confirm that the plugin is activated? If it is you should see it in the left nav when you go to /admin/plugins
O, I see it now. Thank you.
I am not sure if anybody is facing this same problem as I am.
On the one hand, I appreciate that certain fields (such as
user_id) get turned into a preview of the actual item (e.g. the post itself) instead of a cold number.
On the other hand, it seems to sometimes choke on certain values.
For example, if I do a simple
SELECT post_id FROM posts, it will come back with a 500 server error. Doing
SELECT post_id as int FROM posts will work just fine.
So, obviously, there are
post_id that, when Data Explorer is trying to get the corresponding item, will cause an error.
Thing is, I have seen sub-ranges like
SELECT post_id FROM posts WHERE post_id BETWEEN x AND y work fine, while other ranges will throw a 500.
Is it just me or is anybody else getting these errors? They only happen if you’re trying to
SELECT fields that have special display.
My guess is that there’s a limit to how many rows of can handle. I’d add a
limit 100 to the query (or a big a number as well work).
AFAIK, the data explorer plugin has limit built in.
My guess is the plugin is trying to get posts (to display them) that are in messages or have been deleted and the plugin doesn’t have Admin privileges.
If you change from SELECTing the posts table to the badge_posts table (a view) does that work?
It is not the limit. I know there is a limit as to how many rows are returned. But the limit just chops off the rows, not return a 500.
I’ve had queries like
SELECT post_id FROM posts WHERE post_id = xxx come back with 500 and that is certainly only one row.
And it is not limited to
posts. Many other tables, as long as there is a field that has special display, there is a chance for the 500 to popup.
The first time I noticed this was selecting on
post_custom_fields. Came back with a 500 and at that time I know which post it was. So tried to select the post in
posts and it also came back with 500.
post_id as int came back fine, though.
EDIT: I know I shouldn’t be whining without more data. I’ll find some time to trace down a few examples and post here.
Might it be because the posts table does not have a post_id field?
Unfortunately it is not that simple…
I made a typo. Should be
id for the
posts table and
post_id everywhere else.
I traced some 500 failure records, and so far ALL of them have topics that are DELETED. Most of them are the first post of a deleted topic.
If you want to use the posts table instead of badge_posts try adding this to the query
WHERE deleted_at IS NULL
This works fine because the id is just a number on the
posts table, not a special display:
SELECT id FROM posts
This throws 500; the special display on
topic_id throw error:
SELECT id, topic_id FROM posts
This doesn’t work also:
SELECT topic_id FROM posts WHERE deleted_at IS NULL
SELECT topic_id as int FROM posts
Conclusion: The problem is not the query. It is with special displays of topics, users and posts.
OK, I just found an example:
Topic is NOT deleted. It has a normal user and all fields look normal.
SELECT topic_id FROM posts WHERE topic_id=xxx will throw 500.
SELECT topic_id as some_other_name FROM posts WHERE topic_id=xxx works but without special display on the topic.
EDIT: The error logs are littered with this:
NoMethodError (undefined method `username' for nil:NilClass) /var/www/discourse/plugins/discourse-data-explorer/plugin.rb:52:in `username'
ActiveModel::MissingAttributeError (missing attribute: category_id) /var/www/discourse/vendor/bundle/ruby/2.4.0/gems/activerecord-5.1.4/lib/active_record/attribute_methods/read.rb:71:in `block in _read_attribute'
I am pretty sure this is one of the causes:
object is most definitely a post.
nil (which can be when the post is deleted and the user also deleted, as in cleanup from SPAM), this will throw.
I’m sure it should be something like
object.user && object.user.username.
which is correct.
As for this error, I believe somewhere is trying to display the
category_id property of a topic but couldn’t find it because
BasicTopicSerializer does not include the
category_id as one of its attributes.
category_id attribute is added by the
So I suspect that Data Explorer will get errors displaying topics (from
Babble is installed.
So, how to unset the 1000 limit when making a call through API?
limit parameter, e.g.
limit=100000000. It must be finite.
… reading that, I’m not sure how format csv actually works…