Own Filter in sqlcontainer - how to implement?

Vaadin 6 / 7 / 8 Framework Data Binding
1

Hi all,

I’m using SQLContainer in vaadin app 6.8.1, and I’d like implement filter for field type date like that (SQL):

DECLARE
@d1 datetime,
@d2 datetime,
@d3 int

SET @d1 = '2012-08-01';
SET @d2 = '2012-08-31';
SET @d3 = (year(@d1)*100+(MONTH(@d1)))

SELECT * FROM table t0
WHERE ( -- part 1
	(
		(t0.periodDateFrom <= @d1)
		AND (t0.periodDateTo >= @d1)
	)
	OR
	(
		(t0.periodDateFrom <= @d2)
		AND (t0.periodDateTo >= @d2)
	)
) OR -- part 2
(
	(
		((YEAR(t0.periodDateFrom)*100)+(MONTH(t0.periodDateFrom))) = @d3
	) AND
	(
		((YEAR(t0.periodDateTo)*100)+(MONTH(t0.periodDateTo))) = @d3
	)
)

In part 2 I need detect that date like that: periodDateFrom = ‘2012-08-04’, periodDateTo = ‘2012-08-25’

I did in app code like that:

		Date d1 = Utils.cleanDateSetFirstDayOfMonth((Date) idfDatePeriodMonth.getValue());
		Date d2 = Utils.cleanDateSetLastDayOfMonth((Date) idfDatePeriodMonth.getValue());

		Filter filter1 = new Compare.LessOrEqual("periodDateFrom", new Timestamp(d1.getTime()));
		Filter filter2 = new Compare.GreaterOrEqual("periodDateTo", new Timestamp(d1.getTime()), 2);
		Filter filter3 = new Compare.LessOrEqual("periodDateFrom", new Timestamp(d2.getTime()));
		Filter filter4 = new Compare.GreaterOrEqual("periodDateTo", new Timestamp(d2.getTime()), 2);
		
		And and1 = new And(filter1, filter2);
		And and2 = new And(filter3, filter4);

		Or or = new Or(and1, and2);
		
		containerTable.addContainerFilter(or);

And filtering on part 1 works OK. But I don’t have idea how can I implement second part filter. Is it possible implement this filter? I found old msg that actually it’s not possible:

https://vaadin.com/forum/-/message_boards/view_message/253684#_19_message_253684

Is this message is actually for version vaadin 6.8.1?

Any ideas?

Best regards,
Paul

2

you may be able to use de Morgans Theorem

http://en.wikipedia.org/wiki/De_Morgan's_laws

in particular
(P or Q) = not(not(P) and not(Q))

3

Hi,

Thank you for idea. I read your link but I think that it cannot be used (or I didn’t understand). See:

Example data:

id periodDateFrom	periodDateTo
1 2012-01-01	2012-12-31
2 2012-01-01	2012-07-31
3 2012-01-01	2012-07-15
4 2012-07-16	2012-12-31
5 2012-07-01	2012-12-31
6 2012-07-05	2012-07-10
7 2012-07-31	2012-12-31
8 2012-08-01	2012-12-31
9 2012-08-02	2012-12-31

For example we search data contains date between: 2012-07-01 - 2012-07-31:

it should returns rows: 1,2,3,4,5,6,7

If you want data between 2012-07-15 - 2012-07-30 it should returns rows: 1,2,3,4,5

Code SQL looks like that:

SELECT t0.* FROM table t0 WHERE
(
    (
        (t0.periodDateFrom <= @d1) AND (t0.periodDateTo >= @d1)
    )
    OR
    (
        (t0.periodDateFrom <= @d2) AND (t0.periodDateTo >= @d2)
    )
) OR (
	(
		((t0.periodDateFrom <= @d1) AND (t0.periodDateTo <= @d1)) OR ((t0.periodDateFrom <= @d2) AND (t0.periodDateTo <= @d2))
	) AND
	(
		((t0.periodDateFrom >= @d1) AND (t0.periodDateTo >= @d1)) OR ((t0.periodDateFrom >= @d2) AND (t0.periodDateTo >= @d2))
	)
)

This code works OK :slight_smile:

Best regards,
Paul

4

Hi again,

I’m interested about solution by Morgan’s Law. Can you write solution, is it possible do it simpler?

Best regards,
Paul

5

i dont think there is any more simple way
below is a description of the steps
i have taken
to convert to the conjunctive form

assuming i have made no typos
(and that is by no means certain!)
i would hope that the SQL at step 4.
will give the same result as the original SQL at step 0.
but i cant test that you will have to

