Laravel Query Builder - Need to modify query result

Question

I created a very simple query but the laravel builder added additional query which I will like to remove.

\DB::enableQueryLog();
  App\Proj::whereNotNull('deleted_at')->paginate(20);
dd(\DB::getQueryLog());

The query that came out of it is as follows:

select * from `projs` where `deleted_at` is not null and `projs`.`deleted_at` is null

To get a good result, the query needed is:

select * from `projs` where `deleted_at` is not null

Without the 'and ...' in the query result statement.

Tania Petsouka · Answer 1 · 2021-02-19T17:05:49.433

3

Your Proj model probably uses SoftDeletes Trait, thus the deleted_at is not null part is added by default.

Get the deleted ones like:

App\Proj::onlyTrashed()->paginate(20)

edited Feb 19 '21 at 17:05

answered Feb 19 '21 at 16:39

Tania Petsouka

Thanks for the feedback, but how can I have a list of only records that have been flagged as deleted? – Samuel Omopariola Feb 19 '21 at 17:02
Just modified my answer, thought you wanted the non-deleted. – Tania Petsouka Feb 19 '21 at 17:06

score 2 · Answer 2 · answered Feb 19 '21 at 16:40

2

don't use whereNotNull('deleted_at') because if you are using soft delete then laravel automatically run this

answered Feb 19 '21 at 16:40

Moolchand Sharma

Thanks for the feedback, but how can I have a list of the deleted only? – Samuel Omopariola Feb 19 '21 at 17:01
This is one perfect answer as a query builder. Cause often 'null' can not be considered as all databases may not recognize it. so whereNotNull() is the right choice to check if the field is not 'null' – Mehedi Hassan Feb 19 '21 at 17:44

score 2 · Accepted Answer · answered Feb 19 '21 at 17:02

To get the soft deleted Proj entries use onlyTrashed()

\DB::enableQueryLog();
  App\Proj::onlyTrashed()->paginate(20);
dd(\DB::getQueryLog());

if you want to include the soft deleted ones in a query, use withTrashed()

App\Proj::withTrashed()->paginate(20);

3 Answers3