filter for users who have profile bio content

[ebarry]

There should probably be a more comprehensive brainstorm on how to make http://publiclab.org/people something useful for community development. But for now, can admins get a datadump for all usernames where anyone has filled out anything in their profile?

just two columns, username, and text from profile. no images.

[jywarren]

Thanks -- urgency?

[ebarry]

not urgent.

[btbonval]

This will be fantastic for moderators to find spammers we haven't gotten to yet!

I bet there are thousands of such users with spam profiles that aren't yet banned.

[ebarry]

If this is to be built into the site as a filter for users who have profile bio content, i will also need to be able to search within profile content from the site. That's what i was planning to do with the datadump.

[ebarry]

Gently bumping this up. Stevie and I are working on geography this fall (Q4) and as part of that we will be doing targeted geographic outreach to potential organizers. Having this filter will enable us to search for people who mention geographies in their bio, letting us make progress on this in the lagtime before RichProfiles happens.

[btbonval]

What format do you want this in? CSV will probably not work because profiles can have commas (there are ways around this).

You just want username and profile. Would it suffice to do something like this:

# username

profileprofile

profile

profile

# username

profile profile profile

# username

profile

[ebarry]

Could you get username, email address, if they've ever posted or commented (or not), and
the full text of what they typed in as their bio?
On Oct 13, 2015 10:07 PM, "Bryan Bonvallet" [email protected]
wrote:

What format do you want this in? CSV will probably not work because
profiles can have commas (there are ways around this).

You just want username and profile. Would it suffice to do something like
this:

username

profileprofile

profile

profile

username

profile profile profile

username

profile

—
Reply to this email directly or view it on GitHub
#308 (comment).

[btbonval]

Oh, it looks like I don't have access to the system anymore. I thought I did.

[btbonval]

woops I have access. Some of my connection configurations are out of date.

[btbonval]

I think this query should work. I started running it but it appears to take time. Works fine if I restrict it to one or two users specifically.

SELECT users.name, count(node.nid) AS notes, count(comments.cid) AS comments, profile_values.value AS profile
FROM users
INNER JOIN profile_values ON (users.uid = profile_values.uid AND profile_values.fid = 1 AND length(profile_values.value) > 0)
LEFT OUTER JOIN node ON (users.uid = node.uid)
LEFT OUTER JOIN comments ON (users.uid = comments.uid);

That'll get the first profile though, but not later updates. I can't seem to figure out how to get MySQL to get the max fid per user. SELECT uid, max(fid) FROM profile_values GROUP BY uid ignores GROUP BY and simply returns the same max(fid) for every row.

[btbonval]

neat trick using self outer join to find largest value (newest profile_value in this case)

SELECT pv1.value
FROM profile_values AS pv1
LEFT OUTER JOIN profile_values AS pv2
  ON (pv1.uid = pv2.uid AND pv1.fid < pv2.fid) WHERE pv2.fid IS NULL
ORDER BY pv1.uid;

I forgot to make sure user is not banned, and I screwed up the counts for comments and notes. This looks better, and it seemingly runs faster too.

SELECT users.name, node.notes, comments.comments, profile_values.value AS profile
FROM users
INNER JOIN (
  SELECT pv1.uid, pv1.value
  FROM profile_values AS pv1
  LEFT OUTER JOIN profile_values AS pv2
    ON (pv1.uid = pv2.uid AND pv1.fid < pv2.fid)
  WHERE pv2.fid IS NULL AND length(pv1.value) > 0
) AS profile_values ON (users.uid = profile_values.uid AND users.status > 0)
LEFT OUTER JOIN (
  SELECT uid, count(nid) AS notes
  FROM node
  GROUP BY uid
) AS node ON (users.uid = node.uid)
LEFT OUTER JOIN (
  SELECT uid, count(cid) AS comments
  FROM comments
  GROUP BY uid
) AS comments ON (users.uid = comments.uid);

[btbonval]

@ebarry okay I ran this and exported. It was too big to share via email so I uploaded to drive and shared on that.

[btbonval]

To reiterate my comment earlier:

btbonval commented on Aug 25
This will be fantastic for moderators to find spammers we haven't gotten to yet!

This turns out to be very true.

[ebarry]

Brian, thank you. This is a treasure trove for place-based organizing.