you then just have to convert to Vaadin code

/* 0. original SQL
*/
(
(
(
(t0.periodDateFrom <= @d1)
AND (t0.periodDateTo >= @d1)
)
OR
(
(t0.periodDateFrom <= @d2)
AND (t0.periodDateTo >= @d2)
)
)
OR (
(
(
(t0.periodDateFrom <= @d1)
AND (t0.periodDateTo <= @d1)
)
OR (
(t0.periodDateFrom <= @d2)
AND (t0.periodDateTo <= @d2)
)
)
AND (
(
(t0.periodDateFrom >= @d1)
AND (t0.periodDateTo >= @d1)
)
OR (
(t0.periodDateFrom >= @d2)
AND (t0.periodDateTo >= @d2)
)
)
)
)

/* 1. negate the whole “where” and then its 2 clauses joined by OR and change OR to AND
*/
not
(
not
(
(
(t0.periodDateFrom <= @d1)
AND (t0.periodDateTo >= @d1)
)
OR
(
(t0.periodDateFrom <= @d2)
AND (t0.periodDateTo >= @d2)
)
)
and
not
(
(
(
(t0.periodDateFrom <= @d1)
AND (t0.periodDateTo <= @d1)
)
OR (
(t0.periodDateFrom <= @d2)
AND (t0.periodDateTo <= @d2)
)
)
AND (
(
(t0.periodDateFrom >= @d1)
AND (t0.periodDateTo >= @d1)
)
OR (
(t0.periodDateFrom >= @d2)
AND (t0.periodDateTo >= @d2)
)
)
)
)

/* 2. handle 1st clause same way - negate the whole clause and negate its 2 sub-clauses joined by OR and change OR to AND
*/
not
(
not
(
not
(
not
(
(t0.periodDateFrom <= @d1)
AND (t0.periodDateTo >= @d1)
)
and
not
(
(t0.periodDateFrom <= @d2)
AND (t0.periodDateTo >= @d2)
)
)
)
and
not
(
(
(
(t0.periodDateFrom <= @d1)
AND (t0.periodDateTo <= @d1)
)
OR (
(t0.periodDateFrom <= @d2)
AND (t0.periodDateTo <= @d2)
)
)
AND (
(
(t0.periodDateFrom >= @d1)
AND (t0.periodDateTo >= @d1)
)
OR (
(t0.periodDateFrom >= @d2)
AND (t0.periodDateTo >= @d2)
)
)
)
)

/* 3. handle 2nd clause same way - for its 1st sub-clause, negate the whole sub-clause and the 2 sub-sub-clauses joined by OR and change OR to AND
*/
not
(
not
(
not
(
not
(
(t0.periodDateFrom <= @d1)
AND (t0.periodDateTo >= @d1)
)
and
not
(
(t0.periodDateFrom <= @d2)
AND (t0.periodDateTo >= @d2)
)
)
)
and
not
(
not
(
not
(
(t0.periodDateFrom <= @d1)
AND (t0.periodDateTo <= @d1)
)
and
not
(
(t0.periodDateFrom <= @d2)
AND (t0.periodDateTo <= @d2)
)
)
AND (
(
(t0.periodDateFrom >= @d1)
AND (t0.periodDateTo >= @d1)
)
OR (
(t0.periodDateFrom >= @d2)
AND (t0.periodDateTo >= @d2)
)
)
)
)

/* 4. finally handle 2nd clause 2nd sub-clause same way - negate the whole sub-clause and the 2 sub-sub-clauses joined by OR and change OR to AND
*/
not
(
not
(
not
(
not
(
(t0.periodDateFrom <= @d1)
AND (t0.periodDateTo >= @d1)
)
and
not
(
(t0.periodDateFrom <= @d2)
AND (t0.periodDateTo >= @d2)
)
)
)
and
not
(
not
(
not
(
(t0.periodDateFrom <= @d1)
AND (t0.periodDateTo <= @d1)
)
and
not
(
(t0.periodDateFrom <= @d2)
AND (t0.periodDateTo <= @d2)
)
)
AND
not
(
not
(
(t0.periodDateFrom >= @d1)
AND (t0.periodDateTo >= @d1)
)
and
not
(
(t0.periodDateFrom >= @d2)
AND (t0.periodDateTo >= @d2)
)
)
)
)

6

sorry
finally worked out how to do this
here it is again

stupid thing still converts tabs to spaces though


