Re: joining

Sun, 13 Jun 2004 07:37:43 -0700 

hi dennis,

you could try 'or-ing' the criteria for the 3 lists:

Criteria to = new Criteria();
Criteria cc = new Criteria();
Criteria bcc = new Criteria();


QueryByCriteria q;
Collection result;

// get all mails for contact 123
Integer id = new Integer(123);
to.addEqualTo("toList.id", id);
cc.addEqualTo("ccList.id", id);
bcc.addEqualTo("bccList.id", id);

to.addOrCriteria(cc);
to.addOrCriteria(bcc);

q = QueryFactory.newQuery(Mail.class, to, true);
result = broker.getCollectionByQuery(q);

i havn't tried it, but i think it should work ;)
btw. the field-descriptor no longer needs an id (as you still use in contact)

jakob

dennis bekkering wrote: 
Jakob,

Thank you for your reacttion. The Contact descriptor =

<class-descriptor class="nl.salesmakers.model.Contact" table="Contact">
  <field-descriptor id="30" name="dateOfBirth" column="dateofbirth"
jdbc-type="DATE"/>
  <field-descriptor id="31" name="email" column="email"
jdbc-type="VARCHAR"/>
  <field-descriptor id="32" name="gender" column="gender"
jdbc-type="VARCHAR"/>
  <field-descriptor id="33" name="mobilePhone" column="mobilephone"
jdbc-type="VARCHAR"/>
  <field-descriptor id="34" name="firstName" column="firstname"
jdbc-type="VARCHAR"/>
  <field-descriptor id="35" name="title" column="title"
jdbc-type="VARCHAR"/>
  <field-descriptor id="35" name="type" column="type" jdbc-type="VARCHAR"/>
  <field-descriptor id="36" name="companyId" column="companyid"
jdbc-type="INTEGER"/>
  <field-descriptor id="37" name="function" column="function"
jdbc-type="VARCHAR"/>
  <field-descriptor id="39" name="middleName" column="middlename"
jdbc-type="VARCHAR"/>
  <field-descriptor id="40" name="initials" column="initials"
jdbc-type="VARCHAR"/>
  <field-descriptor id="41" name="phone" column="phone"
jdbc-type="VARCHAR"/>
  <field-descriptor id="42" name="id" column="id" jdbc-type="INTEGER"
primarykey="true" autoincrement="true"/>
  <field-descriptor id="43" name="lastName" column="lastname"
jdbc-type="VARCHAR"/>
  <field-descriptor id="44" name="creationDate" column="creationdate"
jdbc-type="DATE"/>
  <field-descriptor id="45" name="searchName" column="searchname"
jdbc-type="VARCHAR"/>
  <field-descriptor id="46" name="privat" column="privat"
jdbc-type="VARCHAR"/>
  <field-descriptor id="47" name="userId" column="userid"
jdbc-type="INTEGER"/>
  <field-descriptor id="48" name="mailFooter" column="mailfooter"
jdbc-type="VARCHAR"/>
  <field-descriptor id="49" name="mailHeader" column="mailheader"
jdbc-type="VARCHAR"/>
  <reference-descriptor name="user" class-ref="nl.salesmakers.model.User">
    <foreignkey field-ref="userId"/>
  </reference-descriptor>
  <reference-descriptor name="company"
class-ref="nl.salesmakers.model.Company">
    <foreignkey field-ref="companyId"/>
  </reference-descriptor>
  <field-descriptor name="online" column="online" jdbc-type="INTEGER"/>
</class-descriptor>

Dennis

----- Original Message ----- From: "Jakob Braeuchi" <[EMAIL PROTECTED]>
To: "OJB Users List" <[EMAIL PROTECTED]>
Sent: Wednesday, June 09, 2004 9:39 PM
Subject: Re: joining



hi dennis,

how does the classdescriptor for Contact look ?

jakob

dennis bekkering wrote:


Hello,

I have class Mail with some collections that return contacts.

public class Mail
{
private Collection attachementList = new ArrayList();
private Collection bccList = new ArrayList();
private Collection ccList = new ArrayList();
private Collection toList = new ArrayList();
........
}

descriptor :

 <collection-descriptor name="toList"
element-class-ref="nl.salesmakers.model.Contact" auto-retrieve="true"
auto-update="true" auto-delete="false"


indirection-table="MailTo2Contact">

   <fk-pointing-to-this-class column="mailid"/>
   <fk-pointing-to-element-class column="contactId"/>
 </collection-descriptor>
 <collection-descriptor name="ccList"
element-class-ref="nl.salesmakers.model.Contact" auto-retrieve="true"
auto-update="true" auto-delete="false"


indirection-table="MailCc2Contact">

   <fk-pointing-to-this-class column="mailid"/>
   <fk-pointing-to-element-class column="contactId"/>
 </collection-descriptor>
 <collection-descriptor name="bccList"
element-class-ref="nl.salesmakers.model.Contact" auto-retrieve="true"
auto-update="true" auto-delete="false"


indirection-table="MailBcc2Contact">

   <fk-pointing-to-this-class column="mailid"/>
   <fk-pointing-to-element-class column="contactId"/>
 </collection-descriptor>


My sql query for getting all mail messages send to a certain contact :

" SELECT distinct m.* " +
" FROM " +
     " Mail m , " +
     " Contact c , " +
     " MailTo2Contact mtc ," +
     " MailCc2Contact mcc ," +
     " MailBcc2Contact mbc " +
" WHERE " +
      " (( m.id = mtc.mailid" +
          " AND" +
      " c.id = mtc.contactid )" +
 " OR " +
      " (m.id = mcc.mailid" +
          " AND" +
      " c.id = mcc.contactid )" +
 " OR " +
      " (m.id = mbc.mailid" +
          " AND" +
      " mbc.contactid = c.id   ))" +
 " AND " +
     " c.id = " + contact.getId() +
 " AND " +
     " m.flag = 'sent'" +
 " order by m.date desc"    +
 "";

I offcourse would like to use the pb API but dont know how. Any hints? I
cannot use select in , since mysql 4.0 does not support them and 4.1 is


not

in production.

regards,
Dennis

---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]




---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]



---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Reply via email to