3.1. Fop Expression Builder Filter

Filter

Filter queries with SQL equals

Here you can find basic examples of fop query usage with comparing the SQL equality

Fop Query	SQL
?filter=Midterm>90;Name~=A;and	select * from Students where (Midterm >90 and Name like '%a%')
?filter=Midterm>90;Name~=A	select * from Students where (Midterm >90 and Name like '%a%')
?filter=Midterm>90;Name~=A;or	select * from Students where (Midterm >90 or Name like '%a%')
?filter=Midterm>90;Name~=A;and$Final>=90	select * from Students where (Midterm >90 and Name like '%a%') or (Final >= 90)
?filter=Midterm>90;Name~=A$Final>=90	select * from Students where (Midterm >90 or Name like '%a%')
?filter=Midterm>90;Name~=A;or$Final>=90	select * from Students where (Midterm >90 or Name like '%a%') or (Final >= 90)
?filter=Midterm>90;Name~=A;and$Final>=90;Level=A	select * from Students where (Midterm >90 and Name like '%a%') or (Final >= 90 and Level = 'A')
?filter=Midterm>92;Name~=A;and$Name!|=A;Birthday<2012-06-07	select * from Students where (Midterm > 92 and Name like '%a%') or (Name not like '%a' and Birthday < '20120607' )

More and more and more...

1. /api/students?filter=Midterm>90;Name~=A;and

It returns students which midterm score more than 90 and Name contain 'A'

2. /api/students?filter=Midterm>90;Name~=A;and$Final>=90

Let's dived and understand.

$ -> Represent next logic filter. It added or between the where statements

filter= // filter is starting

Midterm>90;Name~=A;and // first filter

$Final>=90 // second filter

we put always or for next filter operation. so above expression equality of sql is

select * from Students
where (Midterm > 90 and Name like '%a%') 
or (Final >= 90)

3. Advanced querying

/api/students? filter= // Filter starting

// First expression here the end of the expression we have logic operator (and)

// the next filter expression we don't have any logic operator (and, or)

// If we don't put logic operator to end of the expression. Fop defaultly added and

Midterm>92;Name~=A;and

$ // seperator for multiple filter -> always or

Final>=90;Name!|=A;Birthday<2012-06-07 // Second expression

/api/students?

filter= 

Midterm>92;Name~=A;and 
$ 

Final>=90;Name!|=A;Birthday<2012-06-07 

Sql Equality

select * from Students
where (Midterm > 92 and Name like '%a%') -- First expression
or  -- $ -> always we use or for multiple filter expressions
(Final >= 90 and Name not like '%a' and Birthday < '20120607' ) -- Second expression