Checking user profiles for inappropriate links

Hi,

I want to check that our users are not adding any inappropriate external links to their user profiles.

What would be the best way of doing this that is not checking one by one?

A.

You could use the data explorer plug in.

2 Likes

It seems a very useful plug in, I’ll look into installing it.

Great idea.

I imagine this query:

-- [params]
-- int :limit = 150
-- string :url = %term%

SELECT 
    up.user_id, u.name, up.website, u.updated_at as updated, up.location, up.views as views
FROM user_profiles up, users u
WHERE up.user_id = u.id
AND (u.admin = 'f' AND u.moderator = 'f')
AND up.website ILIKE :url
ORDER BY views desc
LIMIT :limit

I tried to put a param for “order by” but I can’t, if some guru from Data Explorer (@simon) may help here I’ll appreciate a lot :wink:

By the way, I updated the query list :rocket:

5 Likes

The order by seems to work ok for me - may need to add a disclaimer saying they need to put whatever they are looking for in between the %% where you have the term in order to return it (some people may not know SQL).

Otherwise you could just change the line

AND up.website ILIKE :url

to

AND up.website IS NOT NULL

To return all profiles with a webaddress.

2 Likes

Sure Mike, the query that I posted is for “search” by term into the url field :wink:
I added that because the @testingsoftware said that he want to check if their users are not adding any inappropriate external links.

I’ll updated the list with your contribution :+1: anyway !
Thanks!

1 Like

Thanks for this, this is brilliant! I will check it out when I get the plug in installed.

Hi @SidV and @mikechristopher,

Thanks for your help.
I am just learning how to query and the query sent above gave me an error so I came up with this one that seems to show the urls and bio of users if they have any of the two. I didn’t put any limit as I would like to see the information for all users.
I will master aliases one of these days to type a bit less…

SELECT 
    users.username AS "Username",
    users.approved AS "Approved?",
    user_profiles.website AS "URL",
    user_profiles.bio_raw AS "User s info"
FROM users
JOIN  user_profiles ON users.id =  user_profiles.user_id
WHERE user_profiles.website IS NOT NULL OR user_profiles.bio_raw IS NOT NULL
ORDER BY users.approved DESC

That looks like a good try, but there is more you should know.

The Data Explorer plugin has built in LIMIT so time and memory consuming queries won’t cause a bog down.

“approved” is like a boolean true / false field so ordering by it is mostly useless.

IMHO it would be more useful and more efficient to ORDER BY the indexed id field.

Try these

SELECT COUNT(`website`) 
FROM `user_profiles` 
WHERE `website` IS NOT NULL; 

and

SELECT COUNT(`bio_raw`) 
FROM `user_profiles` 
WHERE `bio_raw` IS NOT NULL; 

to get an idea of how many rows you’re dealing with.

1 Like

Count gives you “where not null” for free :slight_smile:

3 Likes

Thanks guys, I’ll try these to see the amount of results before getting the listing.

We have lots of not approved accounts (old accounts from software migration) and I wanted to see first the approved accounts.

The Data Explorer plugin has built in LIMIT so time and memory consuming queries won’t cause a bog down.

ok, thanks, better keep queries simple then? at my level, no problem with that! :wink:

1 Like

If you’re ever worried about how complex your query is, turn on the ‘show query plan’ option. The “cost” unit doesn’t have a straight relation to time, but you can get a good idea.

In that case - try

SELECT COUNT(website), COUNT(bio_raw), approved
FROM user_profiles
GROUP BY approved

:slight_smile:

2 Likes