John McNally wrote:
>
> Let me start off by saying I have been working on making the Peer system
> handle OR where clauses. And I have added the ability to alias tables
> as well.
>
I have the changes needed in Criteria/BasePeer to allow OR. I think I
need to test this some more before checking it in, if anyone has
interest in helping to test email ME and I will send you the
modifications, otherwise I would like to test to at least make sure
things work as they used to.
Please note the old way of adding items to the Criteria still exists. I
have just made it so that
you can grab an instance of the inner class to chain Criterion in a more
specific way. Separate Criterion added to a Criteria will still get
concatenated with an AND.
Please take a look at this (not very realistic, I am not using the alias
in the select columns for example) example for the added functionality
and give any helpful comments as to how this might be done better.
Criteria crit = new Criteria(3);
Criteria.Criterion c = crit.getNewCriterion("SCARAB_MODULE",
"MODULE_NAME", "name", Criteria.EQUAL);
Criteria.Criterion d = crit.getNewCriterion("SCARAB_MODULE",
"MODULE_DESCRIPTION", "%serious%", Criteria.LIKE);
Criteria.Criterion e = crit.getNewCriterion("f", "MODULE_URL",
"url", Criteria.EQUAL);
Criteria.Criterion f = crit.getNewCriterion("SCARAB_ISSUE",
"ISSUE_ID", new Integer(1), Criteria.GREATER_THAN);
crit.addAlias("f", "SCARAB_MODULE");
c.or(d);
e.or(f);
crit.add(c);
crit.add(e);
Vector srmvs =
ScarabRModuleUserPeer.doSelectJoinScarabModule(crit);
SELECT SCARAB_R_MODULE_USER.MODULE_ID, SCARAB_R_MODULE_USER.USER_ID,
SCARAB_R_MODULE_USER.DELETED, SCARAB_MODULE.MODULE_ID,
SCARAB_MODULE.MODULE_NAME, SCARAB_MODULE.MODULE_DESCRIPTION,
SCARAB_MODULE.MODULE_URL, SCARAB_MODULE.PARENT_ID,
SCARAB_MODULE.OWNER_ID, SCARAB_MODULE.QA_CONTACT_ID,
SCARAB_MODULE.DELETED
FROM SCARAB_R_MODULE_USER, SCARAB_MODULE, SCARAB_MODULE f, SCARAB_ISSUE
WHERE
(f.MODULE_URL='url' OR (SCARAB_ISSUE.ISSUE_ID>1))
AND
(SCARAB_MODULE.MODULE_NAME='name' OR (SCARAB_MODULE.MODULE_DESCRIPTION
LIKE '%serious%'))
Changing the order a bit to give (c and d) or (e and f)
c.and(d);
e.and(f);
c.or(e);
crit.add(c);
Vector srmvs =
ScarabRModuleUserPeer.doSelectJoinScarabModule(crit);
SELECT SCARAB_R_MODULE_USER.MODULE_ID, SCARAB_R_MODULE_USER.USER_ID,
SCARAB_R_MODULE_USER.DELETED, SCARAB_MODULE.MODULE_ID,
SCARAB_MODULE.MODULE_NAME, SCARAB_MODULE.MODULE_DESCRIPTION,
SCARAB_MODULE.MODULE_URL, SCARAB_MODULE.PARENT_ID,
SCARAB_MODULE.OWNER_ID, SCARAB_MODULE.QA_CONTACT_ID,
SCARAB_MODULE.DELETED
FROM SCARAB_R_MODULE_USER, SCARAB_MODULE, SCARAB_MODULE f, SCARAB_ISSUE
WHERE
(SCARAB_MODULE.MODULE_NAME='name' OR (f.MODULE_URL='url' AND
(SCARAB_ISSUE.ISSUE_ID>1))
AND (SCARAB_MODULE.MODULE_DESCRIPTION LIKE '%serious%'))
This last where clause is accurate assuming AND has higher priority than
OR. I believe this should be the case for all db's, anyone know
differently?
John McNally
>
> Now the proposal. There is some code in BasePeer that attempts to
> determine whether it should add a join to the where clause. Here is the
> condition
g
------------------------------------------------------------
To subscribe: [EMAIL PROTECTED]
To unsubscribe: [EMAIL PROTECTED]
Search: <http://www.mail-archive.com/turbine%40list.working-dogs.com/>
Problems?: [EMAIL PROTECTED]
