Data explorer challenge: Generate list of members by location

社区 数据与报告
1

On our site, we populate the location field via SSO with the country where the member is based. I had a request this week from a moderator who wanted to alert members in Pakistan about a new resource published specific to Pakistan. He wanted to do this by mentioning all of them by name in a post.

I looked into it and found that I don’t know an easy way to do this via the UI. Location isn’t even provided in user exports which surprised me. Am I missing something?

Data explorer seems the low hanging fruit home for solving this problem. Has anyone created a data explorer query to search the user list by location and spit out a list of usernames who share that location?

A better solution for the longer term to this specific problem might be a new feature to be able to mention users by country somehow, e.g. !pakistan or some such.

2

The issue with the location field as it is is that is free text entry - so whilst some people could have put in Pakistan others could have just put in India for example. I would love to be able to use a localised mention system or at least some visibility on where members are from but whilst its free text and not validated or from a drop down its going to prove difficult.

3

Thanks for the thoughts. On my site it’s not a freetext field, actually - it’s handled via SSO externally and is always countries. I’m sure many sites do this. In any case this query doesn’t have to look for a perfect match.

Maybe another approach would be to use the user list, which currently only allows filtering by username. An additional filter by profile option would be interesting - e.g. show me everyone who indicates Pakistan in their profile bio or location. And then an admin link to export directly from there. :wink:

All of this would be moot if profile details were included in user export - let me check again to see if I have it right that location is not actually exported. That really surprised me.

4

Did you get the country filter working? I would be very interested in seeing it. My members really like to group per country

6

也许我不明白这个问题，但如果我明白了，那么

SELECT u.username
    FROM users u
    LEFT JOIN user_profiles up ON u.id = up.user_id
    WHERE location = 'Pakistan'

或者，同样地，

SELECT username from user_profiles, users
where user_id = id and location = 'Pakistan'

请注意，数据浏览器不接受双引号作为分隔符。

7

太好了！感谢您（迟到很久的）提供解决我问题的查询。也许它会对其他人有所帮助。我已不再在 Namati 工作，当时我们正试图将帖子定位到巴基斯坦的成员。最终，我们解决此类问题的方法是创建一个 WordPress 的洞察仪表板，我们通过 WP Discourse WordPress 插件和 SSO 设置了该仪表板来配合 Discourse。在仪表板中，我们创建了一个视图，允许我们按地点和许多其他标准进行筛选。

然后，我们可以将该列表导出到电子表格中，并获取用户名或电子邮件地址，以各种方式在 Discourse 中使用。我最喜欢的方式是直接向每位用户发送私人消息，让他们知道一个针对他们的机会。

8

我曾有机会扩展唐的查询，为所有有位置的用户生成一个包含用户名、位置和电子邮件的列表。在某些情况下，这可能很有趣或有用：

SELECT u.username, u.locale, up.location, ue.email
    FROM users u
    LEFT JOIN user_profiles up ON u.id = up.user_id
    LEFT JOIN user_emails ue ON u.id = ue.user_id
    -- comment out WHERE clause for complete user list
    WHERE up.location <> ''
    ORDER BY up.location