[btbonval]

do the thing again, but with more thing!

1. newer. not the old one, but a new one. like from now. and not the before times.
2. include user email addresses
3. possibly include signup date
4. (possibly include last login. not asked for, but heck)
5. workaround the spaces.

[btbonval]

Adds new fields requested but no workarounds for spacing issues.

SELECT users.name, users.mail AS email, users.created AS signup_date, users.access AS last_browse_date, node.notes, comments.comments, profile_values.value AS profile
FROM users
INNER JOIN (
  SELECT pv1.uid, pv1.value
  FROM profile_values AS pv1
  LEFT OUTER JOIN profile_values AS pv2
    ON (pv1.uid = pv2.uid AND pv1.fid < pv2.fid)
  WHERE pv2.fid IS NULL AND length(pv1.value) > 0
) AS profile_values ON (users.uid = profile_values.uid AND users.status > 0)
LEFT OUTER JOIN (
  SELECT uid, count(nid) AS notes
  FROM node
  GROUP BY uid
) AS node ON (users.uid = node.uid)
LEFT OUTER JOIN (
  SELECT uid, count(cid) AS comments
  FROM comments
  GROUP BY uid
) AS comments ON (users.uid = comments.uid);

[btbonval]

It looks like dates are stored in time since epoch, which is not very useful. In the profie, line feeds and carriage returns need to be replaced with spaces and commas should be replaced with semi-colons or something. This can probably be done as part of the MySQL query.

[btbonval]

I'm going to skip login times after all. looks like those times are not being updated, so it is probably not worth including. #360

I'll keep signup date though.

[btbonval]

SELECT users.name, users.mail AS email, FROM_UNIXTIME(users.created) AS signup_date, node.notes, comments.comments,
REPLACE(REPLACE(REPLACE(TRIM(profile_values.value), ",", ";"), "\r", " "), "\n", " ") AS profile
FROM users
INNER JOIN (
  SELECT pv1.uid, pv1.value
  FROM profile_values AS pv1
  LEFT OUTER JOIN profile_values AS pv2
    ON (pv1.uid = pv2.uid AND pv1.fid < pv2.fid)
  WHERE pv2.fid IS NULL AND length(pv1.value) > 0
) AS profile_values ON (users.uid = profile_values.uid AND users.status > 0)
LEFT OUTER JOIN (
  SELECT uid, count(nid) AS notes
  FROM node
  GROUP BY uid
) AS node ON (users.uid = node.uid)
LEFT OUTER JOIN (
  SELECT uid, count(cid) AS comments
  FROM comments
  GROUP BY uid
) AS comments ON (users.uid = comments.uid);

[btbonval]

oh I guess last time I supplied a tab-separated value sheet so the commas shouldn't have been a problem. There might have been rogue tabs in profiles, but tabs in html editors are far less likely than commas as tab usually changes focus rather than entering a character.

Good thing to keep in mind for next time: TSVs work fine, change the replace above comma-to-semicolon conversion to convert tabs into four spaces and then almost the entire sense is maintained.

[btbonval]

@ebarry has requested to include wiki edits and comments. I'll have to read the code on how plots2 does this, but I don't think it is something straightforward to do directly in the database.

[grvsachdeva]

Hey @ebarry, what goals/issues we can make from here, keeping in view the latest /people page? Thanks!

[ebarry]

The essence of this issue is to have profile bio *text* and *tags* show up in Search results. If this is happening already (?), then we can just close this issue.

…

On Mon, Mar 25, 2019, 11:05 AM Gaurav Sachdeva ***@***.***> wrote: Hey @ebarry <https://github.com/ebarry>, what goals/issues we can make from here, keeping in view the latest /people page? Thanks! — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#308 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAJ2nyc7xbOcbvjxva4mMzyNE9hSdOFfks5vaOXNgaJpZM4FwnoF> .

[ebarry]

I suppose there is a second part which is that a Stats function can show how many people have made which types of contributions. It's a community awareness / eval function, as well as perhaps a support for spam moderation. Perhaps we could move over to a Stats issue to describe how that could work.

…

