Data explorer challenge: Generate list of members by location

Datos e informes
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.

1 me gusta
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.

1 me gusta
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.

5 Me gusta
4

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

2 Me gusta
6

Quizás no entienda la pregunta, pero si la entiendo, entonces

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

o, igualmente,

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

Tenga en cuenta que el Explorador de Datos no acepta comillas dobles como delimitadores.

7

¡Genial! Gracias por la oferta (muy tardía) de una consulta para resolver mi problema. Quizás sea útil para otros. Ya no trabajo en Namati, donde intentábamos dirigir una publicación a miembros en Pakistán. La forma en que finalmente resolvimos este tipo de problema fue creando un panel de información en WordPress, que habíamos configurado para acompañar a Discourse utilizando el plugin de WordPress WP Discourse y SSO. En el panel creamos una vista que nos permitía filtrar por ubicación y muchos otros criterios.

Luego pudimos exportar esa lista a una hoja de cálculo y obtener los nombres de usuario o direcciones de correo electrónico para usarlos de diversas maneras en Discourse. Mi forma preferida era enviar un mensaje privado a cada usuario directamente para informarles sobre una oportunidad específica para ellos.

8

Tuve la ocasión de ampliar un poco la consulta de Don para producir una lista de usuarios, ubicaciones y correos electrónicos de todos los usuarios con una ubicación introducida. Podría ser interesante o útil en algunos casos:

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
    -- comentar la cláusula WHERE para obtener la lista completa de usuarios
    WHERE up.location <> ''
    ORDER BY up.location
3 Me gusta