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.
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.
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
By the way, I updated the query list
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.
Sure Mike, the query that I posted is for âsearchâ by term into the url field
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 anyway !
Thanks!
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.
Count gives you âwhere not nullâ for free
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!
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