#Aggregation / Math in Views

114 messages · Page 1 of 1 (latest)

late pelican
#

Merry Christmas all! I got my database running (https://discord.com/channels/856567261900832808/1321006276955869227) yesterday and now I'm working on views. However, the Django tutorials are kinda... lackluster? There's a lot of references to the shell but barely any examples in the code itself. So I don't know where to look.

I have this set up in views.

    thisplayer = get_object_or_404(PlayerList, pk=playerID)
    return render(request, "GHLWebsiteApp/player.html", {"playerID": thisplayer})```

In table *SkaterRecords*, which references *PlayerList*, there are 20 different stats that I'd like to show, with an additional count. So I need to show all the stats for playerID by using a count function for **GP**, sum function for most stats like **Goals**, and also average functions for certain stats like **Takeaways**. My question is, where does that go, and/or does it have to be passed through the player function?

(I will be using Javascript later to restrict SkaterRecords even more by seasonID, from a totally different table)
wraith nova
#

Well, the most straight and unsophisticated way is right there in the view

#

You are using quite confusing naming here and lacking actual model code it's hard to make a more concrete suggestion

late pelican
wraith nova
#

Sorry, but explanation of what? Aggregation?

late pelican
#

For lack of a better term, yep. All the tutorials I found, including on the django site, just talk about it in the shell, but not hard coded in views. so it's confusing me a bit.

late pelican
wraith nova
#

code in shell is no different from code in view

#

only difference that views will send values to render

late pelican
# 
    ea_player_ID = models.IntegerField(primary_key=True)
    username = models.CharField(max_length=16)
    current_team = models.ForeignKey(TeamList, on_delete = models.CASCADE, blank=True, null=True)
    def __str__(self):
        return self.username

class SkaterRecords(models.Model):
    ea_player_ID = models.ForeignKey(PlayerList, on_delete = models.CASCADE)
    game_ID = models.ForeignKey(Games, on_delete = models.CASCADE)
    ea_club_ID = models.ForeignKey(TeamList, on_delete = models.CASCADE)
    position = models.ForeignKey(Positions, on_delete = models.CASCADE)
    build = models.ForeignKey(Builds, on_delete = models.CASCADE)
    goals = models.PositiveSmallIntegerField()
    assists = models.PositiveSmallIntegerField()
    hits = models.PositiveSmallIntegerField()
    plus_minus = models.SmallIntegerField()
    sog = models.PositiveSmallIntegerField()
    shot_attempts = models.PositiveSmallIntegerField()
    deflections = models.PositiveSmallIntegerField()
    ppg = models.PositiveSmallIntegerField()
    shg = models.PositiveSmallIntegerField()
    pass_att = models.PositiveSmallIntegerField()
    pass_comp = models.PositiveSmallIntegerField()
    saucer_pass = models.PositiveSmallIntegerField()
    blocked_shots = models.PositiveSmallIntegerField()
    takeaways = models.PositiveSmallIntegerField()
    interceptions = models.PositiveSmallIntegerField()
    giveaways = models.PositiveSmallIntegerField()
    pens_drawn = models.PositiveSmallIntegerField()
    pims = models.PositiveSmallIntegerField()
    pk_clears = models.PositiveSmallIntegerField()
    poss_time = models.PositiveSmallIntegerField()
    fow = models.PositiveSmallIntegerField()
    fol = models.PositiveSmallIntegerField()
    def __str__(self):
        return str(self.ea_player_ID) + " Game " + str(self.game_ID)```
#

That's what I have so far.

#

at least, relevant for my question.

wraith nova
#

don't append _ID to ForeignKey fields, that's incorrect from django standpoint

#

So yeah, you want to run aggregation on PlayerList object (what is "player list"?)

#

Do you know code for aggregations you need?

late pelican
#

I'll change the _ID's shortly then.

To a degree. I've only seen first level, not second level, aggregations in tutorials too. As in, I'm counting all the SkaterRecords that have the ea_player_ID from the PlayerList object

wraith nova
#

Yes, that's what you want it seems

#

you need to access player_list SkaterRecords set via reverse relation and then runaggregate on it

late pelican
#

reverse relation is probably exactly the terminology I was looking for. Thanks!

wraith nova
#

related_name is an attribute ForeignKey that controls hove reverse relation is named on target model

#

by default it appends _set to lowercased model name

#

so playerlist.skaterrecords_set or something in your case

#

not sure if it adds _ in between

late pelican
#

would something like this work?

    player = get_object_or_404(PlayerList, pk=playernum)
    allskatergames = get_object_or_404(SkaterRecords, ea_player_num=playernum)
    skater_gp = allskatergames.objects.aggregate(Count("game_num"))
    skater_g = allskatergames.objects.aggregate(Sum("goals"))
    context = {"playerID": player, "skater_gp": skater_gp, "skater_g": skater_g}
    return render(request, "GHLWebsiteApp/player.html", context)```
wraith nova
#

generally - no, get_object_or_404 expects 1 or 0 objects

late pelican
#

what if I change that second line to
allskatergames = SkaterRecords.objects.get(ea_player_num=playernum)

wraith nova
#

as I shown above you access records via player

wraith nova
#

it's simply longer and removes room to optimize the query

late pelican
#

I see. Thanks!

#

so then

    allskatergames = player.skaterrecords_set.all()
    sk_gp = allskatergames.aggregate(Count("game_num"))
    sk_g = allskatergames.aggregate(Sum("goals"))```
late pelican
#

hmm. Now I'm having problems interpreting that data on the html side lol

late pelican
#

Never mind, figured it out that the aggregation passes a dictionary instead of just a value!

wraith nova
#

You can run several aggregations in same aggregate

#

they return a dictionary of results you send as is or update the context for render

# 
  player = get_object_or_404(PlayerList, pk=playernum)
  aggregate_dict = player.skaterrecords_set.aggregate(Count("game_num"), Sum("goals"))
  context = {"player": player}
  context.update(aggregate_dict)  # merge dicts
  return render(request,"my.html", context)
late pelican
#

That’s a lot simpler than what I was doing. Does the Sum function allow multiple arguments or do I have to add more Sum arguments to aggregate?

wraith nova
late pelican
#

I absolutely know I'm doing this wrong, but my brain is fried so I need an extra hand.

I have the aggreation leaders_goals below. This is aggregating from the SkaterRecords model. Each one of those models references a single player id that's in the PlayerList model. That model has a current_team field which references TeamList. I need to pull the player's club_abbr from their appropriate TeamList.

The aggregation works fine, it's when we get into the for loop that things break down. Maybe it's just a different primary key since it's a QuerySet?

def leaders(request):
    leaders_goals = SkaterRecords.objects.filter(game_num__season_num=seasonSetting).annotate(numgoals=Sum("goals")).order_by("-numgoals")[:10]
    .....
    for player in leaders_goals...:
        playerobj = PlayerList.objects.get(pk=player)
        teamabbr = playerobj.club_abbr
        player.append({"teamabbr": teamabbr})
    context = {
        "leaders_goals": leaders_goals,
        ...
    }
    return render(request, "GHLWebsiteApp/leaders.html", context)
#

My brain is so fried looking at this that I can't even visualize the data so I don't even know where to begin.

true adder
#

If anything, it might be more clear for both you and us if you renamed your models 🙂

late pelican
true adder
#

You will need to make and run migrations after renaming, yes

#

Django should be able to detect the renamed models. (Maybe do just one at a time 🤔)

late pelican
true adder
#

Or you could create a empty migrations file and just manually add a lot of migrations.RenameModel operations.

#

Like this

late pelican
#

One sec, finishing a page real fast. Then I'll try to migrate one at a time.

#

Ah - I see now why it didn't work last time. The admin.py page was throwing an error.

true adder
#

You will have to change all imports and references, yes 🤔 Sometimes the IDE can do it for you, sometimes not.

late pelican
#

Okay, cleaned up:

def leaders(request):
    leaders_goals = SkaterRecord.objects.filter(game_num__season_num=seasonSetting).annotate(numgoals=Sum("goals")).order_by("-numgoals")[:10]
    ...
    for player in leaders_goals, ...:
        playerobj = PlayerList.objects.get(pk=player)
        teamabbr = playerobj.club_abbr
        player.append({"teamabbr": teamabbr})
    context = {
        "leaders_goals": leaders_goals,
       ...
    }
    return render(request, "GHLWebsiteApp/leaders.html", context)```
#

I have the aggreation leaders_goals below. This is aggregating from the SkaterRecord model. Each one of those models references a single player id that's in the Player model. That model has a current_team field which references Team. I need to pull the player's club_abbr from their appropriate Team.

true adder
#

What re you adding team abbreviations to a list that already contains players?

#

No, wait, you are adding it to ....

#

This code is just confusing 🤔

late pelican
#

No, that's just me that's confused

#

SkaterRecord references Player references Team. I need to get a value from Team for each player in leaders_goals. The for loop was my own attempt and I just gave up halfway through.

true adder
#

(i am at the end of a 20 hour day, so i am not the best at reading now). But cleaning up model and attribute names (in separate migrations) will make helping you easier 😅. And sometimes, just asking for what data you want out and not giving a half-finished example may be better

#

Is the source on GitHub/simmilar?

late pelican
#

Yes, unlisted. I can share.

true adder
#

You can have unlisted repos? 🤔

late pelican
#

No, I'm just tired and thought I unlisted it somehow.

true adder
#

Just so you know, you have committed a database. Hopefully just test/non sensitive data?

late pelican
#

yep, all test

true adder
late pelican
#

< Installing... >

#

failing something during install. I'm missing some sort of C dependency. Somehow. lol

true adder
#

But, 21 hours -> 🚿 -> 🛌

late pelican
#

Get some sleep!

true adder
#

Someone else might take a look, if not i may be awake in 8-12 hours 😂

true adder
late pelican
#

I want you to know I had a dream about you helping me out last night. haha

true adder
#

Add a requirements.txt with

Django
djangorestframework
django-cors-headers

# pyparsing
# pydot
# django-extensions

to make it easier for others to test/help 🙂

#

There we go

#

python ./manage.py graph_models -g -o GHLWebsiteApp.png --arrow-shape normal GHLWebsiteApp

true adder
true adder
#

So, you are getting all the top ten SkaterRecord by the game's season ...

#

but since goals is on SkaterRecord, i don't 100% understand what that annotation is for ? 🤔

#

do you want to group them by season and then get the goals per season ?

#

so leaders_goals will just be a queryset of all SkaterTecords for season seasonSetting, with a extra attribute numgoals that is == goals if i understand correctly.

#

Then you are looping that and getting players ..... but it is not a list of players? I think you just want

 for something in leaders_goals:
        playerobj = something.ea_player_num
#

Then you will get the assosiated PLayer object from the SkaterRecord

#

This should also make it clear that SkaterRecord.ea_player_num should probably be named SkaterRecord.player - when dealing with the ORM you do not have to think about if something is a id or something, just think of it as the opbject it is linking to.

#

The same is true for game_num, (probably just name it game) and ea_club_num (probably just name it team?)

#

This has become more of a "name your fields and models properly and everything is so much easier to understand" than a "aggreagation/math" thread 🤣

#

To get from what shop i bough an item in, i just move from OrderItem to Shop like <orderitem_object>.order.shop.name

#

Now i will go and do something else while you get some time to read and digest my wall of text 😅

late pelican
#

haha

late pelican
true adder
#

Externally as in "directly from the database" or from a API ?

late pelican
#

external API

true adder
#

then what they are called in the database should not matter(to much)

late pelican
# true adder do you want to group them by season and then get the goals per season ?

The aggregation in the first line works the way I want it to. It's pulling all the information from SkaterRecord and it's showing on my page perfectly fine. I'm getting the top 10 goal scorers of the current season.

See: this pic of the site

But, I also want to add a column in there (currently labeled TEAM on the website) that has multiple relations: it' would have to go through the Player to the club_abbr which is in the Team table. That's what I'm trying to do in the for loop

Imgur
#

so would it just be teamabbr = player.ea_player_num.current_team.club_abbr

#

Nope, because then I get File "F:\GHL-Website\GHLWebsiteDjango\GHLWebsiteApp\views.py", line 35, in leaders teamabbr = player.ea_player_num.current_team.club_abbr ^^^^^^^^^^^^^^^^^^^^ AttributeError: 'QuerySet' object has no attribute 'ea_player_num'

#

hm.. So you can't combine QuerySets. so do I need to add a .values() to the original QS?

#

hmmmm

#

I'm getting the impression maybe this should just be a separate API to pull the team from. Or something like that.

wraith nova
late pelican
wraith nova
#

alternatives are either getting single object (get() first() [0] etc) or operate on set (e.g in loop)

true adder
late pelican
#

Yep.

late pelican
#

@true adder any chance you work with JS at all? I've got a React JS in my static files but I'm not sure why it's not running.

Never mind, I've decided to scrap React entirely and just work through Django and JS