# 检查用户个人资料中的不当链接 **URL:** \ **Category:** Support\ **Created:** [2018年四月6日 12:42 UTC](https://meta.discourse.org/t/checking-user-profiles-for-inappropriate-links/84718 "2018-04-06T12:42:19Z")\ **Posts on this page:** 13\ **Page:** 1
**Author:** ![testingsoftware](https://avatars.discourse-cdn.com/v4/letter/t/f1d935/32.png) [@testingsoftware](https://meta.discourse.org/u/testingsoftware)\ **Post date:** [2018年四月6日 12:42 UTC](https://meta.discourse.org/t/checking-user-profiles-for-inappropriate-links/84718/1 "2018-04-06T12:42:19Z")
你好， 我想确认一下，我们的用户是否在其个人资料中添加了任何不恰当的外部链接。 有没有一种最佳方法可以批量检查，而无需逐一查看？ A. ---
**Author:** ![pfaffman](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/pfaffman/32/120154_2.png) [@pfaffman](https://meta.discourse.org/u/pfaffman)\ **Post date:** [2018年四月6日 12:49 UTC](https://meta.discourse.org/t/checking-user-profiles-for-inappropriate-links/84718/2 "2018-04-06T12:49:28Z")
You could use the [data explorer](https://meta.discourse.org/t/32566?silent=true) plug in. ---
**Author:** ![testingsoftware](https://avatars.discourse-cdn.com/v4/letter/t/f1d935/32.png) [@testingsoftware](https://meta.discourse.org/u/testingsoftware)\ **Post date:** [2018年四月6日 13:02 UTC](https://meta.discourse.org/t/checking-user-profiles-for-inappropriate-links/84718/3 "2018-04-06T13:02:03Z")
It seems a very useful plug in, I’ll look into installing it. ---
**Author:** ![SidV](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sidv/32/119460_2.png) [@SidV](https://meta.discourse.org/u/SidV)\ **Post date:** [2018年四月6日 14:50 UTC](https://meta.discourse.org/t/checking-user-profiles-for-inappropriate-links/84718/4 "2018-04-06T14:50:09Z")
Great idea. I imagine this query: ```sql -- [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](https://meta.discourse.org/t/32566?silent=true) (@simon) may help here I’ll appreciate a lot 😉 By the way, I updated _[the query list](https://github.com/SidVal/discourse-data-explorer/blob/queries/querys.md)_ 🚀 ---
**Author:** ![mikechristopher](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mikechristopher/32/89135_2.png) [@mikechristopher](https://meta.discourse.org/u/mikechristopher)\ **Post date:** [2018年四月6日 15:04 UTC](https://meta.discourse.org/t/checking-user-profiles-for-inappropriate-links/84718/5 "2018-04-06T15:04:22Z")
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 ```plaintext AND up.website ILIKE :url ``` to ```plaintext AND up.website IS NOT NULL ``` To return all profiles with a webaddress. ---
**Author:** ![SidV](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sidv/32/119460_2.png) [@SidV](https://meta.discourse.org/u/SidV)\ **Post date:** [2018年四月6日 15:08 UTC](https://meta.discourse.org/t/checking-user-profiles-for-inappropriate-links/84718/6 "2018-04-06T15:08:49Z")
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! ---
**Author:** ![testingsoftware](https://avatars.discourse-cdn.com/v4/letter/t/f1d935/32.png) [@testingsoftware](https://meta.discourse.org/u/testingsoftware)\ **Post date:** [2018年四月6日 21:06 UTC](https://meta.discourse.org/t/checking-user-profiles-for-inappropriate-links/84718/7 "2018-04-06T21:06:02Z")
Thanks for this, this is brilliant! I will check it out when I get the plug in installed. ---
**Author:** ![testingsoftware](https://avatars.discourse-cdn.com/v4/letter/t/f1d935/32.png) [@testingsoftware](https://meta.discourse.org/u/testingsoftware)\ **Post date:** [2018年四月18日 11:35 UTC](https://meta.discourse.org/t/checking-user-profiles-for-inappropriate-links/84718/8 "2018-04-18T11:35:07Z")
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 ``` ---
**Author:** ![Mittineague](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/mittineague/32/114259_2.png) [@Mittineague](https://meta.discourse.org/u/Mittineague)\ **Post date:** [2018年四月18日 18:24 UTC](https://meta.discourse.org/t/checking-user-profiles-for-inappropriate-links/84718/9 "2018-04-18T18:24:29Z")
That looks like a good try, but there is more you should know. > [@testingsoftware](#): > > I didn’t put any limit The [Data Explorer](https://meta.discourse.org/t/32566?silent=true) 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 ```plaintext SELECT COUNT(`website`) FROM `user_profiles` WHERE `website` IS NOT NULL; ``` and ```plaintext 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. ---
**Author:** ![riking](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/riking/32/170938_2.png) [@riking](https://meta.discourse.org/u/riking)\ **Post date:** [2018年四月18日 22:25 UTC](https://meta.discourse.org/t/checking-user-profiles-for-inappropriate-links/84718/10 "2018-04-18T22:25:31Z")
> [@Mittineague](#): > > SELECT COUNT(`website`), COUNT(`bio_raw`) > FROM `user_profiles` Count gives you “where not null” for free 🙂 ---
**Author:** ![testingsoftware](https://avatars.discourse-cdn.com/v4/letter/t/f1d935/32.png) [@testingsoftware](https://meta.discourse.org/u/testingsoftware)\ **Post date:** [2018年四月18日 23:18 UTC](https://meta.discourse.org/t/checking-user-profiles-for-inappropriate-links/84718/11 "2018-04-18T23:18:17Z")
Thanks guys, I’ll try these to see the amount of results before getting the listing. > [@Mittineague](#): > > “approved” is like a boolean true / false field We have lots of not approved accounts (old accounts from software migration) and I wanted to see first the approved accounts. > The [Data Explorer](https://meta.discourse.org/t/32566?silent=true) 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! 😉 ---
**Author:** ![riking](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/riking/32/170938_2.png) [@riking](https://meta.discourse.org/u/riking)\ **Post date:** [2018年四月20日 01:31 UTC](https://meta.discourse.org/t/checking-user-profiles-for-inappropriate-links/84718/12 "2018-04-20T01:31:40Z")
> [@testingsoftware](#): > > better keep queries simple then? 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. > [@testingsoftware](#): > > We have lots of not approved accounts (old accounts from software migration) and I wanted to see first the approved accounts. In that case - try ```plaintext SELECT COUNT(website), COUNT(bio_raw), approved FROM user_profiles GROUP BY approved ``` 🙂 ---
**Author:** ![system](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/system/32/443519_2.png) [@system](https://meta.discourse.org/u/system)\ **Post date:** [2020年九月4日 05:09 UTC](https://meta.discourse.org/t/checking-user-profiles-for-inappropriate-links/84718/13 "2020-09-04T05:09:31Z")