When using this package to search multiple relations using the OR mode the query generated is not produced as expected. For example, consider the following if we have a one to one relation between "providers" and "users" where the users table contains first_name and last_name and the providers table has a user_id column:
?user:first_name&user:last_name&mode=or will generate the following query
where exists (select * from `users` where `providers`.`user_id` = `users`.`id` and (`last_name` = ?)) **and**
exists (select * from `users` where `providers`.`user_id` = `users`.`id` and (`first_name` = ?))
The expected result would be
where exists (select * from `users` where `providers`.`user_id` = `users`.`id` and (`last_name` = ?)) **OR**
exists (select * from `users` where `providers`.`user_id` = `users`.`id` and (`first_name` = ?))
As a side note, I believe this query should actually group the two queries under the same subquery like this:
where exists (select * from `users` where `providers`.`user_id` = `users`.`id` and (`last_name` = ? OR `first_name` = ?))
however I believe this is a separate issue. I propose the following changes in Constraint.php
public function apply(Builder $builder, $field, $mode = Constraint::MODE_AND)
{
if ($this->isRelation($field)) {
list($relation, $field) = $this->splitRelationField($field);
if (static::parseIsNegation($relation)) {
$builder->doesntHave($relation, $mode, function (Builder $builder) use ($field, $mode) {
$this->doApply($builder, $field, $mode);
});
} else {
$builder->has($relation,'>=',1,$mode, function (Builder $builder) use ($field, $mode) {
$this->doApply($builder, $field, $mode);
});
}
} else {
$this->doApply($builder, $field, $mode);
}
}
Where the ->whereDoesntHave() is replaced with doesntHave() passing in the $mode and the whereHas() is replaced with has(). This could actully simplify the doApply() function as well instead of resolving the method name 'whereIn' 'orWhereIn' etc, the $mode could be passed in directly the function.
When using this package to search multiple relations using the OR mode the query generated is not produced as expected. For example, consider the following if we have a one to one relation between "providers" and "users" where the users table contains first_name and last_name and the providers table has a user_id column:
?user:first_name&user:last_name&mode=or will generate the following query
The expected result would be
As a side note, I believe this query should actually group the two queries under the same subquery like this:
however I believe this is a separate issue. I propose the following changes in Constraint.php
Where the ->whereDoesntHave() is replaced with doesntHave() passing in the $mode and the whereHas() is replaced with has(). This could actully simplify the doApply() function as well instead of resolving the method name 'whereIn' 'orWhereIn' etc, the $mode could be passed in directly the function.