Rules Engine "One-To-Many Count" 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( configurations.id ) as onetomany_count,
testplatform.id as id
from
`pobj_testplatform` `testplatform`
left join
`pobj_testplatform_configuration` `configurations`
on (`configurations`.`testplatform` = `testplatform`.`id`)
where
( configurations.is_active = true )
group by
testplatform.id
) `manyToManyCount221d5ef5eee34f02a567a987c61bcbac`
on (`manyToManyCount221d5ef5eee34f02a567a987c61bcbac`.`id` = `testplatform`.`id`)
where
(
( manyToManyCount221d5ef5eee34f02a567a987c61bcbac.onetomany_count > 0.0 )
)

Proposed change:
select
testplatform.identifier,
testplatform.name,
testplatform.description,
testplatform.id as id
from
`pobj_testplatform` `testplatform`
where
(
exists (
select
1
from
`pobj_testplatform_configuration` `configurations`
where
configurations.testplatform = `testplatform`.`id`
and ( configurations.is_active = true )
group by
configurations.testplatform
having
count(`configurations`.`testplatform`) > 0
)
)

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

Assignee

Unassigned

Reporter

Jan Jannek