Variable query parameter withTrashed, onlyTrashed or default

I am building a data table to show all users.

It has a dropdown to select if records need to be collected with the trashed once and only the trashed ones. By default it will collect the records without the trashed once.

The query looks like this:

User::where('name', 'LIKE', '%'.$request->search.'%')
	->withTrashed()
	->orderBy($order, $direction)
	->paginate(10)
	->withQueryString()
	->through(function ($user) {
		return [
			'id' => $user->id,
			'profile_photo_url' => $user->profile_photo_url,
			'name' => $user->name,
			'username' => $user->username,
			'email' => $user->email,
			'created_at' => $user->created_at,
			'updated_at' => $user->updated_at,
			'email_verified_at' => $user->email_verified_at,
			];
		})
	]);

At the moment I am repeating the query for the three variants in a switch but that looks rather ugly. I have been searching on a way to make the "->withTrashed()" part variable but am unable to find a solution.

Is this possible and if so, how?

Thanks!

Nakov

4 years ago

Best Answer

Level 73

Have you tried with a conditional clause like here: https://laravel.com/docs/8.x/queries#conditional-clauses

User::where('name', 'LIKE', '%'.$request->search.'%')
	->when(true, function($query) {
        return $query->withTrashed();
    })
	->orderBy($order, $direction)
	->paginate(10)
	->withQueryString()
	->through(function ($user) {
		return [
			'id' => $user->id,
			'profile_photo_url' => $user->profile_photo_url,
			'name' => $user->name,
			'username' => $user->username,
			'email' => $user->email,
			'created_at' => $user->created_at,
			'updated_at' => $user->updated_at,
			'email_verified_at' => $user->email_verified_at,
			];
		})
	]);

ofc replace true with your check. Here is an example as well https://laraveldaily.com/less-know-way-conditional-queries/

1 like

Kimmer

4 years ago

Level 4

Thanks a lot for your reply @nakov !

This is indeed a nicer solution than repeating the query. This is what I did to accommodate for the three variants. I'm not sure if it is the most elegant solution but it works nicely and is way shorter than what I had before.

switch ($request->trashed) {
	case 'with':
	$withTrashed = true;
	$onlyTrashed = false;
	break;
	case 'only':	
	$withTrashed = false;
	$onlyTrashed = true;
	break;
	default:	
	$withTrashed = false;
	$onlyTrashed = false;		
};

User::where('name', 'LIKE', '%'.$request->search.'%')
->when($withTrashed, function($query) {
	return $query->withTrashed();
})
->when($onlyTrashed, function($query) {
	return $query->onlyTrashed();
})
->orderBy($order, $direction)
->paginate(10)
->withQueryString()
->through(function ($user) {
	return [
		'id' => $user->id,
		'profile_photo_url' => $user->profile_photo_url,
		'name' => $user->name,
		'username' => $user->username,
		'email' => $user->email,
		'created_at' => $user->created_at,
		'updated_at' => $user->updated_at,
		'email_verified_at' => $user->email_verified_at,
	];
})

Thanks again!

Nakov

4 years ago

Level 73

@Kimmer Looks okay to me if it works. :)

Make sure you accept my answer if it helped, it might help someone else as well :)

Kimmer

4 years ago

Level 4

Sadly the second when() stops working when a orWhere is used to also search in another column

So in this case "when($onlyTrashed..." stops working

User::where('name', 'LIKE', '%'.$request->search.'%')
->orWhere('username', 'LIKE', '%'.$request->search.'%')
->when($withTrashed, function($query) {
	return $query->withTrashed();
})
->when($onlyTrashed, function($query) {
	return $query->onlyTrashed();
})
->orderBy($order, $direction)
->paginate(10)
->withQueryString()
->through(function ($user) {
	return [
		'id' => $user->id,
		'profile_photo_url' => $user->profile_photo_url,
		'name' => $user->name,
		'username' => $user->username,
		'email' => $user->email,
		'created_at' => $user->created_at,
		'updated_at' => $user->updated_at,
		'email_verified_at' => $user->email_verified_at,
	];
})

Any idea why?

Update: it seems "->onlyTrashed()" simply does not work when orWhere() is used

User::where('name', 'LIKE', '%'.$request->search.'%')
->orWhere('username', 'LIKE', '%'.$request->search.'%')
->onlyTrashed()
->orderBy($order, $direction)
->paginate(10)
->withQueryString()
->through(function ($user) {
	return [
	'id' => $user->id,
	'profile_photo_url' => $user->profile_photo_url,
	'name' => $user->name,
	'username' => $user->username,
	'email' => $user->email,
	'created_at' => $user->created_at,
	'updated_at' => $user->updated_at,
	'email_verified_at' => $user->email_verified_at,
	];
	})

Kimmer

4 years ago

Level 4

Aparerently you need to add the Trahsed clause after every where or orWhere. This seems to work

User::where('name', 'LIKE', '%'.$request->search.'%')
->when($withTrashed, function($query) {
	return $query->withTrashed();
})
->when($onlyTrashed, function($query) {
	return $query->onlyTrashed();
})
->orWhere('username', 'LIKE', '%'.$request->search.'%')
->when($withTrashed, function($query) {
	return $query->withTrashed();
})
->when($onlyTrashed, function($query) {
	return $query->onlyTrashed();
})
->orWhere('email', 'LIKE', '%'.$request->search.'%')
->when($withTrashed, function($query) {
	return $query->withTrashed();
})
->when($onlyTrashed, function($query) {
	return $query->onlyTrashed();
})
->orderBy($order, $direction)
->paginate(10)
->withQueryString()
->through(function ($user) {
	return [
		'id' => $user->id,
		'profile_photo_url' => $user->profile_photo_url,
		'name' => $user->name,
		'username' => $user->username,
		'email' => $user->email,
		'created_at' => $user->created_at,
		'updated_at' => $user->updated_at,
		'email_verified_at' => $user->email_verified_at,
	];
})

Please or to participate in this conversation.