RE: How to do a "SELECT DISTINCT"
RE: How to do a "SELECT DISTINCT"
- Subject: RE: How to do a "SELECT DISTINCT"
- From: <email@hidden>
- Date: Wed, 23 Mar 2005 09:51:11 +0100
- Thread-topic: How to do a "SELECT DISTINCT"
Hi!
The below code will get you distinct values for a given attribute of objects matching a given fetch specification. For each value returned there will be AT LEAST one row matching your query.
public static NSArray distinctAttributeWithFetchSpecificationAndBindings(
EOEditingContext ec,
String attributeName,
EOFetchSpecification fs,
NSDictionary bindings)
{
EOModelGroup modelGroup = EOUtilities.modelGroup(ec);
EOEntity entity = modelGroup.entityNamed(fs.entityName());
EOModel model = entity.model();
EODatabaseContext databaseContext =
EODatabaseContext.registeredDatabaseContextForModel(model, ec);
EOSQLExpression expression =
databaseContext.adaptorContext().adaptor().expressionFactory().createExpression(entity);
EOAttribute wantedAttribute = entity.attributeNamed(attributeName);
String name = "p_distinct" + wantedAttribute.name();
NSArray results = null;
expression.addSelectListAttribute(wantedAttribute);
synchronized (entity)
{
EOAttribute attribute = new EOAttribute();
attribute.setName(name);
attribute.setColumnName(name);
attribute.setClassName(wantedAttribute.className());
attribute.setValueType(wantedAttribute.valueType());
attribute.setReadFormat("DISTINCT " + expression.listString());
entity.addAttribute(attribute);
fs = fs.fetchSpecificationWithQualifierBindings(bindings);
fs.setFetchesRawRows(true);
fs.setRawRowKeyPaths(new NSArray(attribute.name()));
results = ec.objectsWithFetchSpecification(fs);
entity.removeAttribute(attribute);
}
return (NSArray) results.valueForKey(name);
}
BTW, this has thread-safety problems when working with multiple EOF stacks within one application.
Is this what you want?
Should you want complete rows, i.e. EOS, you might want to have a look at the BestMatchQualifier on my web page http://homepage.mac.com/I_love_my .
It's purpose is to find among the objects matching a given qualifier the one whose value for a certain attribute is closest to a given limit. Additionally you may define "uniquingPaths". The qualifier will then find one object for each combination of values the "uniquingPaths" lead to.
In your example the "uniquingPaths" would be an array with "attribute" for only element. You would have to define a value to approximate. E.g. you could go for the row with the highest timestamp. You would then get one result for each value of "attribute".
Pierre.
-----Original Message-----
From: webobjects-dev-bounces+pierre.bernard=email@hidden
[mailto:webobjects-dev-bounces+pierre.bernard=email@hidden]On
Behalf Of Chuck Hill
Sent: Tuesday, March 22, 2005 10:50 PM
To: WebObjects Dev Apple
Subject: Re: How to do a "SELECT DISTINCT"
I'm not sure that Sacha's suggestion is what you want. If you want to
get all of the rows that have a unique name in them, then EOF is not
going to be helpful. The reason is that EOF maps objects to some
external, persistent, representation. It is very, very concerned with
maintaining the connect of an object to its external representation.
If there are multiple rows with the same name, SELECT DISTINCT will
return the data from one of them, but which one is not known. So your
fetch, if it were possible, would be saying to EOF "get me some random
objects, but make sure they have unique names". It is just not built
for that. I can't even think of how to do this in SQL, return entire
rows where the values in column are unique, but its been a while since
I had to do things like that in SQL.
If you want a list of names to use in a lookup list, then you should
further normalize your data do that the unique names are held in their
own object / table.
Chuck
On Mar 22, 2005, at 1:23 PM, Sacha Mallais wrote:
> On Mar 22, 2005, at 1:03 pm, Sébastien Gruhier wrote:
>
>> I would like to perform a "select distinct" on my database.
>> Actually I do a fecthSpecification like this
>> EOFetchSpecification fetchSpec = new EOFetchSpecification("Table",
>> new
>> EOKeyValueQualifier("attribute",
>> EOQualifier.QualifierOperatorEqual,
>> new Integer(1)),
>> null);
>> editingContext.objectsWithFetchSpecification(fetchSpec);
>>
>> I get all elements from Table that have "attribute = 1". I would
>> like to add something like only with a distinct "name".
>> Any ideas?
>>
> RTFM:
> http://developer.apple.com/documentation/WebObjects/Reference/API/com/
> webobjects/eocontrol/
> EOFetchSpecification.html#setUsesDistinct(boolean)
>
>
--
Practical WebObjects - a book for intermediate WebObjects developers
who want to increase their overall knowledge of WebObjects, or those
who are trying to solve specific application development problems.
http://www.global-village.net/products/practical_webobjects
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden
**********************************************************************
This email and any files transmitted with it are intended solely for
the use of the individual or entity to whom they are addressed.
If you have received this email in error please notify the sender
of this message. (email@hidden)
This email message has been checked for the presence of computer
viruses; however this protection does not ensure this message is
virus free.
Banque centrale du Luxembourg; Tel ++352-4774-1; http://www.bcl.lu
**********************************************************************
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden