Condition for a constraint?

[chrismerron]

Hi,
Is there a way to provide a condition for the constraint, rather than a callable on the query?

For example, I'd like to do this:
Builder::hasByNonDependentSubquery('relationship', '>=', $value)

This is possible when using
Builder::has('relationship', '>=', $value)

Many thanks!

[mpyw]

Unfortunately that is not supported. The feature is useful but its SQL looks hard to be optimized.

	has without counting
Standard	where exists (select * from sub_table where sub_table.id = main_table.foreign_id)
Patched	where main_table.foreign_id in (select sub_table.id from sub_table)

The patched query will be normally optimized to have the same execution plan as for INNER JOIN clause.

	has with counting
Standard	where (select count(*) from sub_table where sub_table.id = main_table.foreign_id) > ?
Patched	???

In this case, what should we do for that? We can use (select sub_table.id, count(*) from sub_table group by sub_table.id) to avoid dependent subqueries, but we need the HAVING clause to use the result which may degrade the performance.

The current best solution for you is still:

Builder::has('relationship', '>=', $value)