Interesting EOF Sorting Problem
Interesting EOF Sorting Problem
- Subject: Interesting EOF Sorting Problem
- From: Ben Ketteridge <email@hidden>
- Date: Thu, 26 Feb 2004 09:47:19 +0000
Hi,
I have a table of TariffBands that contain costing information, a minimum and
maximum cost, a fixed cost and a per-unit cost. The result of base + (unit *
quantity) must be between the minimum & maximum costs.
I have a requirement to fetch TariffBand records based upon a number of
criteria, sorted by calculated cost.
I can either read _all_ the potential TariffBands (filtered by my other
requirements), and then iterate, calculating the cost based on the quantity
parameter in my context, or I can try to get EOF/Oracle to do the work for me.
Because the quantity is a runtime parameter to the fetch, I can't put a
derived column in the model, more's the pity. So I thought I'd try Max
Muller's pre-fetch counting trick of putting the temporary "count(*)" derived
attribute on the entity in the loaded model.
Thus:
private static EOAttribute _objectSortAttribute = null;
private static final String COST_SORT_ATTRIBUTE_NAME =
"p_objectSortAttribute";
private static int _objectSortQuantity = 0;
private static EOAttribute objectSortAttribute(Number quantity) {
int intQuantity = quantity != null ? quantity.intValue() : 0;
if ( _objectSortAttribute == null || intQuantity != _objectSortQuantity )
{
_objectSortQuantity = intQuantity;
_objectSortAttribute = new EOAttribute();
_objectSortAttribute.setName(COST_SORT_ATTRIBUTE_NAME);
_objectSortAttribute.setColumnName(COST_SORT_ATTRIBUTE_NAME);
_objectSortAttribute.setClassName("java.lang.Number");
_objectSortAttribute.setValueType("d");
String baseString = "least(nvl(chargeMax, 9999999),
greatest(nvl(chargeMin, 0), nvl(chargeFlatRate, 0)";
if (intQuantity > 0) {
_objectSortAttribute.setReadFormat(baseString+" +
("+intQuantity+" * nvl(chargeUnitRate, 0))))");
}
else {
_objectSortAttribute.setReadFormat(baseString + "))");
}
_objectSortAttribute.setDefinition(COST_SORT_ATTRIBUTE_NAME);
}
return _objectSortAttribute;
}
And here's my fetch method:
EOEntity entity = // assume this is correct
EOSortOrdering sortOrdering = new EOSortOrdering(COST_SORT_ATTRIBUTE_NAME,
EOSortOrdering.CompareAscending);
NSMutableArray realSortOrderings = new NSMutableArray(sortOrdering);
if (sortOrderings != null && sortOrderings.count() > 0) {
realSortOrderings.addObjectsFromArray(sortOrderings);
}
synchronized (entity) {
EOAttribute attribute = objectSortAttribute(quantity);
entity.addAttribute(attribute);
EOFetchSpecification f = new EOFetchSpecification("TariffBand", q,
sortOrderings);
f.setRefreshesRefetchedObjects(true);
f.setFetchLimit(fetchLimit < 0 ? 0 : fetchLimit);
f.setRawRowKeyPaths(new NSArray(
new Object[] {"id", COST_SORT_ATTRIBUTE_NAME}));
array = editingContext.objectsWithFetchSpecification(f);
entity.removeAttribute(attribute);
}
NSMutableArray ma = new NSMutableArray(array.count());
for (int ii = 0, maxII = array.count(); ii < maxII; ii++) {
// using my own more resilient implementation of objectForRawRow
ma.addObject(objectForRawRow(editingContext, array.objectAtIndex(ii),
getClass().getName()));
}
But, the problem is that the SQL is then rendered 'incorrectly':
"SELECT t0.ID, least(nvl(chargeMax, 9999999), greatest(nvl(chargeMin, 0),
nvl(chargeFlatRate, 0) + (10 * nvl(chargeUnitRate, 0)))) FROM TARIFFBAND t0,
TARIFF T1 WHERE AND t0.TARIFFID = T1.ID ORDER BY t0.p_objectSortAttribute "
With the exception:
SQL State:42000 -- error code: 904 -- msg: ORA-00904:
"T0"."P_OBJECTSORTATTRIBUTE": invalid identifier
I've taken out the other where clauses as they don't seem to be relevant to
this problem.
Is there a way I can cause EOF to put the t0 alias into the select clause, and
to name the derived value so that it can be sorted?
Many thanks for your time.
--
Kind Regards
Ben.
Dr Ben Ketteridge
email@hidden
Team Leader,
ProAct International,
PO Box 100, Denbigh, UK.
Tel: 01745 817161 ext. 322
_______________________________________________
webobjects-dev mailing list | email@hidden
Help/Unsubscribe/Archives: http://www.lists.apple.com/mailman/listinfo/webobjects-dev
Do not post admin requests to the list. They will be ignored.