Data Explorer - Legacy replies


(David García-Navas) #1

We’ve upgraded the plugin with your last commits and now works perfect! Thanks!

Btw, the “Most Common Likers” query is a very very useful stat for communities! :smile:
It makes us think on a “tinder”-like badge: when two users give each other n likes, could be a match!
*perhaps a feature for Helloforos? cc/ @charleswalter


Data Explorer Plugin
(Felix Freiberger) #2

Should moderators be able to use this plugin?

Currently on discourse-data-explorer d4d5417 and Discourse ad8f2cb, admins and mods can see the link to the data explorer page, but it fails to load for mods.


(Jeff Atwood) #3

No, this is definitely an Admin thing. Very low level access to the database.


(Felix Freiberger) #4

I agree – so having this show up to moderators is a bug, then?

(Of course, it would be cool if admins could allow mods to run certain pre-defined queries – but that certainly is an advanced feature.)


(Jeff Atwood) #5

This should not show up for mods can you check into this @riking?


(Anton) #6

An example of a query that I use for a simple monthly contest in our community:

Fetch top 10 posts by likes received in the last month, excluding administrators. If the likes count equals, prioritize the posts that were created earlier.

SELECT p.id as post_id, p.like_count as like_count
FROM posts p
LEFT JOIN topics t ON t.id = p.topic_id
LEFT JOIN users u ON u.id = p.user_id
WHERE p.created_at >= CURRENT_DATE - INTERVAL '1 month'
  AND NOT u.admin
  AND NOT u.blocked
ORDER BY p.like_count DESC, p.created_at ASC
LIMIT 10

Should I also add clauses to filter out archived and deleted topics?


(Gerhard Schlager) #7

Am I doing something wrong or is the plugin not compatible with the master branch?
I’m getting the following error in the browser console after enabling the plugin in my development environment:

Error: Assertion Failed: The attempt to link-to route 'adminPlugins.explorer' failed. The router did not find 'adminPlugins.explorer' in its possible routes: 'loading', 'error', 'exception', 'about', 'topic.loading', 'topic.error', 'topic.fromParams', 'topic', 'topic.fromParamsNear', 'topicBySlug', 'topicUnsubscribe', 'discovery.loading', 'discovery.error', 'discovery.top', 'discovery.topParentCategory', 'discovery.topCategoryNone', 'discovery.topCategory', 'discovery.topAll', 'discovery.topAllParentCategory', 'discovery.topAllCategoryNone', 'discovery.topAllCategory', 'discovery.topYearly', 'discovery.topYearlyParentCategory', 'discovery.topYearlyCategoryNone', 'discovery.topYearlyCategory', 'discovery.topQuarterly', 'discovery.topQuarterlyParentCategory', 'discovery.topQuarterlyCategoryNone', 'discovery.topQuarterlyCategory', 'discovery.topMonthly', 'discovery.topMonthlyParentCategory', 'discovery.topMonthlyCategoryNone', 'discovery.topMonthlyCategory', 'discovery.topWeekly', 'discovery.topWeeklyParentCategory', 'discovery.topWeeklyCategoryNone', 'discovery.topWeeklyCategory', 'discovery.topDaily', 'discovery.topDailyParentCategory', 'discovery.topDailyCategoryNone', 'discovery.topDailyCategory', 'discovery.latest', 'discovery.latestParentCategory', 'discovery.latestCategoryNone', 'discovery.latestCategory', 'discovery.unread', 'discovery.unreadParentCategory', 'discovery.unreadCategoryNone', 'discovery.unreadCategory', 'discovery.new', 'discovery.newParentCategory', 'discovery.newCategoryNone', 'discovery.newCategory', 'discovery.read', 'discovery.readParentCategory', 'discovery.readCategoryNone', 'discovery.readCategory', 'discovery.posted', 'discovery.postedParentCategory', 'discovery.postedCategoryNone', 'discovery.postedCategory', 'discovery.bookmarks', 'discovery.bookmarksParentCategory', 'discovery.bookmarksCategoryNone', 'discovery.bookmarksCategory', 'discovery.categories', 'discovery.parentCategory', 'discovery.categoryNone', 'discovery.category', 'discovery', 'application', 'group.loading', 'group.error', 'group.members', 'group.index', 'group', 'users', 'user.loading', 'user.error', 'userActivity.loading', 'userActivity.error', 'userActivity.likes_given', 'userActivity.likes_received', 'userActivity.bookmarks', 'userActivity.topics', 'userActivity.posts', 'userActivity.replies', 'userActivity.mentions', 'userActivity.quotes', 'userActivity.edits', 'userActivity.messages_sent', 'userActivity.messages_received', 'userActivity.pending', 'userActivity.index', 'userActivity', 'user.badges', 'user.notifications', 'user.flaggedPosts', 'user.deletedPosts', 'userPrivateMessages.loading', 'userPrivateMessages.error', 'userPrivateMessages.mine', 'userPrivateMessages.unread', 'userPrivateMessages.index', 'userPrivateMessages', 'preferences.loading', 'preferences.error', 'preferences.username', 'preferences.email', 'preferences.about', 'preferences.badgeTitle', 'preferences.card-badge', 'preferences.index', 'preferences', 'userInvited.loading', 'userInvited.error', 'userInvited.show', 'userInvited.index', 'userInvited', 'user.index', 'user', 'signup', 'login', 'login-preferences', 'forgot-password', 'faq', 'tos', 'privacy', 'guidelines', 'new-topic', 'badges.loading', 'badges.error', 'badges.show', 'badges.index', 'badges', 'queued-posts', 'full-page-search', 'admin.loading', 'admin.error', 'admin.dashboard', 'admin', 'adminSiteSettings.loading', 'adminSiteSettings.error', 'adminSiteSettingsCategory', 'adminSiteSettings.index', 'adminSiteSettings', 'adminEmail.loading', 'adminEmail.error', 'adminEmail.all', 'adminEmail.sent', 'adminEmail.skipped', 'adminEmail.previewDigest', 'adminEmail.index', 'adminEmail', 'adminCustomize.loading', 'adminCustomize.error', 'adminCustomize.colors', 'adminCustomizeCssHtml.loading', 'adminCustomizeCssHtml.error', 'adminCustomizeCssHtml.show', 'adminCustomizeCssHtml.index', 'adminCustomizeCssHtml', 'adminSiteText.loading', 'adminSiteText.error', 'adminSiteText.edit', 'adminSiteText.index', 'adminSiteText', 'adminUserFields', 'adminEmojis', 'adminPermalinks', 'adminEmbedding', 'adminCustomize.index', 'adminCustomize', 'admin.api', 'admin.backups.loading', 'admin.backups.error', 'admin.backups.logs', 'admin.backups.index', 'admin.backups', 'adminReports', 'adminFlags.loading', 'adminFlags.error', 'adminFlags.list', 'adminFlags.index', 'adminFlags', 'adminLogs.loading', 'adminLogs.error', 'adminLogs.staffActionLogs', 'adminLogs.screenedEmails', 'adminLogs.screenedIpAddresses', 'adminLogs.screenedUrls', 'adminLogs.index', 'adminLogs', 'adminGroups.loading', 'adminGroups.error', 'adminGroupsType.loading', 'adminGroupsType.error', 'adminGroup', 'adminGroupsType.index', 'adminGroupsType', 'adminGroups.index', 'adminGroups', 'adminUsers.loading', 'adminUsers.error', 'adminUser.loading', 'adminUser.error', 'adminUser.badges', 'adminUser.tl3Requirements', 'adminUser.index', 'adminUser', 'adminUsersList.loading', 'adminUsersList.error', 'adminUsersList.show', 'adminUsersList.index', 'adminUsersList', 'adminUsers.index', 'adminUsers', 'adminBadges.loading', 'adminBadges.error', 'adminBadges.show', 'adminBadges.index', 'adminBadges', 'adminPlugins.loading', 'adminPlugins.error', 'adminPlugins.index', 'adminPlugins', 'unknown'

