How can I query for all users with the default avatar?

James Hurff September 23, 2015

We have asked our user population to "change their avatar".  Is there a way for me to query all users with the default avatar assigned to their profile?

2 answers

1 vote
Jonas Andersson
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 24, 2015

Did some readin up.. This is how to display all users without a avatar picture:

select t1.lower_user_name from cwd_user t1 left join avatar t2 on t1.lower_user_name = t2.owner where t2.owner is null;

Enjoy!

1 vote
Jonas Andersson
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 24, 2015

If a user have not selected their own avatar they will not be in the JIRA avatar mysql-table. There is probably a nice nested query to see which users exist in the user table and cross reference it to the owner cell of the avatar table, but not being a great SQL ninja, i can only list the user that DID change their avatars by running

select distinct owner from avatar;

You should be able to do something like this:

select avatar.owner from avatar where avatartype='user' AND avatar.owner NOT IN (select cwd_user.lower_user_name from cwd_user);

Not sure where this breaks but if you have a DBA around maybe he can help you finetune it. Please post the answer here, it's good practice for my mysql-fu wink

 

Suggest an answer

Log in or Sign up to answer