Fetching Distinct Results
Synopsis
Describes how to use the EOFetchSpecification setUsesDistinct method to omit duplicate records during a fetch.
Discussion
Duplicate records sometimes appear in database fetches, usually when the fetch involves joining multiple tables to satisfy the fetch qualifier. The EOFetchSpecification allows you to specify whether you want these duplicate records with the setUsesDistinct method.The EOF adaptor modifies its query based on this setting. The standard relational database adaptors add the SQL keyword DISTINCT to the SQL Select statement when usesDistinct is set to YES.
DISTINCT keyword' and, therefore, allow duplicates.
The simplest way to fetch with no duplicate records is to explicitly create the EOFetchSpecification.
As an example, assume you want to fetch all of the movies from the Movies example database whose titles begin with "S" having one or more talents whose lastNames begin with "F").
Figure 1. Java Code
public NSArray movies;
public WOComponent fetch() {EOEditingContext ec=session().defaultEditingContext();
EOQualifier qual=EOQualifier.qualifierWithQualifierFormat
("title like 'S*' AND roles.talent.lastName like 'F*'", null); EOFetchSpecification fs=new EOFetchSpecification("Movie", qual, null);fs.setUsesDistinct(true);
movies=ec.objectsWithFetchSpecification(fs);
return null;
}
Figure 2. Objective-C Code
NSArray *movies=nil;
- (WOComponent *) fetch {EOEditingContext *ec=[[self session] defaultEditingContext];
EOQualifier *qual=[EOQualifier qualifierWithQualifierFormat:
@"title like 'S*' AND roles.talent.lastName like 'F*'"];
EOFetchSpecification *fs=[EOFetchSpecification
fetchSpecificationWithEntityName: @"Movie"
qualifier: qual sortOrderings: nil];
[fs setUsesDistinct: YES];
movies =[ec objectsWithFetchSpecification: fs];
return nil;
}
Figure 3. SQL Log
[<ODBCChannel: 0xa416a0> evaluateExpression: <ODBCSQLExpres sion: "SELECT DISTINCT t0.`CATEGORY`, t0.`DATE_RELEASED`, t0.`LANGUAGE`, t0.`MOVIE_ID`, t0.`POSTER_NAME`, t0.`REVENUE `, t0.`STUDIO_ID`, t0.`TITLE`, t0.`TRAILER_NAME` FROM `TALE NT` t2 , `MOVIE_ROLE` t1 , `MOVIE` t0 WHERE (t0.`TITLE` li ke ? AND t2.`LAST_NAME` like ?) AND t1.`TALENT_ID` = t2.`TA LENT_ID` and t0.`MOVIE_ID` = t1.`MOVIE_ID`" withBindings:(1 :S%(title), 2:F%(lastName))>]
Using DISTINCT with Large Data Types
Many databases do not allow the DISTINCT keyword on fetches that contain columns of large data types. These data types include BLOB, LONG RAW , LONG , and MEMO columns.
One technique to solve this problem is to split up the large data type columns into another table, and set up a to-one relationship from the master row to the row containing the large data types. This allows you to fetch using DISTINCT on all the columns except the large data type column. Once you have the desired master records, you can walk the to-one relationship and get the extra large data. This technique may also save time-very large data is not retrieved until it's needed.
Questions
· How can I eliminate duplicate records from my fetch?
· How can I fetch from the database?
· What is the EOF equivalent of the SQL DISTINCT keyword?
· How do I fetch distinct records with entities that contain BLOB s?
· How do I fetch distinct records using an EOFetchSpecification that I did not create?