You can use ERXSqlHelper.limitExpressionForSQL, or here is a utility method I
used once to convert a fetchspec to one with limited SQL in the SQL hints. The
code is only used in one place in one project, so no guarantee of reliability
applies to it right now:
Usage Example:
// Fetch the most recent shipped message
ERXSortOrderings s = CTMessage.XKEY_OID.descs();
ERXFetchSpecification<CTMessage> fs = new
ERXFetchSpecification<CTMessage>(CTMessage.ENTITY_NAME,null,s);
fs = WKEOUtils.limitedFetchSpecification(ec, fs, 0, 1);
Utility method:
/**
* @param <T>
* @param ec
* @param spec
* @param start
* @param end
* @return a {...@link ERXFetchSpecification} clone having the custom
SQL
* "hints" dictionary populated with the fetch SQL and the SQL
has
* fetch LIMIT clause based on start and end params
*/
public static <T extends EOEnterpriseObject> ERXFetchSpecification<T>
limitedFetchSpecification(
EOEditingContext ec,
ERXFetchSpecification<T> spec, int start, int end) {
EOEntity entity = ERXEOAccessUtilities.entityNamed(ec,
spec.entityName());
EOModel model = entity.model();
spec = (ERXFetchSpecification<T>) spec.clone();
EOQualifier qualifier = spec.qualifier();
if (qualifier != null) {
qualifier =
EOQualifierSQLGeneration.Support._schemaBasedQualifierWithRootEntity(qualifier,
entity);
}
if (qualifier != spec.qualifier()) {
spec.setQualifier(qualifier);
}
if (spec.fetchLimit() > 0) {
spec.setFetchLimit(0);
spec.setPromptsAfterFetchLimit(false);
}
NSArray<EOAttribute> attributes =
attributesToFetchForEntity(spec, entity);
String sql = null;
EODatabaseContext dbc =
EOUtilities.databaseContextForModelNamed(ec, model.name());
EOAdaptor adaptor = dbc.adaptorContext().adaptor();
// Not sure why I have to lock this, but doing so because
// ERXEOControlUtilities#sqlExpression... does
dbc.lock();
try {
EOSQLExpressionFactory sqlFactory =
adaptor.expressionFactory();
EOSQLExpression sqlExpr =
sqlFactory.selectStatementForAttributes(attributes, false, spec, entity);
sql = sqlExpr.statement();
// We completely ignore any existing SQL "hints"
dictionary
ERXSQLHelper sqlHelper = ERXSQLHelper.newSQLHelper(dbc);
if (end >= 0) {
sql = sqlHelper.limitExpressionForSQL(sqlExpr,
spec, sql, start, end);
}
} catch (Exception e) {
throw new NestableRuntimeException(e);
} finally {
dbc.unlock();
}
NSDictionary<String, String> hints = new NSDictionary<String,
String>(sql,
EODatabaseContext.CustomQueryExpressionHintKey);
spec.setHints(hints);
return spec;
}
On Jul 12, 2010, at 11:27 AM, Mark Gowdy wrote:
>
> On 2 Mar 2009, at 23:43, Chuck Hill wrote:
>
>>
>> On Mar 2, 2009, at 1:42 PM, Kieran Kelleher wrote:
>>
>>> I always thought that EOFetchSpecification.setFetchLimit( int n) would
>>> generate SQL with LIMIT n tacked onto the end of the SQL statement, but it
>>> appears not to.
>>
>> This is the default behavior as not all databases implement this. Specific
>> plugins add this, IIRC.
>>
>> Chuck
>
>
> Hi guys,
>
> I have just stumbled into the same issue here:
>
> I need to fetch the latest item from a table in a MySQL database, where there
> are almost half a million items
> I thought that fetchSpec.setFetchLimit(1) would tell the DB to do the hard
> work, but this appears not to be the case.
> Consequently, my app is exploding with 'out of memory' issues.
>
> Is there a work around for this problem (some plug-in I am unaware of), or do
> I need to get creative with raw-rows?
>
> A solution would be good, because I was using these all over the place
> (without knowledge of the consequences)
>
> Thanks again,
>
> Mark
>
>>
>>> Instead it fetches the all qualifying records and does the LIMIT in
>>> memory?! Has this always been the case .... or is it just the MySQL driver?
>>>
>>> For example:
>>>
>>> <snip>
>>> // Fetch the most recent shipped message
>>> ERXSortOrderings s = CTMessage.XKEY_OID.descs();
>>>
>>> ERXFetchSpecification<CTMessage> fs = new
>>> ERXFetchSpecification<CTMessage>(CTMessage.ENTITY_NAME,null,s);
>>>
>>> fs.setFetchLimit(1);
>>>
>>> System.out.println("MailingAddressUtilitiesTests.suppressionTest(): fs.SQL
>>> = " + ERXEOAccessUtilities.sqlForFetchSpecification(ec, fs));
>>>
>>> NSArray<CTMessage> objects = fs.fetchObjects(ec);
>>> </snip>
>>>
>>>
>>> ..... generates the following SQL without "LIMIT 1" !!!
>>>
>>> SELECT t0.oid, t0.oidcampaign, t0.oiddatarecord, t0.oidjob, t0.oidlocation,
>>> t0.oidmediatemplate, t0.recaddtime, t0.recadduser, t0.recmodtime,
>>> t0.recmoduser, RTRIM(t0.workflowstate) FROM message t0 ORDER BY t0.oid DESC;
>>> _______________________________________________
>>> Do not post admin requests to the list. They will be ignored.
>>> Webobjects-dev mailing list ([email protected])
>>> Help/Unsubscribe/Update your Subscription:
>>> http://lists.apple.com/mailman/options/webobjects-dev/chill%40global-village.net
>>>
>>> This email sent to [email protected]
>>>
>>
>> --
>> Chuck Hill Senior Consultant / VP Development
>>
>> Practical WebObjects - for developers who want to increase their overall
>> knowledge of WebObjects or who are trying to solve specific 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 protected])
>> Help/Unsubscribe/Update your Subscription:
>> http://lists.apple.com/mailman/options/webobjects-dev/gowdy%40mac.com
>>
>> This email sent to [email protected]
>
> _______________________________________________
> Do not post admin requests to the list. They will be ignored.
> Webobjects-dev mailing list ([email protected])
> Help/Unsubscribe/Update your Subscription:
> http://lists.apple.com/mailman/options/webobjects-dev/kieran_lists%40mac.com
>
> This email sent to [email protected]
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list ([email protected])
Help/Unsubscribe/Update your Subscription:
http://lists.apple.com/mailman/options/webobjects-dev/archive%40mail-archive.com
This email sent to [email protected]
