Complex joining condition on event_table in WSO2 CEP

Question

I need to implement a filtering feature on events using WSO2 CEP 4.1.0. My filters are stored in a PostgreSQL database.

To do this, I created an event_table configuration, and I join my event on this event_table stream. My filters can have default values, so I need a complex joining condition:

from my_stream#window.length(1) left outer join my_event_table as filter
    on (filter.field1 == '' OR stream.field1 == filter.field1)
    AND (filter.field2 == '' OR stream.field2 == filter.field2)

(I do a LEFT OUTER JOIN because I must have a different process if the filter is found or not: if I find the filter, I complete my_stream with information from it, and I save the event in database table; if not, I save the event in another database table).

Problem is when the system extract the join condition to interpret it, it removes the parenthesis, so the boolean interpretation is wrong:

on filter.field1 == '' OR stream.field1 == filter.field1
AND filter.field2 == '' OR stream.field2 == filter.field2

Is there a way to implement this kind of feature, without plugin creation?

Regards.

EDIT: This is the current solution I found, but I am afraid about performance and complexity, so look for another one:

#first, I left join on my event_table
from my_stream#window.length(1) left outer join my_event_table as filter
        on (filter.field1 == '' OR stream.field1 == filter.field1)
select stream.field1, stream.field2, stream.field3, filter.field1 as filter_field1, filter.field2 as filter_field2, filter.field3 as filter_field3, filter.info1
insert into tempStreamJoinProblemCount

#if the join return nothing, then no filter for my line
from tempStreamJoinProblemCount[filter_field1 IS NULL]
insert into filter_not_found

#if the join return some lines, maybe 1 of these lines can match, I continue to check
from tempStreamJoinProblemCount[NOT filter_field1 IS NULL]
select field1, field2, field3, info1
#I check my complex joining condition and store it in a boolean for later: 1 then my filter match, 0 then no match
convert(
(filter_field2=='' OR field2 == filter_field2)
AND(filter_field3=='' OR field3 == filter_field3),'int') as filterMatch
insert into filterCheck

#if filterMatch is 1, I extract the filter information (info1), else I put a default value (minimal value); custom:ternaryInt is just the ternary function: boolean_condition?value_if_true:value_if_false
from computeFilterMatchInformation
select field1, custom:ternaryInt(filterMatch==1, info1, 0) as info1, filterMatch
insert into filterCheck

#As we did not join on all fields, 1 line has been expanded into several lines, so we group the lines, to remove these generated lines and keep only 1 initial line; 
from filterMatchGroupBy#window.time(10 sec)
#max(info1) return only the filter value (because the value 0 from previous stream is the minimal value);
#sum(filterMatch) return 0 if there is no match, and 1+ if there is a match
select field1, max(info1) as info1, sum(filterMatch) as filter_match
group by field1, field2, field3
insert into filterCheck

#we found no match
from filterCheck[filter_match == 0]
select field1, field2, field3
insert into filter_not_found

#we found a match, so we extract filter information (info1)
from filterCheck[filter_match > 0]
select field1, field2, field3, info1
insert into filter_found

Grainier · Answer 1 · 2016-09-09T08:07:28.320

Fundamentally, left outer join might not work with an event table. Because event table is not an active construct (like a stream). So we cannot assign a window to an event table. However, in order to join with (outer joins) each stream should be associated with a window. Since we cannot do that with event tables, outer-joins wouldn't work anyway.

However, to address your scenario, you can join my_stream with my_event_table without any conditions and emit resulting events into an intermediate stream, and then check for the conditions on that intermediate stream. Try something similar to this;

from my_stream join my_event_table
select 
    my_stream.field1 as streamField1, 
    my_event_table.field1 as tableField1,
    my_stream.field1 as streamField2, 
    my_event_table.field1 as tableField2,
insert into intermediateStream;

from intermediateStream[((tableField1 == '' OR streamField1 == tableField1) AND (tableField2 == '' OR streamField2 == tableField2))]
select *
insert into filtereMatchedStream;

from intermediateStream[not ((tableField1 == '' OR streamField1 == tableField1) AND (tableField2 == '' OR streamField2 == tableField2))]
select *
insert into filtereUnMatchedStream;

Problem with this solution is I lose the not condition matched lines, which I need. I edit the question on this point. Concerning LEFT OUTER JOIN and window, I do not understand what you said, because it currently works as expected. — Astrorvald, Sep 09 '16 at 08:00
For that, you can check for the negation and emit that into another stream as shown in the updated the answer. — Grainier, Sep 09 '16 at 08:09
Hm, but as I join on no condition, then the negation will join on all the join generated lines, no?About the window, I forget something in the firstly provided question, I completed it with #window.length(1); — Astrorvald, Sep 09 '16 at 08:40

Complex joining condition on event_table in WSO2 CEP

1 Answers1