I'm constantly amazed at how the "little things" get you in coding, and how what seems like a straightforward task is, in fact, not so straightforward. Although sometimes the solution, in the end, is rather elegant, obvious and simple.

In the new CelestialVale pets can get buffs from various sources, and with the exception of ONE type of buff, these buffs all stack. I offload processing these buffs into Pet::PopulateBuffs() and upon calling that, the pet is buffed and ready to use. The problem was dealing with buffs that "tick" and don't stack with others of the same type.

Because these are ticking buffs that operate out of cron, I wanted to confine the buffs to a single database query and not use the Pet class to calculate their effects. This meant that when a pet was given a Trinket of a certain type that the Pet::GiveTrinket() method had to calculate which buff to preserve based off what other trinkets the pet had of that type, and to retain the buff granted by the highest ranked one.

ACKTUALLY proved to be more complicated than originally expected and it took me several hours to conjure a solution. The solution

The solution ended up being rather simple and elegant. When the Pet object is populated it populates the pet's trinkets. The first thing GiveTrinket() does is give the pet the new trinket in that trinket spot.

Within GiveTrinket() is a hard-coded item_ID => buff_granted for this one special trinket type. When the code detects this trinket type has been given it deletes all buffs of that type from the buff table.

Then the pet's existing trinkets are checked against the array, checking for that trinket ID. It pulls out each one that matches and puts this data into a holding array. This holding array is then sorted and the first key/value pair gives us the highest active buff we should use. This buff is inserted into the buff table normally.

Now there is only 1 buff of this type active and the proper cron will pick it up and process it from a single JOIN query.

This method requires no additional queries over regular buff handling and an additional 15 lines of code to support sorting through the trinkets to extract the best one to use.