created by Travis Cripps
demystified by Aaron Rosenzweig
Codivus, Inc.
(sometimes)
(sometimes)
EOQualifier qualifier = Company.EMPLOYEES.dot(Employee.PROFESSION) .containsObject(Profession.chef(editingContext())); return Company.fetchCompanies(editingContext(), qualifier, null /*sortOrderings*/);
SELECT t0."id", t0."NAME" FROM "COMPANY" t0 INNER JOIN "EMPLOYEE" T1 ON t0."id" = T1."COMPANY_ID" WHERE T1."PROFESSION_ID" = 1000002
EOQualifier chefQualifier = Company.EMPLOYEES.dot(Employee.PROFESSION) .containsObject(Profession.chef(editingContext())); EOQualifier compSciQualifier = Company.EMPLOYEES.dot(Employee.PROFESSION) .containsObject(Profession.computerScientist(editingContext())); EOQualifier combinedQualifier = new ERXAndQualifier(chefQualifier, compSciQualifier); return Company.fetchCompanies( editingContext(), combinedQualifier, null /*sortOrderings*/);
SELECT t0."id", t0."NAME" FROM "COMPANY" t0 INNER JOIN "EMPLOYEE" T1 ON t0."id" = T1."COMPANY_ID" WHERE (T1."PROFESSION_ID" = 1000004 AND T1."PROFESSION_ID" = 1000002)
EOQualifier chefQualifier = Company.EMPLOYEES.dot(Employee.PROFESSION) .containsObject(Profession.chef(editingContext())); EOQualifier compSciQualifier = Company.EMPLOYEES.dot(Employee.PROFESSION) .containsObject(Profession.computerScientist(editingContext())); EOQualifier combinedQualifier = new ERXAndQualifier(chefQualifier, compSciQualifier); return ERXArrayUtilities.filteredArrayWithQualifierEvaluation( allCompanies(), combinedQualifier);
jdbcadaptor.frontbase.frontbaseContainsOperatorFix=true
EOQualifier chefQualifier = new ERXExistsQualifier( Employee.PROFESSION.is( Profession.chef(editingContext())) /*subqualifier*/, Company.EMPLOYEES.key() /*baseKeyPath*/); EOQualifier compSciQualifier = new ERXExistsQualifier( Employee.PROFESSION.is( Profession.computerScientist(editingContext())) /*subqualifier*/, Company.EMPLOYEES.key() /*baseKeyPath*/); EOQualifier combinedQualifier = new ERXAndQualifier(chefQualifier, compSciQualifier); return Company.fetchCompanies( editingContext(), combinedQualifier, null /*sortOrderings*/);
SELECT t0."id", t0."NAME" FROM "COMPANY" t0 WHERE ( EXISTS ( SELECT exists0."id" FROM "EMPLOYEE" exists0 WHERE exists0."PROFESSION_ID" = 1000004 AND exists0."COMPANY_ID" = t0."id" ) AND EXISTS ( SELECT exists0."id" FROM "EMPLOYEE" exists0 WHERE exists0."PROFESSION_ID" = 1000002 AND exists0."COMPANY_ID" = t0."id" ) )
EOQualifier chefQualifier = new ERXExistsQualifier( Employee.PROFESSION.is( Profession.chef(editingContext())) /*subqualifier*/, Company.EMPLOYEES.key() /*baseKeyPath*/, true /*usesInQualInstead*/); EOQualifier compSciQualifier = new ERXExistsQualifier( Employee.PROFESSION.is( Profession.computerScientist(editingContext())) /*subqualifier*/, Company.EMPLOYEES.key() /*baseKeyPath*/, true /*usesInQualInstead*/); EOQualifier combinedQualifier = new ERXAndQualifier(chefQualifier, compSciQualifier); return Company.fetchCompanies( editingContext(), combinedQualifier, null /*sortOrderings*/);
SELECT t0."id", t0."NAME" FROM "COMPANY" t0 WHERE ( t0."id" IN ( SELECT exists0."COMPANY_ID" FROM "EMPLOYEE" exists0 WHERE exists0."PROFESSION_ID" = 1000004 ) AND t0."id" IN ( SELECT exists0."COMPANY_ID" FROM "EMPLOYEE" exists0 WHERE exists0."PROFESSION_ID" = 1000002 ) )
This SQL
SELECT t0."id", t0."NAME" FROM "COMPANY" t0 WHERE EXISTS ( SELECT exists0."id" FROM "EMPLOYEE" exists0 WHERE exists0."PROFESSION_ID" = 1000002 AND exists0."COMPANY_ID" = t0."id" )
Is executed like:
for x in ( select t0."id", t0."NAME" from "COMPANY" t0 ) loop if ( exists ( select null from "EMPLOYEE" exists0 where exists0."PROFESSION_ID" = 1000002 AND exists0."COMPANY_ID" = t0."id" ) ) then OUTPUT THE RECORD end if end loop
It always results in a full scan of T0 (Company)
This SQL
SELECT t0."id", t0."NAME" FROM "COMPANY" t0 WHERE t0."id" IN ( SELECT exists0."COMPANY_ID" FROM "EMPLOYEE" exists0 WHERE exists0."PROFESSION_ID" = 1000002 )
Is executed like:
select t0."id", t0."NAME" FROM "COMPANY" t0, ( SELECT DISTINCT exists0."COMPANY_ID" FROM "EMPLOYEE" exists0 WHERE exists0."PROFESSION_ID" = 1000002 ) t1 where t0."id" = t1."COMPANY_ID";
This does a full table scan of exists0 (EMPLOYEE) as the subquery is evaluated, distinct'ed, indexed and then joined to the original table.
An example with not only an EXISTS0 but also an EXISTS1 replacement. It happens when you have a complex subquery with one or more joins.
EOQualifier qualifier = new ERXExistsQualifier( Employee.PROFESSION.dot(Profession.NAME).is("Chef") .and( Employee.SALARY.greaterThanOrEqualTo(20000)) .and( Employee.SALARY.lessThanOrEqualTo(40000)) /*subqualifier*/, Company.EMPLOYEES.key() /*baseKeyPath*/); return Company.fetchCompanies( editingContext(), qualifier, null /*sortOrderings*/);
SELECT t0."id", t0."NAME" FROM "COMPANY" t0 WHERE EXISTS ( SELECT exists0."id" FROM "EMPLOYEE" exists0 INNER JOIN "PROFESSION" exists1 ON exists0."PROFESSION_ID" = exists1."id" WHERE ( exists1."NAME" = 'Chef' AND exists0."salary" >= 20000 AND exists0."salary" <= 40000 ) AND exists0."COMPANY_ID" = t0."id" )
9 times out of 10, regular joins will work just fine for to-one relationships. But, there are those odd times where the join is so painful that you will want to use an Esists! qualifier.
EOQualifier qualifier = new ERXExistsQualifier( Profession.NAME.is("Chef") /*subqualifier*/, Employee.PROFESSION.key() /*baseKeyPath*/); return Employee.fetchEmployees( editingContext(), qualifier, null /*sortOrderings*/);
SELECT t0."COMPANY_ID", t0."FIRST_NAME", t0."id", t0."LAST_NAME", t0."PROFESSION_ID", t0."salary" FROM "EMPLOYEE" t0 WHERE EXISTS ( SELECT exists0."id" FROM "PROFESSION" exists0 WHERE exists0."NAME" = 'Chef' AND exists0."id" = t0."PROFESSION_ID" )
// (Aaron Dec. 31, 2013) // using a join is much slower than the "in" clause // EOQualifier forQuestionQualifier = Answer.PLACED_QUESTION // .dot(PlacedQuestion.QUESTION).is(forQuestion); EOQualifier forQuestionQualifier = new ERXExistsQualifier( PlacedQuestion.QUESTION.is(forQuestion), Answer.PLACED_QUESTION.key(), true /*usesInQualInstead*/); * "Answer" table has 19,353,992 rows * "PlacedQuestion" table has 27,202 rows * "Question" table has 2,307 rows
How would you write this?
How would you write this?
Normally it would be impossible.
"isNotNull" qualifiers don't work for to-Many relationships.
EOQualifier qualifier = new ERXExistsQualifier( new ERXTrueQualifier() /*subqualifier*/, Company.EMPLOYEES.key() /*baseKeyPath*/, false /*usesInQualInstead*/); return Company.fetchCompanies( editingContext(), qualifier, null /*sortOrderings*/);
SELECT t0."id", t0."NAME" FROM "COMPANY" t0 WHERE EXISTS ( SELECT exists0."id" FROM "EMPLOYEE" exists0 WHERE 1=1 AND exists0."COMPANY_ID" = t0."id" )
EOQualifier qualifier = new ERXExistsQualifier( new ERXTrueQualifier() /*subqualifier*/, Company.EMPLOYEES.key() /*baseKeyPath*/, false /*usesInQualInstead*/); EOQualifier notQualifier = new ERXNotQualifier(qualifier); return Company.fetchCompanies( editingContext(), notQualifier, null /*sortOrderings*/);
SELECT t0."id", t0."NAME" FROM "COMPANY" t0 WHERE not ( EXISTS ( SELECT exists0."id" FROM "EMPLOYEE" exists0 WHERE 1=1 AND exists0."COMPANY_ID" = t0."id" ) )
When you think you're stuck...
Exists! will dig you out.
Exists! solves many problems but there may come a time you need something else.
Exists! will give you a template to make your own qualifier.
The Houdah frameworks are also quite good.
Consider using a pure object Database.
It might just be for the really tricky part of your business logic that you use an OODB.
Space | Forward |
---|---|
Left, Down, Page Down | Next slide |
Right, Up, Page Up | Previous slide |
P | Open presenter console |
H | Toggle this help |