/* 0. original SQL 
*/
(
	(
		(
			(t0.periodDateFrom	<= @d1)
		AND	(t0.periodDateTo	>= @d1)
		)
	OR
		(
			(t0.periodDateFrom	<= @d2)
		AND	(t0.periodDateTo	>= @d2)
		)
	)
OR	(
		(
			(
				(t0.periodDateFrom	<= @d1)
			AND	(t0.periodDateTo	<= @d1)
			)
		OR	(
				(t0.periodDateFrom	<= @d2)
			AND	(t0.periodDateTo	<= @d2)
			)
		)
	AND	(
			(
				(t0.periodDateFrom	>= @d1)
			AND	(t0.periodDateTo	>= @d1)
			)
		OR	(
				(t0.periodDateFrom	>= @d2)
			AND	(t0.periodDateTo	>= @d2)
			)
		)
	)
)

----------------------------------------------
/* 1. negate the whole "where" and then its 2 clauses joined by OR and change OR to AND
*/
not
(
	not
	(
		(
			(t0.periodDateFrom	<= @d1)
		AND	(t0.periodDateTo	>= @d1)
		)
	OR
		(
			(t0.periodDateFrom	<= @d2)
		AND	(t0.periodDateTo	>= @d2)
		)
	)
and
	not
	(
		(
			(
				(t0.periodDateFrom	<= @d1)
			AND	(t0.periodDateTo	<= @d1)
			)
		OR	(
				(t0.periodDateFrom	<= @d2)
			AND	(t0.periodDateTo	<= @d2)
			)
		)
	AND	(
			(
				(t0.periodDateFrom	>= @d1)
			AND	(t0.periodDateTo	>= @d1)
			)
		OR	(
				(t0.periodDateFrom	>= @d2)
			AND	(t0.periodDateTo	>= @d2)
			)
		)
	)
)

----------------------------------------------
/* 2. handle 1st clause same way - negate the whole clause and negate its 2 sub-clauses joined by OR and change OR to AND
*/
not
(
	not
	(
		not
		(
			not
			(
				(t0.periodDateFrom	<= @d1)
			AND	(t0.periodDateTo	>= @d1)
			)
		and
			not
			(
				(t0.periodDateFrom	<= @d2)
			AND	(t0.periodDateTo	>= @d2)
			)
		)
	)
and
 	not
	(
		(
			(
				(t0.periodDateFrom	<= @d1)
			AND	(t0.periodDateTo	<= @d1)
			)
		OR	(
				(t0.periodDateFrom	<= @d2)
			AND	(t0.periodDateTo	<= @d2)
			)
		)
	AND	(
			(
				(t0.periodDateFrom	>= @d1)
			AND	(t0.periodDateTo	>= @d1)
			)
		OR	(
				(t0.periodDateFrom	>= @d2)
			AND	(t0.periodDateTo	>= @d2)
			)
		)
	)
)

----------------------------------------------
/* 3. handle 2nd clause same way - for its 1st sub-clause, negate the whole sub-clause and the 2 sub-sub-clauses joined by OR and change OR to AND
*/
not
(
	not
	(
		not
		(
			not
			(
				(t0.periodDateFrom	<= @d1)
			AND	(t0.periodDateTo	>= @d1)
			)
		and
			not
			(
				(t0.periodDateFrom	<= @d2)
			AND	(t0.periodDateTo	>= @d2)
			)
		)
	)
and
 	not
	(
		not
		(
			not
			(
				(t0.periodDateFrom	<= @d1)
			AND	(t0.periodDateTo	<= @d1)
			)
		and
			not
			(
				(t0.periodDateFrom	<= @d2)
			AND	(t0.periodDateTo	<= @d2)
			)
		)
	AND	(
			(
				(t0.periodDateFrom	>= @d1)
			AND	(t0.periodDateTo	>= @d1)
			)
		OR	(
				(t0.periodDateFrom	>= @d2)
			AND	(t0.periodDateTo	>= @d2)
			)
		)
	)
)


----------------------------------------------
/* 4. finally handle 2nd clause 2nd sub-clause same way - negate the whole sub-clause and the 2 sub-sub-clauses joined by OR and change OR to AND
*/
not
(
	not
	(
		not
		(
			not
			(
				(t0.periodDateFrom	<= @d1)
			AND	(t0.periodDateTo	>= @d1)
			)
		and
			not
			(
				(t0.periodDateFrom	<= @d2)
			AND	(t0.periodDateTo	>= @d2)
			)
		)
	)
and
 	not
	(
		not
		(
			not
			(
				(t0.periodDateFrom	<= @d1)
			AND	(t0.periodDateTo	<= @d1)
			)
		and
			not
			(
				(t0.periodDateFrom	<= @d2)
			AND	(t0.periodDateTo	<= @d2)
			)
		)
	AND
		not
		(
			not
			(
				(t0.periodDateFrom	>= @d1)
			AND	(t0.periodDateTo	>= @d1)
			)
		and
			not
			(
				(t0.periodDateFrom	>= @d2)
			AND	(t0.periodDateTo	>= @d2)
			)
		)
	)
)
7

