Hello all,
> is there a convenient way to do the following with Criteria:
>
> select distinct document_id from channel_document_latest
> where channel_id=26942 and document_id not in
> (select document_id from channel_document_latest where channel_id =
> 26945 or channel_id = 26944)
I'm not sure if I am right as I just had a quick look on your problem,
but I read on the Torque pages about the "ExtendedCriteria": there you
might encapsulate the subselect, represented as an Criteria object
itself, and create the proper statement.
You just need two additional methods, something like that. For a "not
in", you certainly have to modify it a bit. There's an extract from my
code:
----
public class ExtendedCriteria extends Criteria {
/**
* Adds a subselect from an existing criteria for one column
with
* the IN operator.
* @param columnname COLUMN IN (select foo from ...)
* @param criteria containing the subselect
*/
public ExtendedCriteria addIn(String columnname, Criteria
criteria) {
try {
String query = getSubQueryString(criteria);
// might have to adapt this to special adapter.
add(columnname, (Object) (columnname + " in ( "
+ query + " )"), CUSTOM);
} catch (TorqueException e) {
//Log.error( "Couldn't create sub select.", e);
// make it unchecked
throw new RuntimeException(e);
}
return this;
}
/**
* Returns the sql query from the given Criteria object.
* @param criteria
* @throws TorqueException when not exacltly one column was
specified
*/
private String getSubQueryString(Criteria criteria) throws
TorqueException {
if (criteria.getSelectColumns().size() != 1) {
throw new TorqueException("Only exactly one
column allowed in subselect.");
}
return BasePeer.createQueryString(criteria);
}
}
----
HTH,
Daniel
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
