こんにちは、
ユーザーがプロフィールに不適切な外部リンクを追加していないか確認したいと考えています。
一つずつ確認する方法以外で、最も効果的な方法はどのようなものでしょうか？
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 DESCThat 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