Laravel Unit Testing, how to "seeInDatabase" soft deleted row?

Question

I'm working on a small unit test where I soft delete a row. To mark the test as successful I have to find that row with:

• a given ID and
• deleted_at column should not be null.

I can fulfil first condition - because obviously I know the ID.

Unfortunately I don't know how to tell seeInDatabase method that I expect deleted_at not to be null:

$this->seeInDatabase(
       'diary_note_categories',
       [
           'id' => 'a7e35ad0-6f00-4f88-b953-f498797042fc',
           'deleted_at' => null // should be is not null, like <> or != or whatever
       ]
 );

Any hints?

'deleted_at <>' => null breaks

'deleted_at' => ['!=' => null] breaks as well

Answer 1

I did it in this way:

$this->seeInDatabase('diary_note...',['id' => 'a7e35ad0'])
->notSeeInDatabase('diary_note...',['id' => 'a7e35ad0','deleted_at'=>null]);

So I'm checking in two steps

1. I check if there is a record with our id in the table
2. I check if there is no record with our id and deleted_at = null in the table

Answer 2

It's not currently possible. Both seeInDatabase and notSeeInDatabase just pass the array directly to the where method of the query builder and that doesn't understand how to deal with anything other than = when passed an array.

https://github.com/laravel/framework/blob/2b4b3e3084d3c467f8dfaf7ce5a6dc466068b47d/src/Illuminate/Database/Query/Builder.php#L452

public function where($column, $operator = null, $value = null, $boolean = 'and')
{
    // If the column is an array, we will assume it is an array of key-value pairs
    // and can add them each as a where clause. We will maintain the boolean we
    // received when the method was called and pass it into the nested where.
    if (is_array($column)) {
        return $this->whereNested(function ($query) use ($column) {
            foreach ($column as $key => $value) {
                $query->where($key, '=', $value);
            }
        }, $boolean);
    }

    // ...
}

Option 1 - Add the following code to your TestCase class which you extend your test cases from

Gist: https://gist.github.com/EspadaV8/73c9b311eee96b8e8a03

<?php
/**
 * Assert that a given where condition does not matches a soft deleted record
 *
 * @param  string $table
 * @param  array  $data
 * @param  string $connection
 * @return $this
 */
protected function seeIsNotSoftDeletedInDatabase($table, array $data, $connection = null)
{
    $database = $this->app->make('db');

    $connection = $connection ?: $database->getDefaultConnection();

    $count = $database->connection($connection)
        ->table($table)
        ->where($data)
        ->whereNull('deleted_at')
        ->count();

    $this->assertGreaterThan(0, $count, sprintf(
        'Found unexpected records in database table [%s] that matched attributes [%s].', $table, json_encode($data)
    ));

    return $this;
}

/**
 * Assert that a given where condition matches a soft deleted record
 *
 * @param  string $table
 * @param  array  $data
 * @param  string $connection
 * @return $this
 */
protected function seeIsSoftDeletedInDatabase($table, array $data, $connection = null)
{
    $database = $this->app->make('db');

    $connection = $connection ?: $database->getDefaultConnection();

    $count = $database->connection($connection)
        ->table($table)
        ->where($data)
        ->whereNotNull('deleted_at')
        ->count();

    $this->assertGreaterThan(0, $count, sprintf(
        'Found unexpected records in database table [%s] that matched attributes [%s].', $table, json_encode($data)
    ));

    return $this;
}

Option 2 - Install the following composer package

This composer package is the exact same code as above, but packaged up for Composer.

composer require kirkbater/soft-deletes

Then use it inside of your specific test class:

<?php

use Kirkbater\Testing\SoftDeletes;

class MyTestClass extends TestClass {

    use SoftDeletes;

}

Answer 3

This is an old question, but for those using more recent versions of Laravel (5.4 and above), there is now an assertSoftDeleted assertion: documentation.

So the answer to the original question would now be:

$this->assertSoftDeleted('diary_note_categories', [
    'id' => 'a7e35ad0-6f00-4f88-b953-f498797042fc'
]);