Hi,

I read your code and tested all parts and I must say that it returns valid values :). Thank you for explanation this code :).

Best regards,
Paul

8

phew!

thanks

another option would be to somehow
try and get round all the palaver associated with delegates, statement handlers and all that rubbish
and just write your own SQL directly
like any normal ordinary programming language would do

i have tried subverting it
and doing “statementHelper.setQueryString” etc.
but it isn’t giving me stable results

the freeform however looks somewhat promising

i am very lucky to have the luxury of time
and will continue for the nonce
to experiment
at least until i get sick of it
and decide to get back to doing something productive

9

Hi,

I did implementation of FreeformStatementDelegate (code I found on this forum but I did some modifications), and I did test for large single table and I must say that it works nice, but I’ve problem with sorting there. Source is here:

https://vaadin.com/forum/-/message_boards/view_message/1615402

Best regards,
Paul

10

yeah i’ve done a freeform query too
and it works fine
ordering can easily be done in the query order by clause

but as to the original post

i thought i should probably read the manual
and i am now totally confused
i can’t see any issue at all

the manual says:


mySQLContainer.addContainerFilter
(
    new Or	(
		new And	(
			new Equal("NAME", "Paul")
			,new Or	(
				new Less("AGE", 18)
				new Greater("AGE", 65)
				)
			)
		,new Like("NAME", "A%")
		)
);

gives SQL

WHERE	(
		(
			"NAME" = "Paul"
		AND	(
				"AGE" < 18
			OR	"AGE" > 65
			)
		)
	OR	"NAME" LIKE "A%"
	)

and this seems quite clear
i have tried an example on my DB and it works fine

so what is the problem?
why can’t you just do this:


Date d1 = Utils.cleanDateSetFirstDayOfMonth((Date) idfDatePeriodMonth.getValue());
Date d2 = Utils.cleanDateSetLastDayOfMonth((Date) idfDatePeriodMonth.getValue());

/* *
can't see what the following is for ?! */
int	d3;
int	int1	= ((YEAR(t0.periodDateFrom)*100)+(MONTH(t0.periodDateFrom)));
int	int2	= ((YEAR(t0.periodDateTo)*100)+(MONTH(t0.periodDateTo)));
/* */

containerTable.addContainerFilter
	(
		new Or
		(
			new Or
			(
				new And
				(
					new LessOrEqual("periodDateFrom", new Timestamp(d1.getTime()))
					,new GreaterOrEqual("periodDateTo", new Timestamp(d1.getTime()), 2)
				)
				,new And
				(
					new LessOrEqual("periodDateFrom", new Timestamp(d2.getTime()))
					,new GreaterOrEqual("periodDateTo", new Timestamp(d2.getTime()), 2)
				)
			)
			,new And
			(
				new Or
				(
					new And
					(
						new LessOrEqual("periodDateFrom", new Timestamp(d1.getTime()))
						,new LessOrEqual("periodDateTo", new Timestamp(d1.getTime()))
					)
					,new And
					(
						new LessOrEqual("periodDateFrom", new Timestamp(d2.getTime()))
						,new LessOrEqual("periodDateTo", new Timestamp(d2.getTime()))
					)
				)
				,new Or
				(
					new And
					(
						new GreaterOrEqual("periodDateFrom", new Timestamp(d1.getTime()), 2)
						,new GreaterOrEqual("periodDateTo", new Timestamp(d1.getTime()), 2)
					)	
					,new And
					(
						new GreaterOrEqual("periodDateFrom", new Timestamp(d2.getTime()), 2)
						,new GreaterOrEqual("periodDateTo", new Timestamp(d2.getTime()), 2)
					)
				)
			)
		)
	);

(once again assuming i haven’t made any typos!)

11

Hi,

Thank you for this help, but this problem is resolved. Actually I’ve problem with ordering by table (clicks on column’s header). Do you have any ideas why sorting doesn’t work properly in topic (table sorting limit cache size - how?) ?

If you don’t have idea, I’ve question how do you implements sorting on table? I cannot use like that:

	TableQuery tq = new TableQuery("view1", connectionPool, new MSSQLGenerator());
	container = new SQLContainer(tq);

Best regards,
Paul