function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion

Trigger/Batch with two elements as key!

Hello everybody and especially sports lovers out there!

One of our clients is actually a major sports league in France! and at the moment, we're developping a system based on salesforce to manage all the teams, matches, statistics... etc. So here's my question:

I'm creating a batch that launches every week. this batch must update a player statistics (points, red cards, subtitutions...etc) per competition. the data is taken from a custom object called "composition equipe" that has all the data related to the player and then we must put the data per player AND per competition in a new custom object called "statistique joueur competition" (joueur= player) 

To furthur explain, here's a scenario:

Let's say a player Mike scores 3 points today and 4 points next week in a competition called "league". The same Mike also scores 2 points today and 3 next week in a competition called "friendly match"

In the end, we must have in our new object "statistique joueur competition" the following records: "Mike League. 7 points" and "Mike freindly match. 5 points"

so my trouble is to find the key that combines the player and the competition together. I tried nested queries but the system only allows 2 levels of queries and there are 4 levels between my objects.

Here's a quick topology of the objects in question:

Player= contact
Parent child relationships between objects: Competition -> Match -> Composition -> Composition Equipe

I hope that it's clear for everybody. if it's not, I'd be happy to explain furthur more.

I'm also new to coding by the way!

Thanks a lot for your help :)
In this case, query only parent records in the start method. Child records should be fetched in execute methods for processing.
So you're just looking to get player totals by competition?  Try something like this:
List<statistique_joueur_competition> newStats = new List<statistique_joueur_competition>();
For(AggregateResult ar : [SELECT player_name__c, competition__c, sum(points__c) totalPoints
                           FROM composition_equipe__c
                           GROUP BY player_name__c, competition__c]){
statistique_joueur_competition newStat = new statistique_joueur_competition(player_name__c = ar.get('player_name__c'), points__c = ar.get('totalPoints'), competition__c = ar.get('competition'));

insert newStats
So you get the scores with an aggregated query, which because you're grouping by player name then competition will give you a list of results like this:
player_name 	competition 	totalPoints
player1 	    friendly     	5
player1 	    league         	7
player2 	    friendly     	1
player2 	    league         	8

so each row is a unique score per player/competition