Rules Engine "Many-To-Many has" Dynamic Filter Expression improvements

Description

Currently a Left join with a subquery in combination with a where clause filter is used for this filter.This is not optimal as via the extra join the query result set is manipulated and not just filtered, requiring a distinct to get rid of duplicates.
Proposal: use exists filter instead.
Example:
Current:
select distinct
testplatform.identifier,
testplatform.name,
testplatform.description,
testplatform.id as id
from
`pobj_testplatform` `testplatform`
left join
(
select
Count( technologies.id ) as manytomany_count
, testplatform.id as id
from
`pobj_testplatform` `testplatform`
left join
`pobj_testplatform_technology` `testplatform_technology`
on (`testplatform_technology`.`testplatform` = `testplatform`.`id`)
left join
`pobj_technology` `technologies`
on (`technologies`.`id` = `testplatform_technology`.`technology`)
where
( technologies.supports_reduced_core_applicability = true )
group by
testplatform.id
) `manyToManyHas114ecd4237cc439d9e224d1229721c19`
on (`manyToManyHas114ecd4237cc439d9e224d1229721c19`.`id` = `testplatform`.`id`)
where
(
( (
manyToManyHas114ecd4237cc439d9e224d1229721c19.manytomany_count is null
or manyToManyHas114ecd4237cc439d9e224d1229721c19.manytomany_count = 0
) )
)

Proposed change:
select
testplatform.identifier,
testplatform.name,
testplatform.description,
testplatform.id as id
from
`pobj_testplatform` `testplatform`
where
(
not exists (
select
1
from
`pobj_testplatform_technology` `testplatform_technology`
inner join
`pobj_technology` `technologies`
on (`technologies`.`id` = `testplatform_technology`.`technology`)
where
testplatform_technology.testplatform = `testplatform`.`id`
and ( technologies.supports_reduced_core_applicability = true )
group by
testplatform_technology.testplatform
having
count(`testplatform_technology`.`technology`) > 0
)
)

Your pinned fields
Click on the next to a field label to start pinning.

Assignee

Unassigned

Reporter

Jan Jannek