This is my personal web space. Please forgive the broken articles, Markdown/Textile are not very forgiving after multiple blog software changes!

Eloquent upsert() requires a UNIQUE index on MySQL

Posted on: 2021-10-29 16:19:48

tl;dr - upsert() acts different based on your DB. MySQL requires that a UNIQUE INDEX exist for the combination of column you are wanting upsert to work on.

Where I can, I've been using upsert() as a way to make many queries into (usually) a single query.

Recently, I had a situation where a function in our code base was essentially doing this:

foreach ($anArray as $newModels) {

This operation was wrapped in a DB::beginTransaction() / DB::commit().

When I finally got around to re-writing it, it was much cleaner and of course looked something like this:

   ['key_one', 'key_two', 'key_three'].
   ['col1', 'col2']

I ran the test suite (it passed), pushed and went to sleep.

When I woke up the next morning, I saw a number of errors and a large number of records being generated. Obviously, something was not right in the world of upsert.

After digging into things and exploring how MySQL "compiles" upserts, it turns out that this:

   ['key_one', 'key_two', 'key_three'].
   ['col1', 'col2']

Also could be written like this:

   ['col1', 'col2']

That second set of parameters is never used in the function:

public function compileUpsert(Builder $query, array $values, array $uniqueBy, array $update)
    $sql = $this->compileInsert($query, $values).' on duplicate key update ';

    $columns = collect($update)->map(function ($value, $key) {
        return is_numeric($key)
            ? $this->wrap($value).' = values('.$this->wrap($value).')'
            : $this->wrap($key).' = '.$this->parameter($value);
    })->implode(', ');

    return $sql.$columns;

$uniqueBy isn't used. It is in the SQLite version, but not here.

After cleaning up the DB and adding an index, the upsert will work as expected.

Continue reading...

Notes on writing reproducable Laravel tests

Posted on: 2021-09-22 11:46:22

Just writing some notes down on things that have made my life easier:

Testing timestamps

This has kicked my butt so many times. Started using Carbon::setTestNow(now()) and Carbon::now() to test timestamps coming back from API calls. You may ask: why do you care? This is beacuse I want to know the format of what comes back. I suppose I could just write a custom assertion that lets me know if the timestamp is within, say, the last second or something like that and matches a valid format. This might work. But I like being precise.

Fully test the "shape" of responses

One of the big things that I've come to rely on tests for is to make sure that I don't accidentally break stuff when refactoring or making changes.

All of our new code is testing such that the entire structure of the response is tested to make sure it's good. We also then test some of the data points to make sure things are where they should be.

$this->actingAs($user, 'api')
        'data' => [
    ->assertJsonPath('data.id', $model->id)

A couple of times I refactored something and ended up accidentally forgetting (or renaming a value) because a test didn't cover it. APIs are contracts. They should always return the expected data.

Testing the DB, too.

I've also gotten into a habit of testing the DB after certain API calls, too. This is helpful for testing both what should be there and also for what shouldn't be there. Yes, the tests are longer. This isn't as necessary as much anymore as we've started pulling stuff out of controllers, but on older code this can be valuable, too.

Testing with no internet connection

Turns out we've accidentally had tests that reached out to production systems to pull data. Testing while offline finds these tests and allows you refactor/abstract them.

Knowing when to use fake/random values

This is actually a big one for me. For several weeks I fought with tests that would randomly fail.

Turns out the problem was that the models being tested had a flag on their factory that was choosing a random value. This random value determined if the user had an active subscription.

Now, we assume free and require paid using a factory state: User::factory()->paid()->create(). This ensures that the tests are clear and we've stopped running into this problem. However, here are some fields you likely shouldn't be using random data for:

  • status (paid_status, post status)
  • state (sending, processing, etc.)
  • type (public, private, shared posts, etc.)
  • flags (reported, archived, hidden)
  • tags (any tags, really)
Continue reading...

Modifying Eloquent's relations upon loading

Posted on: 2021-09-01 14:06:48

In our application's backend, we have a model that is very large to handle our Training programs. Part of the reason it's so large is because there are a lot of layers to give flexibility to training programmers. For a normal user who is doing a workout, their session looks like this:

              /     \                                                   
,----------------.  ,------.      
|Training Program|  |Groups|      
|----------------|  |------|      
`----------------'  `------'      

One of the things we really have been wanting to implement is the ability to "swap" a Movement for another. If you've ever done a work out, especially an advanced one, a good trainer will normally start with a base workout and then will scale it to an individual. If you've ever done Crossfit, you've done this.

In the end, this is essentially what we are wanting to accomplish:

$swap = [ 
   'original_movement' => 'push-up',
   'new_movement' => 'knee push-up'

// true, this is the old one.
$workout->groups[0]->movements[0]->movement === 'push-up';
$workout->groups[0]->originalMovements[0]->movement === 'push-up';


// now is the new one but original is unchanged.
$workout->groups[0]->movements[0]->movement === 'knee push-up'; 
$workout->groups[0]->originalMovements[0]->movement === 'push-up';

We have a lot of code that leverages Eloquent's relations to load and build responses for our API. However, the API isn't the only thing that will need this information. Ultimately, we cannot simply depend on the API/resources to format a response for a user, we need to be able to ensure that for a given point in time, a user's workout representation can be easily generated.

So updating our resource models to somehow transform the data is out.

When thinking about it from a DX perspective first, this is how I originally want to be able to do things:

  • When loading a user's training Session, I want their swaps to automatically apply.
  • When loading a Workout (outside of a session), swaps should be able to be selectively applied (e.g. $workout->setSwaps(...)).
  • A minimum amount of changes should be done to the rest of the API except for adding the necessary attributes to the respective resources.

A trait and an override gets us most of the way

What I ended up doing was creating a trait AppliesMovementSwaps that exposes 3 different implementation points:

  • Setting $inheritSwapsRelation denotes which relation may contain swap information. This allows setSwaps() on a model to talk to a relation to grab their swap information.
  • Setting $appliesSwapsRelation denotes a "child" relation which may want to take action when adding swaps from "above".
  • Classes can implement applySwaps() to take action when swaps are added. More on this in a moment.

So now the classes look like this (they all use AppliesMovementSwaps).

|Session                          | 
|$appliesSwapsRelation = 'workout'| 
|Workout                           |
|$appliesSwapsRelation = 'groups'  |
|$inheritsSwapsRelation = 'session'|
|Groups                            |
|$inheritsSwapsRelation = 'workout'|


  • The Groups model (where the swaps are applied) had the relation that needed to be modified (exercises) duplicated so that the original is always available (e.g. originalExercises).
  • A pointer to the originalExercises relation was created.

This was done so that one relation always points to what was programmed for the workout. exercises is what the user sees and that's the the relation that gets modified.

Finally, (and most importantly) I overrode setRelation on the Group model so that I could apply the transformation and "swap" out the records. This allows the relation to be modified and continue to work as a regular relation even though we are doing things with it.

public function setRelation($relation, $value)
    if ($relation === 'movements' && count($this->getSwaps()) > 0) {
        $value = $this->movementsWithSwaps($value);

    return parent::setRelation($relation, $value);

This all should work in theory. In practice, relations make things a little bit harder.

Modifying relations with setRelation

The above mostly works. But it doesn't in some instances. Here is an example:

// Load workout, apply swaps.
$workout->groups[0]->movements[0]->movement === 'knee push-ups';

// This doesn't.
$workout->groups[0]->movements[0]->movement === 'push-ups';

// Wait, this works?
$workout->groups[0]->movements[0]->movement === 'knee push-ups';

... what is going on?

Eloquent + setRelation

When Eloquent is accessing a relation:

                     ^^^^^^^^^ This is a relation.
          ^^^^^^ This is too, but not relevant.

It ends up calling a small shrub of functions:


It is here within getRelationshipFromMethod() where the issue is manifested:

protected function getRelationshipFromMethod($method)
    $relation = $this->$method();

    // <snip>...

    return tap($relation->getResults(), function ($results) use ($method) {
        $this->setRelation($method, $results);

Huh. So the first time you load a relation, it gets tap()'d and during the tap setRelation gets called. This means that the first access doesn't see the modified values that we do in setRelation only on second access does it pull from the relation value stored on the model. Shucks.

A hack to make it work

To make it work, we ended up hacking applySwaps() to force the reload of those certain relations when swaps were being applied. This means the relation value is primed for any access.

So far, this works really well. It feels hackish, but all of the tests (nearly 2 dozen) are passing so... yay?


Here's things that could make this easier:

  • Add the ability to transform() a relation before it gets loaded. This would remove the need to override setRelation. This seems clean and could be easy to do.
  • Add support for a get{Relation}Relation() method? I looked at Laravel Relationship Events but it didn't support the transformation.
  • Rewrite the models to simply support a getter. This is probably the "right" way but would likely have required a lot more time since right now everything is handled using relations.
Continue reading...

Databases inspection while debugging tests

Posted on: 2021-05-04 13:21:39

While debugging, I use a local MySQL database. Sometimes, I want to inspect the contents of the DB while the tests are running to I can check out the state of things.

This is how I do it.

Add a breakpoint to your code, and then open up your query environment of choice:

set tx_isolation="READ-UNCOMMITTED";

Now run whatever queries you want to inspect the DB.

The tx_isolation change will only impact the current session.

Continue reading...

Don't put services in a Laravel Console command class constructor

Posted on: 2021-04-20 22:33:15

It will attempt to resolve the component, and that might not take place when you want it to.

You know, for instance, before your cached config gets cleared and composer dumpautoload tries to run and barfs.

Continue reading...