Testing sql-queries can be challenging. Not only do you need to have a database to test against, you also need to seed it data to have something to test with.

Laravel, however, makes this process much easier with it's brilliant testing tools and helpers. You can spin up an in-memory sqlite database with almost no setup, seed the database using seeders and factories and assert against the database using helpers such as assertDatabaseHas() and assertDatabaseMissing().

Testing long queries is still quite cumbersome though. Consider the following example.

Let's say I have an ecommerce site that sells.. Skateboards and Jimi Hendrix records (because how awesome wouldn't that be?). Every day I want to get all the skateboards that I should ship that day.

I write the following eloquent statement

class GetSkateboardsThatShouldBeSentToday() 
{
    public function __invoke() 
    {
        return Order::query()
            // It should have a product that is a skateboard and is in stock.
            ->whereHas('product', function (Builder $query) {
                return $query
                    ->where('type', 'skateboard')
                    ->where('in_stock', true)
            })
            // We only want to get skateboards that we should ship. Not skateboards that the customer wants to collect in store.
            ->whereHas('shipping', function (Builder $query) {
                return $query
                    ->where('method', 'send by mail')
            })
            // It should be paid
            ->whereNotNull('paid_at')
            // It should not be cancelled
            ->whereNull('cancelled_at')
            // The order should have been placed today.
            ->whereBetween('created_at', [Carbon::now()->startOfDay(), Carbon::now()->endOfDay()])
            ->get()
    }
}

I have used the command-pattern above to wrap the query in a single method class. Read more about that pattern and how to use it to clean up your laravel code here

Let's ignore the fact that the logic behind the query is quite simplified and flawed, and instead focus on making it testable :)

A note about scopes. If you are like me, your fingers are itching to extract some of the logic above into query scopes. We then get reusable pieces that we can use in several places, and abstract away implementation details such as column names and focus more on what should be done than how it's done. It does not, however, solve the testability issue of the query as a whole. Therefore I'll keep the query scopeless to reduce the overhead of introducing scopes.

That's quite a lot we need to test. Every where-statement needs to be tested, and some of them will require more than one test each. The whereBetween for example needs to be tested that the date is not before or after the given interval, which is today.

Let's first write a happy path test to check that it can return something.

/** @test */
public function it_can_get_skateboards_that_should_be_shipped_today()
{
    $order = Order::factory()
        ->has(
            Product::factory()
                ->state([
                    'type' => 'skateboard',
                    'in_stock' => true,
                ])
        )
        ->has(
            Shipping::factory()
                ->state([
                    'method' => 'send by mail'
                ])
        )
        ->create([
            'paid_at' => Carbon::now(),
            'cancelled_at' => null,
            'created_at' => Carbon::now(),
        ]);

    $foundOrders = (new GetSkateboardsThatShouldBeSentToday)();

    $this->assertCount(1, $foundOrders);
    $this->assert($foundOrders[0]->is($order));
}  

With the happy path in place, let's start testing to pick apart each where clause to make sure everything works as expected, starting with the whereNotNull('paid_at')

/** @test */
public function unpaid_orders_are_not_returned()
{
    $unpaidOrder = Order::factory()
        ->has(
            Product::factory()
                ->state([
                    'type' => 'skateboard',
                    'in_stock' => true,
                ])
        )
        ->has(
            Shipping::factory()
                ->state([
                    'method' => 'send by mail'
                ])
        )
        ->create([
            'paid_at' => null,
            'cancelled_at' => null,
            'created_at' => Carbon::now(),
        ]);

    $foundOrders = (new GetSkateboardsThatShouldBeSentToday)();

    $this->assertCount(0, $foundOrders);
}

This certainly works, but we have a lot of duplicated code here. Not only that, it is also hard to see what actually differs this set up to the test above. To make this better, we can move the set up of a happy path order to its own method.

// in the test file.

protected function createOrder() 
{
    return Order::factory()
        ->has(
            Product::factory()
                ->state([
                    'type' => 'skateboard',
                    'in_stock' => true,
                ])
        )
        ->has(
            Shipping::factory()
                ->state([
                    'method' => 'send by mail'
                ])
        )
        ->create([
            'paid_at' => Carbon::now(),
            'cancelled_at' => null,
            'created_at' => Carbon::now(),
        ]);
}

We can now update the tests as follows:

/** @test */
public function it_can_get_skateboards_that_should_be_shipped_today()
{
    $order = $this->createOrder();

    $foundOrders = (new GetSkateboardsThatShouldBeSentToday)();

    $this->assertCount(1, $foundOrders);
    $this->assert($foundOrders[0]->is($order));
}  

/** @test */
public function unpaid_orders_are_not_returned()
{
    $unpaidOrder = $this->createOrder();
    $unpaidOrder->update(['paid_at' => null]);

    $foundOrders = (new GetSkateboardsThatShouldBeSentToday)();

    $this->assertCount(0, $foundOrders);
}

protected function createOrder() 
{
    return Order::factory()
        ->has(
            Product::factory()
                ->state([
                    'type' => 'skateboard',
                    'in_stock' => true,
                ])
        )
        ->has(
            Shipping::factory()
                ->state([
                    'method' => 'send by mail'
                ])
        )
        ->create([
            'paid_at' => Carbon::now(),
            'cancelled_at' => null,
            'created_at' => Carbon::now(),
        ]);
}

This reads much better, but we can still improve. One thing that bothers me is the extra database call we have to make to update the paid_at column. To eliminate this call, we can return the factory instance instead of a created object, and modify the object before creation.

