Here is my scope, the apply() method works wonderfully.
use Illuminate\Database\Eloquent\ScopeInterface;
use Illuminate\Database\Eloquent\Builder;
use Illuminate\Database\Eloquent\Model;
use Carbon\Carbon;
class JobStatusScope implements ScopeInterface
{
/**
* Apply scope on the query.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function apply(Builder $builder, Model $model)
{
$builder
->where('active', true)
->whereHas('user', function($q) {
$q->where('account_type', 'company')
->whereNested(function($r) {
$r->where('stripe_active', true)
->orWhereNotNull('subscription_ends_at')
->where('subscription_ends_at', '>', Carbon::now())
->orWhereNotNull('trial_ends_at')
->where('trial_ends_at', '>', Carbon::today());
});
});
}
/**
* Remove scope from the query.
*
* @param \Illuminate\Database\Eloquent\Builder $builder
* @param \Illuminate\Database\Eloquent\Model $model
* @return void
*/
public function remove(Builder $builder, Model $model)
{
$query = $builder->getQuery();
//dd($query->wheres);
$columns = ['stripe_active', 'subscription_ends_at', 'active', 'trial_ends_at'];
foreach ((array) $query->wheres as $key => $where) {
if (in_array($where['column'], $columns)) {
unset($query->wheres[$key]);
$query->wheres = array_values($query->wheres);
}
}
}
}
What I tried in my remove() method didn't work.
I have this in a trait file, and attached to my Model
use App\Cable\Traits\Scopes\JobStatusScope;
trait JobStatusTrait {
public static function bootJobStatusTrait()
{
static::addGlobalScope(new JobStatusScope);
}
/**
* Get the query builder without the scope applied.
*
* @return \Illuminate\Database\Eloquent\Builder
*/
public static function withAll()
{
return with(new static)->newQueryWithoutScope(new JobStatusScope);
}
}
When I try to run MyModel::withAll()->get() it should return all records without the apply() constraints but it does not.
When I dd($query->wheres) in the remove() method and call the ->withAll() I get this
array:3 [▼
0 => array:3 [▼
"type" => "Null"
"column" => "jobs.deleted_at"
"boolean" => "and"
]
1 => array:5 [▼
"type" => "Basic"
"column" => "active"
"operator" => "="
"value" => true
"boolean" => "and"
]
2 => array:5 [▼
"type" => "Basic"
"column" => Expression {#478 ▼
#value: "(select count(*) from `users` where `jobs`.`user_id` = `users`.`id` and `account_type` = ? and (`stripe_active` = ? or `subscription_ends_at` is not null and `subscription_ends_at` > ? or `trial_ends_at` is not null and `trial_ends_at` > ?) and `users`.`deleted_at` is null)"
}
"operator" => ">="
"value" => Expression {#476 ▶}
"boolean" => "and"
]
]
I can't seem to figure this out. Most of Laravel is quite easy to figure out, and it is so "eloquent", but this escapes me.
Any help would be appreciated. Thanks!
The whereHas() restriction works by use a subquery to get the count of the related records.
As you can see from your dd() output, the column for your target where clause is not just a plain string (like 'active'), but is an Expression object that contains the subquery.
You'll need to write some logic to determine if this Expression is the one that matches the subquery generated by the whereHas(), so you can remove it if it is.
A simple example would be something like the following (n.b. not syntax checked or tested):
public function remove(Builder $builder, Model $model)
{
$query = $builder->getQuery();
// your plain column names
$columns = ['active'];
// columns present in the user relationship subquery
$userColumns = ['stripe_active', 'subscription_ends_at', 'trial_ends_at'];
foreach ((array) $query->wheres as $key => $where) {
// first check if the column is really an Expression
// if it is not an expression, do the normal check on strings
if ($where['column'] instanceof \Illuminate\Database\Query\Expression) {
// get the sql value of the Expression
$sql = $where['column']->getValue();
// see if all the user columns are in the sql statement
$count = 0;
foreach($userColumns as $name) {
// increment count if the user column was found
$count += (stripos($sql, $name) !== false) ? 1 : 0;
}
// if all the user columns were found, assume this is the where and remove it
if ($count == count($userColumns)) {
unset($query->wheres[$key]);
$query->wheres = array_values($query->wheres);
}
} elseif (in_array($where['column'], $columns)) {
unset($query->wheres[$key]);
$query->wheres = array_values($query->wheres);
}
}
}
You may want to come up with something a little more strict than just checking if all the column names are in the SQL, but this should get you started.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments