db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Vitzethum, Daniel" <Daniel.Vitzet...@gigatronik.com>
Subject AW: nested select-statement
Date Fri, 20 May 2005 08:38:40 GMT
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: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org


Mime
View raw message