Level 63
I don't see any scopeCurrentlyAvailable scope in your code, but you are trying to use it.
Jul 14, 2022
5
Level 6
Query Scope where $user->relationship->method() is true
I am trying to write a query scope to find all instances of the User model where the user's schedule indicates that they are available.
I have the User model, and a UserSchedule model. the User model has a method called isAvailable() that returns true or false if the UserSchedule indicates that a user is available on the current day of the week.
my UserSchedule:
class UserSchedule extends Model
{
use HasFactory;
protected $table = 'availability_user';
protected $guarded = [];
public function user(): BelongsTo
{
return $this->belongsTo(User::class);
}
public function get(): OpeningHours
{
return OpeningHours::create([
'monday' => $this->getDaySchedule('monday'),
'tuesday' => $this->getDaySchedule('tuesday'),
'wednesday' => $this->getDaySchedule('wednesday'),
'thursday' => $this->getDaySchedule('thursday'),
'friday' => $this->getDaySchedule('friday'),
'saturday' => $this->getDaySchedule('saturday'),
'sunday' => $this->getDaySchedule('sunday'),
'exceptions' => $this->exceptions(),
]);
}
private function monday(): array
{
return [$this->removeSeconds($this->monday_start).'-'.$this->removeSeconds($this->monday_end)];
}
private function getDaySchedule($day): array
{
if ($this->$day === 0) {
return [];
} else {
return [$this->removeSeconds($this->{$day.'_start'}).'-'.$this->removeSeconds($this->{$day.'_end'})];
}
}
private function exceptions(): array
{
return [];
}
private function removeSeconds($time): string|null
{
if ($time) {
return substr($time, 0, -3);
} else {
return null;
}
}
}
my User model:
class User extends Authenticatable implements \Spatie\Onboard\Concerns\Onboardable
{
use HasApiTokens,
HasFactory,
Notifiable,
HasRoles,
Billable,
\Spatie\Onboard\Concerns\GetsOnboarded;
/*
* ....
*/
public function schedule(): HasOne
{
return $this->hasOne(UserSchedule::class);
}
public function isAvailable(): bool
{
if ($this->schedule()->exists()) {
$schedule = $this->schedule->get();
$today = Carbon::now()->dayName;
return $schedule->isOpenOn($today);
} else {
return false;
}
}
/**
* Alters the query string so that only the
* users with a specific role are returned.
*
* @param $query
* @param $role
* @return void
*/
function scopeWithRole($query, $role): void
{
$query->whereHas('roles', function ($q) use ($role) {
$q->where('name', $role);
});
}
/**
* Alters the query string so that only the
* users whose schedule indicates they are
* available today are returned.
*
* @param $query
* @param $role
* @return void
*/
function scopeAvailableToday($query)
{
$query->where($this->isAvailable(), true);
}
}
What I am trying to do is create a query scope so that I can do something like this:
$availableUsers = User::currentlyAvailable()->get();
and get a collection of Users whose schedules indicate that they are available on today's day of the week.
But when I run the code in Tinker as is, with data that I know is accurate, I get this error:
$user = App\Models\User::currentlyAvailable()->get();
Illuminate\Database\QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column '' in 'where clause' (SQL: select * from `users` where `` = 1)'
Level 10
That error you get when calling App\Models\User::currentlyAvailable() seems to be something you would get when calling App\Models\User::availableToday(), defined in scopeAvailableToday()
The first parameter of the where() method of a query object should be a string with colum name, but the function isAvailable() returns a boolean.
If that happens to be false, I can totally see eloquent making an empty string as column name, which is what you see in "select * from users where `` = 1"
1 like
Level 6
@psrz I ended up modifying the code a bit to check whether or not the day of the week is listed as 1 in the database for that user, so now everything works... but it leaves me wondering, is it possible to build a custom query scope so that we check the return value of a method on that relationship, rather than a column value?
Level 10
@garrettmassey I don't quite understand what you are trying to do when you say checking "the return value of a method on that relationship"
To your original question, the "Eloquent" way of filtering the User model by its schedule relationship would be calling the whereHas method on the query builder, just like you did with your scope withRole()
By looking at the code of the getDaySchedule() method I'm guessing in the availability_user table there are fields such as monday_start, monday_end, tuesday_start, tuesday_end and so on
$users = User::whereHas('schedule', function(Builder $query) {
$day = now()->dayName;
$query->whereNotNull($day . '_start')->whereNotNull($day . '_end');})
->get();
Or something similar. I don't know your data so I'm just guessing. I would make it a scope with $day as an optional parameter for more flexibility
Also, the isAvailable() method can easily make a N+1 issue if you're itarating User models and calling that method. I would recommend eager loading the "schedule" relationship if you intend to check or show the availabily on every user and simplify a bit that method
public function isAvailable() : bool
{
if (! $this->schedule){
return false;
}
return $this->schedule->isOpenOn(now()->dayName);
}
Or if you're using php 8.0 or superior you can take advantage of the nullsafe operator and the body method could be simply
return (bool) $this->schedule?->isOpenOn(now()->dayName);
Level 102
Your scope scopeWithRole already exist in spatie permissions
User::role('admin')->get();
2 likes
Please or to participate in this conversation.