Why soft deleted entities appear in query results?

There is a little trick using soft delete tables and queries in laravel:

When we create something like

$objCars = Car::where("color","blue");

The system executes something like that:

SELECT
  *
FROM
  cars
WHERE
  deleted_at IS NULL
AND
  "color" = 'blue'

So far, so good. But, when we apply the "orWhere" method, something funny happens

$objCars = Car::where("color","blue")->orWhere("color","red");

The system will execute something like that:

SELECT 
  * 
FROM
  cars
WHERE
  deleted_at IS NULL 
AND 
  "color" = 'blue'
OR
  "color" = 'red'

This new query will return all the car where deleted_at is null and the color is blue OR if the color is red, even if the deleted_at is not null. It is the same behavior of this other query, what show the problem more explicitly:

SELECT 
  * 
FROM
  cars
WHERE
  (
    deleted_at IS NULL 
  AND 
    "color" = 'blue'
  )
OR
  "color" = 'red'

To escape from this problem, you should change the "where" method passing a Closure. Like that:

$objCars = Car::where(
  function ( $query ) {
    $query->where("color","blue");
    $query->orWhere("color","red");
  }
);

Then, the system will execute something like that:

SELECT
  *
FROM
  cars
WHERE
  deleted_at IS NULL
AND
  (
    "color" = 'blue' 
  OR
    "color" = 'red'
  )

This last query, searches for all cars where deleted_at is null and where the color can be or red or blue, as we was want it to do.

Sometimes, you will get the soft deleted table entries with get() even with eloquent and protected $softDelete = true;.

So to avoid this problem, use

...->whereNull('deleted_at')->get();

For example, this query will fetch all rows including soft deleted.

DB::table('pages')->select('id','title', 'slug')
                                   ->where('is_navigation','=','yes')
                                   ->where('parent_id','=',$parent_id)
                                   ->orderBy('page_order')
                                   ->get();

So the proper method is,

DB::table('pages')->select('id','title', 'slug')
                                   ->where('is_navigation','=','yes')
                                   ->where('parent_id','=',$parent_id)
                                   ->whereNull('deleted_at')
                                   ->orderBy('page_order')
                                   ->get();

The soft deleting feature works when using Eloquent. If you are querying the results with query builder you will eventually see all the records trashed and not trashed.

It is not clear in the current docs of Laravel 4, but seeing that the concept of soft deleting just appears under Eloquent ORM - Soft Deleting and not under Query Builder, we can only assume that: soft delete only works with Eloquent ORM.