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でさまざまな方法で使用することができました。私のお気に入りの方法は、各ユーザーに直接PMを送信して、彼らに固有の機会について知らせることでした。

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
    -- 完全なユーザーリストの場合はWHERE句をコメントアウトします
    WHERE up.location <> ''
    ORDER BY up.location