Data Explorer Plugin

If the column that is returned is called user_id or another name that ends with user_id, it will return a clickable avatar. For example, the following query will return user IDs instead of clickable avatars:

SELECT
id
FROM users LIMIT 5

To have clickable avatars returned, change the query to:

SELECT
id AS user_id
FROM users LIMIT 5
5 Likes

There is not. Data Explorer is very intentionally read-only. It is used to obtain data, never modify it.

You’d need to connect to the site via a rails or db console to make changes via SQL commands.

5 Likes

You can construct it with a admin_page_url column, or generally any result name ending in _url.

SELECT
  '/admin/users/' || users.id || '/' || users.username_lower
    AS admin_page_url
FROM users WHERE id = 172

Source: https://github.com/discourse/discourse-data-explorer/blob/master/assets/javascripts/discourse/components/query-row-content.js.es6#L86

Edit: _url has more hidden features. You can also do this:

SELECT
  'Admin Page,/admin/users/' || users.id || '/' || users.username_lower
    AS admin_page_url
FROM users WHERE id = 172
5 Likes

You can find instructions for that here: How to run Data Explorer queries with the Discourse API

The most likely problem is that you’re trying to pass the api_key in the URL. That’s no longer supported - you need to use HTTP headers to pass the credentials. More details on that can be found at docs.discourse.org

7 Likes

That’s the bit I missed: has to be headers now. Thanks!
dg

5 Likes

Thanks for the PR @Grayden_Shand - sorry we missed it for so long. We keep a very close eye on Discourse core PRs, but sometimes the plugins/themes are accidently missed.

We don’t require a post on Meta, but it can sometimes help to give context on a change. If I PR has been without activity for a very long time, do feel free to make a post here to remind us, as you’ve done :+1:

11 Likes

We were running a very old version of both Discourse and this plugin and we recently updated everything. Somewhere along the way, a limit on exported rows from query results was introduced, capping it at 10,000. Is there anyway to bypass this?

2 Likes

The limit on returning a maximum of 10 000 results is hard coded into the plugin. There is no way that I am aware of to override it.

3 Likes

You can use LIMIT/OFFSET statements in your SQL query to pull the data in batches of 10k and stitch the resulting CSVs/JSON together to get the full data.

2 Likes

Hello,
I’m having a hard time understanding the relationship between posts and topics. Typically some queries I’d like to write are: Topics without replies, Top 5 topics with most replies, etc. (Basically I’m looking to differentiate posts and topics.)

I understand that some posts are also considered topics (not sure why though). I also understand that a topic has posts_count, but why can it be 0 or 1 when there are no replies? What topics have no replies but posts_count = 2 ? (I think I have this for an About topic.)

Some clarity on this and maybe example queries would be much appreciated.
Thanks!

2 Likes

Topics are collections of posts. Let’s use this topic as an example. This topic has a title, “Data Explorer Plugin”, and ID, 32566, and contains 12 posts.

Posts are the individual “content” within a topic. This includes the “OP”, or original post, that is created at the same time as the topic.

A topic should always have a post count of 1 or greater. Are you seeing topics with a post count of 0? Far as I know that shouldn’t occur.

Most likely the topic has “small posts”. That’s things like topic closed, topic opened, pinned, archived, etc (see example below). Does the topic you see with “no replies” have a small post? If not, can you share a screenshot of the topic? As well as the query you used that showed the topic has a post count of 2?

7 Likes

Thanks, very interesting. This definitely clears things up.

So I’m running this query:

SELECT t.id, t.title, t.user_id, t.category_id, t.posts_count
FROM topics as t
JOIN categories as c
ON (t.category_id = c.id)
WHERE c.slug IN ('alenepa','alenepanews','alenepadata','alenepash')

and it’s returning me these results:

But from what I can tell, these topics seem to have been modified or deleted so that may be why posts_count = 0.

So is there a proper way of differentiating a “reply” post from a post which initiated the topic ? I was thinking maybe by comparing created_at or maybe reply_to_post_number. For instance, in our situation, I don’t want the first post by Kane York but all the other posts.

2 Likes

You can eliminate “deleted topics” cases by adding to the query the condition deleted_at ISNULL.

The first post will always be the main topic, so in the table posts, post_number = 1 is the initiated topic.

In this case, you must use the condition post_number > 1.

Hope this helps.

6 Likes

Definitely! Thanks for the feedback!

4 Likes

A post was split to a new topic: Pre-seeded Data Explorer “assigned” queries need updating

Not sure if this is possible, but I’m trying to come up with a query that will find an exact phrase in a post. I can search for, say “human AND workflow” but what I can’t seem to figure out is to find an exact phrase ‘human workflow’.

If anyone has any tips on this … I’m all ears!

dg

1 Like

have you tried

%human workflow%

2 Likes

I have. It gives me the same results as ‘human workflow’

1 Like

Have to use a different kind of query:

phraseto_tsquery('human workflow')

does the trick.

3 Likes

So here’s some very odd behavior. I now have 2 queries defined. One uses
phrase_tsquery() as mentioned above to do an exact phrase match. The other uses TO_TSQUERY(:query) to match any/all search terms.

-- [params]
-- string :query = 'connectors'

SELECT p.id as post_id, p.raw as raw FROM posts p
LEFT JOIN post_search_data psd ON psd.post_id = p.id
WHERE psd.search_data @@ phraseto_tsquery(:query)

And

-- [params]
-- string :query = 'connectors'

SELECT p.id as post_id, p.raw as raw FROM posts p
LEFT JOIN post_search_data psd ON psd.post_id = p.id
WHERE psd.search_data @@ TO_TSQUERY(:query)

If I run those queries from the queries page in the Admin console, the first returns zero results, and the second returns 71 results.

The first is saved as query 4, the second as query 3. When I call the forum.com/admin/plugin/queries/3/run or /4/run via an API call, I get the exact same results. 71 results. And it’s the results for the #3 query. How can that be possible??

1 Like