Make "Exists" qualifiers work for you

created by Travis Cripps

demystified by Aaron Rosenzweig

aaron@chatnbike.com

Codivus, Inc.

Why EOF is Cool:

The Problem:

To-Many relationships are hard!

The Problem:

To-Many relationships are hard!

The Problem:

To-One relationships can be slow!

(sometimes)

The Problem:

To-One relationships can be slow!

(sometimes)

The Solution:

Use ERXExistsQualifer

(with Codivus improvements)

ERXExistsQualifier Improvements:

Simple Entity Model

Sample data

Companies with a chef (java)

EOQualifier qualifier =
        Company.EMPLOYEES.dot(Employee.PROFESSION)
        .containsObject(Profession.chef(editingContext()));

return Company.fetchCompanies(editingContext(),
                qualifier, null /*sortOrderings*/);

Companies with a chef (sql)

SELECT t0."id", t0."NAME"
FROM "COMPANY" t0
INNER JOIN "EMPLOYEE" T1
        ON t0."id" = T1."COMPANY_ID"
WHERE T1."PROFESSION_ID" = 1000002

Companies with a chef (sql) issues

Companies with a chef and a coder (java)

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*/);

Companies with a chef and a coder (sql)

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)

Companies with a chef and a coder (sql) issues

Companies with a chef and a coder (In Memory)

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);

Companies with a chef and a coder (In Memory) issues

Never Forget

Never Forget (addendum)

jdbcadaptor.frontbase.frontbaseContainsOperatorFix=true

Chef and a coder (Exists)

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*/);

Chef and a coder (Exists sql)

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"
        )
)

Chef and a coder (Exists converted to "IN")

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*/);

Chef and a coder (Exists converted to "IN" sql)

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
        )
)

Which is better Exists or In

Which is better Exists or In

Exists explanation

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" )

Exists explanation

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)

In explanation

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 )

In explanation

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.

For Dave Avendasora

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.

Companies with chef salary between 20k and 40k (java)

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*/);

Companies with chef salary between 20k and 40k (SQL)

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"
)

A to-one relationship example

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.

All employees that are chefs (java)

EOQualifier qualifier = new ERXExistsQualifier(
        Profession.NAME.is("Chef") /*subqualifier*/,
        Employee.PROFESSION.key() /*baseKeyPath*/);

return Employee.fetchEmployees(
        editingContext(), qualifier, null /*sortOrderings*/);

All employees that are chefs (SQL)

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" )

Real life example!

// (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

All companies with at least one employee (java)

How would you write this?

All companies with at least one employee (java)

How would you write this?

Normally it would be impossible.

"isNotNull" qualifiers don't work for to-Many relationships.

All companies with at least one employee (java)

EOQualifier qualifier = new ERXExistsQualifier(
                new ERXTrueQualifier() /*subqualifier*/,
                Company.EMPLOYEES.key() /*baseKeyPath*/,
                false /*usesInQualInstead*/);

return Company.fetchCompanies(
        editingContext(), qualifier, null /*sortOrderings*/);

All companies with at least one employee (SQL)

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" )

All companies with no employees (java)

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*/);

All companies with no employees (SQL)

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" ) )

Good to Know:

Mantra:

When you think you're stuck...

Exists! will dig you out.

Corollary:

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.

If it is too painful:

Consider using a pure object Database.

It might just be for the really tricky part of your business logic that you use an OODB.

Please take our pull request

SpaceForward
Left, Down, Page DownNext slide
Right, Up, Page UpPrevious slide
POpen presenter console
HToggle this help