Certiv Analytics

Complex IQL Query Specifications

Inductor provides a number of facilities, usable alone or in combination, for implementing simple to quite complex query specifications.  With reasonable choice, and subject to one caveat, the query specification remains quite readable regardless of the logical complexity of the query.  The Inductor facilities include:

1. Serial Specification of Field Criteria;
2. Field Sets and Field Arrays.
3. Conjunctions.

Serial Specification

For a given Field, the IQL criteria constraints that will be applied to that Field are specified serially by a trailing sequence of Field method calls.  By default, an and relationship is implied as joining each successive constraint.

(String literal parameters are used for illustration.  Field parameters can also be specified as variables and, in either case, can be of type String or Number.)

IQL:

employee.name.startsWith("B").endsWith("s").like("%ses%").neq("Branses");

Equivalent SQL:

 
employee.name LIKE 'B%' AND
employee.name LIKE '%s' AND
employee.name LIKE '%ses%' AND
employee.name <> 'Branses'

IQL Using Fields

 
q.where(employee.firstname.eq("Bill"),
        employee.lastname.eq("Jones"),
        employee.department.eq("Home") ); 

IQL Using Fields and a Field[]:

 
q.where(employee.firstname.eq("Bill"),
        employee.lastname.eq("Jones"),
        new Field[] { employee.department.eq("Home"),
                      employee.paygrade.gt(new Integer(4)),
                      employee.shift.gteq(new Integer(2)),
                      employee.storelocation.eq("CA")
                    } ); 

IQL Using Just a Field[]:

 
q.where(new Field[] { employee.firstname.eq("Bill"),
                      employee.lastname.eq("Jones"),
                      employee.department.eq("Home"),
                      employee.paygrade.gt(new Integer(4)),
                      employee.shift.gteq(new Integer(2)),
                      employee.storelocation.eq("CA")
                    } ); 

Conjunctions

The three conjunctions and(), or(), and not() can be used in-line to connect criteria acting on a given field and to define subclauses that relate additional Fields to the root Field of the conjunction.

In-Line:

 
q.where(employee.firstname.startsWith("B").or().startsWith("C"),
        employee.firstname.startsWith("J").or().startsWith("K"),
        employee.number.eq(12).or().gt(45) ); 

In-Line Connecting Fields:

 
q.where(employee.firstname.eq("Bill").or(),
        employee.lastname.eq("Smith") ); 

Connecting Sub-Clauses:

 
q.where(employee.firstname.startsWith("B").and(
              employee.lastname.endsWith("s").or(
                    employee.number.gt(45).or().eq(12) ))); 

Equivalent SQL:

 
  employee.firstname LIKE 'B%' AND 
( employee.lastname LIKE '%s' OR
( employee.number > 45 OR employee.number = 12 ) ) 

Conjunctions and Field Arrays:

 
q.where(employee.firstname.startsWith("Will"),
        employee.lastname.endsWith("s").or(
              new Field[] { employee.department.eq("Home"),
                    employee.title.startsWith("Chief").or(
                        employee.number.gt(45).or().eq(12) 
                    ) 
              }
       )
); 

Equivalent SQL:

 
 employee.firstname LIKE 'Will%' AND 
 employee.lastname LIKE '%s'  OR
 (  ( employee.department = 'Home' AND
      employee.title LIKE 'Chief%' OR
      ( employee.number > 45 OR employee.number = 12 )
     )
 ) 

Simplification Strategies

Just as with standard SQL, Inductor permits completely correct, but relatively incomprehensible queries to be constructed.  Unlike standard SQL, however, Inductor supports a number of effective strategies for making clear even the most complex of queries.

Logically correct:

 
q.where(person.zip.gt(aZip1).and(
           person.fname.like(firstName).and(
               person.lname.like(lastName)))));

Clarified:

 
q.where(person.zip.gt(aZip1),
        person.fname.like(firstName),
        person.lname.like(lastName) );

Rule Refactoring:

Logically correct:

 
q.where(new Field[] { project.jobtype.eq(aJob).and(
                      new Field[] {
                              project.city.eq(jobCity),
                              project.state.eq(jobState),
                              project.zip.eq(jobZip)
                      }).or(),
                project.contractor.eq(jobContractor).or(),
                project.workdate.gt(jobDate).and(),
                project.invoice.gteq(invoiceNumb).and(
				      new Field[] {
                              project.invoicetype.eq(jobType),
                              project.workcode.gt(jobCode)
                      })
        });

Clarified Using Rules:

 
Field[] validLoc = new Field[] { 
                project.city.eq(jobCity),
                project.state.eq(jobState), 
                project.zip.eq(jobZip) };

Field[] validJob = new Field[] { 
                project.invoicetype.eq(jobType), 
                project.workcode.eq(jobCode) };
	
q.where(new Field[] {
                project.jobtype.eq(aJob).and(validLoc).or(),
                project.contractor.eq(jobContractor).or(),
                project.workdate.gt(jobDate).and(),
                project.invoice.gteq(invoiceNumb).and(validJob)
        });

Caveat -- SQL evaluation order v. Java VM evaluation order:

Inductor implements SQL in objects.  SQL, however, uses a quite literal, linear evaluation logic.  That is, each SQL subclause is independent of all others and evaluated in basically left to right order.   Java also evaluates objects in a left to right order, but Field objects in different subclauses are actually the same objects.

Rather than using some intrusive initializer for every Field object to force independence, Inductor internally adapts the Java VM evaluation order!  A bit of a trick, but entirely portable.  As a result, Inductor subclauses are, like their SQL counterparts, mutually independent.

Unfortunately, nothing is for free: there is a caveat.  Just one, though, and it is pretty obscure. 

The caveat is: the first root Field in a subclause that is a child of a parent subclause cannot be the same Field as the root Field of the parent.

Caveat Illustrated:

 
    // this illustrates the caveat - the or() creates a child subclause
    //    and both parent a child start with the same Field zip ...
    q.where(person.zip.gt(aZip1).or(new Field[] {
                person.zip.lt(aZip2),
                person.lname.like(lastName)
                })
          );

    // this is OK, though
    q.where(person.zip.gt(aZip1).or(new Field[] {
                person.lname.like(lastname),
                person.zip.lt(aZip2)
                })
          );

    // peers work fine - effectively the same SQL logic
    q.where(person.zip.gt(aZip1).or(),
            new Field[] { 
                person.zip.lt(aZip2),
                person.lname.like(lastname)
            }
        ); 

In most cases -- hopefully all practical cases -- the goals of both esthetics and required query logic can be met without running afoul of the caveat.

Be sure to report any other instances where Inductor fails to properly implement SQL evaluation ordering, or any other problem for that matter.