/** @test */
public function it_can_get_skateboards_that_should_be_shipped_today()
{
    $order = $this
        ->createOrder()
        ->create();

    $foundOrders = (new GetSkateboardsThatShouldBeSentToday)();

    $this->assertCount(1, $foundOrders);
    $this->assert($foundOrders[0]->is($order));
}  

/** @test */
public function unpaid_orders_are_not_returned()
{
    $unpaidOrder = $this
        ->createOrder()
        ->create(['paid_at' => null]);

    $foundOrders = (new GetSkateboardsThatShouldBeSentToday)();

    $this->assertCount(0, $foundOrders);
}

protected function createOrder() 
{
    return Order::factory()
        ->has(
            Product::factory()
                ->state([
                    'type' => 'skateboard',
                    'in_stock' => true,
                ])
        )ss
        ->has(
            Shipping::factory()
                ->state([
                    'method' => 'send by mail'
                ])
        )
        ->state([
            'paid_at' => Carbon::now(),
            'cancelled_at' => null,
            'created_at' => Carbon::now(),
        ]);
}

We switched from 'create' to 'state' in the createOrder method, and handled the creation in the tests themselves instead. It turns out that you can modify the value of the same field multiple times in laravels factories. The last value set is the one that will be used.

You might have noticed that we do not test the inverse in the ‘unpaid_orders_are_not_returned’ test. We test that unpaid orders aren’t returned, but not that paid orders are. The reason for that is that it is already tested in the happy path test. Because we have that one, we only need to test the exceptions.

Now, let's test the rest of the query. The test that should make sure that no orders that have been cancelled will be very similar to the test that tests that the order has been paid, so let's hammer that out without the need for more explanation.

/** @test */
public function cancelled_orders_are_not_returned()
{
    $cancelledOrder = $this
        ->createOrder()
        ->create(['cancelled_at' => Carbon::now()]);

    $foundOrders = (new GetSkateboardsThatShouldBeSentToday)();

    $this->assertCount(0, $foundOrders);
}

Nice and clean!

Next up we're gonna tackle the product relation. The product type should be 'skateboard'. In the createOrder method we specify the type in the ->has() block. It turns out that you can override this by writing the same ->has() statement again for the relation. Like this:

public function only_orders_for_skateboards_should_be_returned()
{
    $jimiHendrixProductOrder = $this
        ->createOrder()
        ->has(
            Product::factory()
                ->state([
                    'type' => 'Jimi hendrix record',
                    'in_stock' => true,
                ])
        )
        ->create();

    $foundOrders = (new GetSkateboardsThatShouldBeSentToday)();

    $this->assertCount(0, $foundOrders);
}

Using the same method as we did with the fields, it's now very clear in the test what we are actually testing, with minimal duplicate code.

To be sure that everything in the set up is correct, you can add an assertion right after the order is created to make sure that the product type is in fact a Jimi Hendrix record.

Now, we test that only products in stock should be returned.

public function only_orders_with_products_in_stock_be_returned()
{
    $outOfStock = $this
        ->createOrder()
        ->has(
            Product::factory()
                ->state([
                    'type' => 'skateboard',
                    'in_stock' => false,
                ])
        )
        ->create();

    $foundOrders = (new GetSkateboardsThatShouldBeSentToday)();

    $this->assertCount(0, $foundOrders);
}

Easy peasy. The test for shipping type we can write in the same manner.

public function only_orders_that_should_be_shipped_is_returned()
{
    $shouldNotBeShipped = $this
        ->createOrder()
        ->has(
            Shipping::factory()
                ->state([
                    'method' => 'pick up in store'
                ])
        )
        ->create();

    $foundOrders = (new GetSkateboardsThatShouldBeSentToday)();

    $this->assertCount(0, $foundOrders);
}

Now it's time for the final where clause. We need a test that makes sure that only orders placed today should be returned. We need to test an order placed in the past, an order placed today as well as an order placed in the future (even though that should not be possible). Let's see what that would look like.

public function only_orders_placed_today_is_returned()
{
    Carbon::setTestNow('2020-06-01');

    $createdInThePassed = $this
        ->createOrder()
        ->create([
            'created_at' => Carbon::now()->subDay(),
        ]);

    $createdToday = $this
        ->createOrder()
        ->create([
            'created_at' => Carbon::now(),
        ]);

    $createdInTheFuture = $this
        ->createOrder()
        ->create([
            'created_at' => Carbon::now()->addDay(),
        ]);

    $foundOrders = (new GetSkateboardsThatShouldBeSentToday)();

    $this->assertCount(1, $foundOrders);
    $this->assertTrue($foundOrders[0]->is($createdToday()));
}

As you can see I've set up Carbon to think the current date is a set date in the past. When testing dates, it's for the most part a good idea to do this, as the date and time will change every time you run the tests (duuh..?) you might stumble across weird edge cases. For example if they run at midnight and the date actually changes from the time you create the model to the time you retrieve the models, your test will fail.

Summary

As you can see, using this method you can quite easily test complex queries in a clean way that makes the set up short and reads well. Another benefit is if the query should change in the future, you only need to update the createOrder method and the specific tests that test the thing that changed. All the other tests can be left unchanged, and will still work.

If this was real code in a real project, I would probably extract scopes for the business rules in the query, as well as create factory state methods for the same rules. That way the test does not have to be concerned with the implementation details of what actually makes an order paid and so on. I've left that out of this example to minimize the code needed to express the method.

I hope this post has helped you, and that you may have picked up some tricks to use next time you test your eloquent queries. If it did, or have questions - please reach out on twitter :) I go by @Isak_Berglind