Rules Engine "Many-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( 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.is_active = true )
group by
testplatform.id
) `manyToManyCountb4afb79dc79a42668e333a3d944aeb5d`
on (`manyToManyCountb4afb79dc79a42668e333a3d944aeb5d`.`id` = `testplatform`.`id`)
where
(
( manyToManyCountb4afb79dc79a42668e333a3d944aeb5d.manytomany_count > 1 )
)

Proposed change:
select
testplatform.identifier,
testplatform.name,
testplatform.description,
testplatform.id as id
from
`pobj_testplatform` `testplatform`
where
(
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.is_active = true )
group by
testplatform_technology.testplatform
having
count(`technologies`.`id`) > 1
)
)

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

Assignee

Unassigned

Reporter

Jan Jannek