On Mon, Mar 25, 2019, 12:28 PM Liz Barry ***@***.***> wrote: The essence of this issue is to have profile bio *text* and *tags* show up in Search results. If this is happening already (?), then we can just close this issue. On Mon, Mar 25, 2019, 11:05 AM Gaurav Sachdeva ***@***.***> wrote: > Hey @ebarry <https://github.com/ebarry>, what goals/issues we can make > from here, keeping in view the latest /people page? Thanks! > > — > You are receiving this because you were mentioned. > Reply to this email directly, view it on GitHub > <#308 (comment)>, > or mute the thread > <https://github.com/notifications/unsubscribe-auth/AAJ2nyc7xbOcbvjxva4mMzyNE9hSdOFfks5vaOXNgaJpZM4FwnoF> > . >

[grvsachdeva]

The essence of this issue is to have profile bio text and tags show up
in Search results.
If this is happening already (?), then we can just close this issue.

We currently show user bio only on /profile page but let's think where else we can add bio field. Here's the search result for warren below 👇

If we decide on adding another column for bio in the table, that can make the page a little crowded? What do you think about displaying a popup on hovering over the user image or name? Or, any other idea? That popup can also contain the user profile tags.

[grvsachdeva]

I suppose there is a second part which is that a Stats function can show
how many people have made which types of contributions. It's a community
awareness / eval function, as well as perhaps a support for spam
moderation. Perhaps we could move over to a Stats issue to describe how
that could work.

Yes @ebarry, that'll be good for the stats page. Should I append this part to stats issue (planning)?

Thanks!

[ebarry]

Great plan re: stats planning issue! For the bio, it's not important that the full text of the bio be printed on the search results page . The important part is that the searched-for term yields profiles where the term appears in bio text or even bio link text as results on the search page. Does this make sense? It's hard to explain precisely.

…

On Mon, Mar 25, 2019, 1:48 PM Gaurav Sachdeva ***@***.***> wrote: I suppose there is a second part which is that a Stats function can show how many people have made which types of contributions. It's a community awareness / eval function, as well as perhaps a support for spam moderation. Perhaps we could move over to a Stats issue to describe how that could work. Yes @ebarry <https://github.com/ebarry>, that'll be good for the stats page. Should I append this part to stats issue (planning)? Thanks! — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#308 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AAJ2n26vVv_ApYMTmqf9MABOiocfTf81ks5vaQwCgaJpZM4FwnoF> .

[grvsachdeva]

Hmm, that's interesting!

Currently, we don't search over profile bio, but yes, it would be awesome to see search related profiles. @jywarren should we make improvement in the current API for this or new API?

Great idea Liz!

[jywarren]

Oh actually yes we do search bio content: https://github.com/publiclab/plots2/blob/master/app/models/user.rb#L360-L362 We don't search profile tags, so that could be broken out into a new issue and this one closed. For the stats stuff, let's also open a new issue -- we do list types of contributions by tag already so let's be sure to add something very specific. Thanks!

…

On Mon, Mar 25, 2019 at 2:01 PM Gaurav Sachdeva ***@***.***> wrote: Hmm, that's interesting! Currently, we don't search over profile bio, but yes, it would be awesome to see search related profiles. @jywarren <https://github.com/jywarren> should we make improvement in the current API for this or new API? Great idea Liz! — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#308 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AABfJywKFuYKWEnXVA0Dkr5cCGpfYkR-ks5vaQ7QgaJpZM4FwnoF> .

[grvsachdeva]

Oh actually yes we do search bio content:
https://github.com/publiclab/plots2/blob/master/app/models/user.rb#L360-L362

😮

[jywarren]

lol gaurav :-))))))

…

On Mon, Mar 25, 2019 at 2:11 PM Gaurav Sachdeva ***@***.***> wrote: Oh actually yes we do search bio content: https://github.com/publiclab/plots2/blob/master/app/models/user.rb#L360-L362 😮 — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub <#308 (comment)>, or mute the thread <https://github.com/notifications/unsubscribe-auth/AABfJ9j2vWlI7hDRaBh2gDsorHkhVUkhks5vaRFIgaJpZM4FwnoF> .

[grvsachdeva]

@jywarren regarding searching user tag. I found this suggestion from GitHub - #3506.

[grvsachdeva]

I have dropped @ebarry's thought as a comment in Stats planning issue, let take it there #3498 (comment)

And, regarding usertag @jywarren is this - #3506 related?

Thanks and closing this one!