Re: Still no luck with this query

[Jakob Braeuchi]

hi john,

you have a 1:1-relationship pointing from RServiceInterestVO to 
ServiceInterestVO, is this correct ? a similar relationship can be found in the 
samples (ProductGroup to Article).
the pk of RServiceInterestVO is used as fk to ServiceInterestVO in your 
repository. but the comment says it's wrong.

according to the repository each class has a single pk; but the table 
serviceinterest uses a combined pk ? and i cannot find a create table for 
rstep1. pk definition in create table and the repository must match !

crit.addEqualTo("serviceInterest.targetMemberServiceId", jMsid);
crit.addEqualToField("serviceInterest.interestMemberServiceId", "memberServiceId");
dou you try to force a join with addEqualToField ?

jakob

[EMAIL PROTECTED] wrote:

Thank you for responding Jakob.

Again this is the query I would like:

        SELECT r.msid, r.rtitle, r.brief
        FROM rstep1 r, serviceinterest si
        WHERE ((si.targetmsid = 101)
              AND (si.interestmsid = r.msid))
Below was my first of many attempts to solve this problem, but it wouldn't work right using RC4 because it tries to form the INNER JOIN from the memberServiceId to the first of two part primary key in the second table. I had not tried this particular method in RC5, but tried other methods using RC5.  

I know below is wrong, but I don't know the right strategy for what I need.   I can't seem to figure out how to handle subqueries not off the primary key.

I want a collection of these back:

<class-descriptor class="com.model.services.interest.RServiceInterestVO" table="rstep1">
    <field-descriptor
        name="memberServiceId"
        column="msid"
        jdbc-type="BIGINT"
        primarykey="true"
    />
    <field-descriptor
        name="rTitle"
        column="rtitle"
        jdbc-type="VARCHAR"
    />
    <field-descriptor
        name="brief"
        column="brief"
        jdbc-type="VARCHAR"
    />
  

    <!-- This would be wrong, as I don't want to go off of ServicenterestVO's
                  primary key, but use this object in a subquery.  -->
    <reference-descriptor
            name="serviceInterest"
            class-ref="com.model.memberservice.ServiceInterestVO"
            auto-retrieve="true"
            auto-update="false"
            auto-delete="false">
        <foreignkey field-ref="memberServiceId"/>
    </reference-descriptor>
</class-descriptor>





This is what the subquery table looks like:

<class-descriptor class="com.model.memberservice.ServiceInterestVO" 
table="serviceinterest">
        <field-descriptor
        name="targetMemberServiceId"
        column="targetmsid"
        jdbc-type="BIGINT"
        primarykey="true"
    />
        <field-descriptor
        name="targetMemberId"
        column="targetmid"
        jdbc-type="BIGINT"
        indexed="true"
    />
        <field-descriptor
        name="interestMemberServiceId"
        column="interestmsid"
        jdbc-type="BIGINT"
        primarykey="true"
    />
        <field-descriptor
        name="interestMemberId"
        column="interestmid"
        jdbc-type="BIGINT"
        indexed="true"
    />
</class-descriptor>




This failed as well as other things:
        crit.addEqualTo("serviceInterest.targetMemberServiceId", jMsid);
        crit.addEqualToField("serviceInterest.interestMemberServiceId", 
"memberServiceId");
        Collection result = 
GenericDAO.getInstance().findCollectionByCriteria(objectClass, crit);






----- Original Message -----
From: Jakob Braeuchi <[EMAIL PROTECTED]>
Date: Wednesday, March 10, 2004 1:25 pm
Subject: Re: Still no luck with this query

hi john,

you should at least provide some information about the repository 
you use.

jakob

[EMAIL PROTECTED] wrote:


Dear all,

Maybe I wasn't asking my question well because it had not gained 
a response in about a week.  Maybe I am missing something very 
simple, but I am still pretty new to any kinda OR-mappings and I 
have nobody who can help but this list.  Rather then try to 
explain the 4 different ways I tried to solve it again, how might 
people with better understanding.   I can not find an answer to 
this in the mailing lists nor in the docs.  If you have a 
reference, please give me a pointer.

I am trying to get a listing of all of the services ('aservice') 
that are interested in my particular service (having id 101).

// The query I would like agressively abreviated:
SELECT r.msid, r.rtitle, r.brief
FROM aservice r, serviceinterest si
WHERE ((si.targetmsid = 101)
     AND (si.interestmsid = r.msid))
// I want a collection of these returned
CREATE TABLE aservice (
 msid BIGINT(20) NOT NULL,
 rtitle VARCHAR(70) NOT NULL,
 brief VARCHAR(255) NULL,
 PRIMARY KEY(msid),
)
// Note a duo key that should not be referenced in whole by 
aservice:> CREATE TABLE serviceinterest (

 targetmsid BIGINT(20) UNSIGNED NOT NULL,
 interestmsid BIGINT(20) UNSIGNED NOT NULL,
 PRIMARY KEY(targetmsid, interestmsid)
)
I have been having problems because of foreign keys and how 
INNER JOIN seems to work with this query.

Thank you if you can.  I will gladly post more detailed 
information or email the code as need be.

JohnE
[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]

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