-1

I'm making a leaderboard for the leveling system my Discord bot has. The list will make a list of the people with most XP and order them from highest to lowest amount. I have already achieved this goal, but I can only show to user's ID next to their XP amount in the leaderboard. How can I turn this user ID into a username?

foreach (ulong n in DbContext.Experiences.OrderByDescending(x => 
x.XP).Select(x => x.ID))
{
    Context.Guild.GetUser(n).ToString()
}

var leaderboard = string.Concat(DbContext.Experiences.OrderByDescending(x => 
x.XP).Select(x => $"Level {x.LevelNumber} with {x.XP} xp 
    {//username must be here}\n"));

await ReplyAsync(leaderboard.ToString());
Austin T French
  • 5,022
  • 1
  • 22
  • 40
VAC Efron
  • 3
  • 1
  • 3

3 Answers3

1

What I would expect to see (Never looked at Discord if that's where the database structure comes from) would be a table for:

  • Guild
  • Experiences
  • Users

I would Expect dbContext.Users to have something like:

UserId | UserName

and then a Guilds table to have something like:

GuildId | GuildName | UserId

and Experiences to look something like:

ExperienceId | UserId

I'll continue to make some assumptions here:

Context.Guild.GetUser(n).ToString();

This to me looks like the an EF Core query which translates into SQL:

select UserId from Guild

Working from that, I see a few potential issues:

First, the Guild method returning only a string or long is weird. Return an object if that's your implementation.

More importantly, you can do this likely in 1 query:

in Sql:

Select g.GuildId, e.Experiece, u.UserId, u.UserName from Guild g
   left join Users u on g.UserId = u.UserId
   left join Experiences e on u.UserId = e.UserId
where g.GuildId = @myGuildId
order by e.Experience Desc

this would give you back rows like: 1 | 1500 | 10 | BitesSpoons 1 | 1450 | 51 | LostElbows 1 | 1121 | 98 | EarthSkyFire 1 | 990 | 15 | GoldenGoose

What I would do: Create a view class, and map it either with something like AutoMapper from a query, or when you materialize the instance:

Make a class:

public class LeaderView 
{
    public string UserName {get; set;}
    public long UserId {get; set;}
    public long GuildId {get; set; }
    public int Experience { get; set; }
}

And then a linq to Sql query like:

var leaders = from g in context.Guilds
join u in context.Users on g.UserId = u.UserId
join e in context.Experience on u.UserId  = e.UserId
select new LeaderView
{
    UserName = u.UserName,
    UserId = u.UserId,
    GuildId = g.UserId,
    Experience = e.Experience
};  

leaders = leaders.OrderByDescending(o => o.Experience);
return leaders.ToList();
Austin T French
  • 5,022
  • 1
  • 22
  • 40
  • Correct me if I'm wrong, but I think you're assuming I have the usernames stored in the database, which I have not. I already know how to get everyone's user ID, but I am looking to turn those user ID's into usernames. I can, however, select the first person on the list and get his username with this: `Context.Guild.GetUser(INSERT_ID)` But I am looking to do this for everyone on the list, and use the result in a message. – VAC Efron Mar 31 '19 at 11:07
  • I am. And is context not a database context? I Made a ton of assumptions not being familiar with discord – Austin T French Mar 31 '19 at 16:06
  • Yeah, DbContext is a database context. However I don't have the usernames stored in the database (or else it would be very simple haha). – VAC Efron Mar 31 '19 at 16:41
0

Assuming that you already have the desired User ID

Context.Guild.GetUser(THE_USER_ID_HERE).Username
This return the user's username if exists.

If your leaderboard is global (where the user might not be in the server in which the command is executed)
you can use client.GetUser(THE_USER_ID_HERE).Username instead, where client is your bot's current socket client.

(Or alternatively, access the Nickname property if you wish to display the user's name tied to the server instead)

WQYeo
  • 3,973
  • 2
  • 17
  • 26
  • I've tried this before and it works for one user. But I want it to get everyone's username, with only having their ID as reference. – VAC Efron Mar 31 '19 at 10:52
  • @VACEfron then loop through the list of their IDs, then fetch their usernames with the specified method (Passing it into a list to let you do what you want with it, or add it to the stringbuilder if you are planning to print out all of the user's name). – WQYeo Mar 31 '19 at 22:02
0

See comments in code:

//What exactly is this for loop meant to do? 
//You appear to be getting a user based on ID and doing nothing with that value.
//This should be removed
foreach (ulong n in DbContext.Experiences.OrderByDescending(x => 
x.XP).Select(x => x.ID))
{
   //While this is how to get a user by ID, you aren't actually doing anything with this once it's retrieved.
    Context.Guild.GetUser(n).ToString()
}

//You can simply fetch the username here, given that you have access to the ID
var leaderboard = string.Concat(DbContext.Experiences.OrderByDescending(x => 
x.XP).Select(x => $"Level {x.LevelNumber} with {x.XP} xp 
    {//username must be here}\n"));

await ReplyAsync(leaderboard.ToString());

Your modified code should look something like:

var leaderboard = string.Join("\n", DbContext.Experiences
                                             .OrderByDescending(x => x.XP)
                                             .Select(x => $"Level {x.LevelNumber} with {x.XP} xp {Context.Guild.GetUser(x.ID).ToString()}"));
await ReplyAsync(leaderboard);

NOTE: I've replaced string.Concat with string.Join as it's a more efficient String building method.

Anu6is
  • 2,137
  • 2
  • 7
  • 17