(Kane York) #8

I’m able to load it here on Meta.


#9

I want to be able to change registration dates of my forum users.
Can i make this possible with this plugin?


(Gerhard Schlager) #10

Found the problem. I forgot to delete the content of the tmp folder after installing it in my development environment. Now it’s working.


(Tobias Eigen) #11

the explorer just lets you see into the data - you can’t change anything. but if you know what you’re doing you could do sql queries from the command line that change the database.


(Charles Walter) #12

I’m trying to figure out how to export the number of topics and posts that our users have created. Is there a variable that I can add to this query?

SELECT
    u.id,
    u.username_lower AS "username",
    u.email,
    u.created_at,
    u.last_seen_at,
   ? ... posts_count
FROM users u
WHERE u.id > 0

(Kane York) #13

Join on the user_stats table, it has posts_count and topics_count.


(Charles Walter) #14

Thx. Glad I was able to figure out how to apply based on another sample I had.

SELECT
    u.id,
    u.username_lower AS "username",
    u.email,
    u.created_at,
    u.last_seen_at,
    us.topic_count,
    us.days_visited,
    us.post_count,
    u.last_seen_at
FROM users u
LEFT JOIN user_stats us ON u.id = us.user_id
WHERE u.id > 0

(Bart) #15

Hey guys, does the Data Explorer plugin run all queries, or will it protect users from bringing their databases to their knees with complex joins etc? Can you only run select statements, or also insert, delete etc?

Thanks!


(Mittineague) #16

AFAIK the plugin does only Read (i.e. SELECT queries)
If you need otherwise the CLI should work.

As far as protecting users from shooting themselves in the foot. I don’t think so. I would hope that anyone using it would know enough to not do a SELECT * FROM * type of query


(Bart) #17

That’s what I figured. Thanks!


(Anton) #18

if I’m not mistaken, if you do SELECT * FROM *, it would limit the request by default to ~1000 items or something like that.


(Kane York) #19

Queries are limited to a 10 second runtime, plus SET TRANSACTION READ ONLY.


(Bart) #20

That sounds like the perfect solution, thanks!