Level 102
Why do you add this check like this?
$query
->whereNotNull('deleted_at') //check if deleted at is null
->withTrashed(); //check if deleted at is null or not null
Do you only want trashed?
$query->onlyTrashed()
Aug 11, 2022
17
Level 1
Query result with deleted_at
Hello, everyone I have a problem with the result with one of my queries. There is two database tables with schema like bellow
Schema::create('post_xrefs', function (Blueprint $table) {
$table->id();
$table->foreignId('user_id')->constrained()->onDelete('cascade');
$table->string('type', 32);
$table->integer('parent')->nullable();
$table->integer('position')->default(0);
$table->string('status', 20)->default('published');
$table->softDeletes();
$table->timestamps();
});
And
Schema::create('posts', function (Blueprint $table) {
$table->id();
$table->foreignId('post_xref_id')->constrained()->onDelete('cascade');
$table->string('locale', 3)->default('bg');
$table->string('title');
$table->text('excerpt')->nullable();
$table->longText('body')->nullable();
$table->string('slug')->unique();
$table->boolean('is_cached')->default(0);
$table->timestamp('cache_expired')->nullable();
$table->softDeletes();
$table->timestamp('published_at')->useCurrent();
$table->timestamps();
});
I have a method for searching which is
public function scopeSearch($query, $search = '', $locale = null, $excluded = 0)
{
/* just for a example */
/* $this->trashed = false; */
/* $this->trashed = true; */
/* ------------------- */
return Post::with('post_xref')
->whereHas('post_xref', function($query) use($excluded) {
$query->where('type', 'news')
->when($this->trashed, function($query) {
$query->whereNotNull('deleted_at')->withTrashed();
})
->when($excluded, function($query) use($excluded) {
$query->where('id', '!=', $excluded);
});
})
->when(mb_strlen($search) >= 2, function($query) use ($search) {
$query->where('posts.title', 'like', '%' . $search . '%');
})
->where('locale', $locale ? $locale : App::currentLocale())
->orderBy($this->sortField, $this->sortDirection);
}
The relation is
public function post_xref()
{
return $this->belongsTo(PostXref::class, 'post_xref_id');
}
When i set $this->trashed = false; and in the the post_xrefs table deleted_at is null, the result is Ok. By Ok i mean the relation of post_xref is not empty, but when i set $this->trashed = true; then post_xref is empty even when in the post_xrefs table there is a record with deleted_at column not null.
How can i make the query so it return always non empty post_xref relation?
Thanks in advance
Level 1
Hello, for some reason if i add only
->withTrashed()
the query is
"select * from `hash_posts` where exists (select * from `hash_post_xrefs` where `hash_posts`.`post_xref_id` = `hash_post_xrefs`.`id` and `type` = ?) and `locale` = ? order by `updated_at` desc
but if i use
$query->whereNotNull('deleted_at')->withTrashed();
the query become
^ "select * from `hash_posts` where exists (select * from `hash_post_xrefs` where `hash_posts`.`post_xref_id` = `hash_post_xrefs`.`id` and `type` = ? and `deleted_at` is not null) and `locale` = ? order by `updated_at` desc
Level 102
@HristoMihaylov Yup. Thats how its meant to work. Thats why you have ->onlyTrashed()
If you dont use any it sets ->whereNull('deleted_at')
If you set ->withTrashed() it removes the check on the deleted_at column completely
If you set ->onlyTrashed() it sets ->whereNotNull('deleted_at')
Level 1
@Sinnbeck
Thank you. My mistake, i just need onlyTrashed() not withTrashed()
Level 102
@HristoMihaylov Is it solved? :)
Level 1
But the main problem still remain
Level 1
Even simple query like this:
Post::with('post_xref')
->whereHas('post_xref', function($query) {
$query->onlyTrashed();
})
->get()
return empty post_xref
Level 102
@HristoMihaylov not sure if it matters but can you try and camel case the method name and in the calls in relationship queries
Level 1
@Sinnbeck Still the same
^ Illuminate\Database\Eloquent\Collection {#1665 ▼
#items: array:2 [▼
0 => App\Models\Post {#1630 ▶}
1 => App\Models\Post {#1670 ▼
#connection: "mysql"
#table: "posts"
#primaryKey: "id"
#keyType: "int"
+incrementing: true
#with: []
#withCount: []
+preventsLazyLoading: false
#perPage: 15
+exists: true
+wasRecentlyCreated: false
#escapeWhenCastingToString: false
#attributes: array:2 [▶]
#original: array:2 [▶]
#changes: []
#casts: []
#classCastCache: []
#attributeCastCache: []
#dates: []
#dateFormat: null
#appends: []
#dispatchesEvents: []
#observables: []
#relations: array:1 [▼
"postXref" => null
]
#touches: []
+timestamps: true
#hidden: []
#visible: []
#fillable: array:7 [▶]
#guarded: array:1 [▶]
+sortField: "updated_at"
+sortDirection: "desc"
+trashed: false
+alias: "page"
}
]
Level 102
@HristoMihaylov and the query? Remember to add onlyTrashed to the with() query
Level 1
Post::with('postXref')
->whereHas('postXref', function($query) {
$query->onlyTrashed();
})->get()
i've simplified the query for testing.
postXref is always empty when i try to filter by deleted_at. If i try filter by Id for example it works.
Post::with('postXref')
->whereHas('postXref', function($query) {
$query->where('id', '!=', 45);
})->get()
Level 102
@HristoMihaylov remember both
Post::with(['postXref' => function($query) {
$query->onlyTrashed();
}])
->whereHas('postXref', function($query) {
$query->onlyTrashed();
})->get()
Level 1
It's working :) But why previous didn't? Why should onlyTrashed be in both places?
Level 102
@HristoMihaylov because they are completely seperate queries that has nothing to do with each other. With() run a new/seperate query
Level 102
I can recommend installing debugbar to inspect the queries being run :)
Please mark a best answer to set the thread as solved
Level 1
Thank you. Wish you the best
Level 102
@HristoMihaylov same to you :)
Please or to participate in this conversation.