Jakob,
I agree. I hope/believe the model is a db model is well thought out. In straight
SQL the solotion is perfect and trivial as shown by the few line query I gave you. It
becomes a bit more complicated representing it in OR mappings it seems and I am not as
good with that. I am taking in all of what we are talking about here before I
implement this part across all the services because you impart good ideas. Here is
what I was trying to do in a less abreviated manner.
Basically what I have is:
Many services (rstep is an example) each with a unique
primary key (msid) that spans all services. Each service
is housed in their own sets of tables (rstep1 .. rstepN).
The serviceinterest table gives the ability for one service to show
interest in another service, hense the double primary key
(interestmsid, targetmsid) making entries unique.
Basically it shows the primary key of one service being
interested in the primary key of the other service.
In general from this I can get two things. All of the services that my
service is interested in and all of the services interested in my
service. In the end, I need the serviceinterest table just
to create that connection, but I don't need its data as my
service would just like to see the collection of a type of service
interested in it.
Boggle. I hope you didn't follow all that. I believe that means that
the serviceinterest table creates a m:n type of connection
between the service tables.
In the end, it would be nice for a service (rstep1) to be able to say,
"give me the data of all the services interested in me" and
"give me the data of all the services I am interested in."
That was how I was thinking about the problem and you now have
me thinking "map a specific serviceinterest to a specific service
and I can get the collection of services that are interested in
my service." "Create another mapping from serviceinterest to
that same specific service to get the collection of services that
I am interested in."
It will work unlike mine, but it isn't as natural a fit. As I said before, it
kinda flips the problem upside down but matches the primary keys.
Thanks again for listening to this long windedness. I am learning!!!
John
[EMAIL PROTECTED]
----- Original Message -----
From: Jakob Braeuchi <[EMAIL PROTECTED]>
Date: Monday, March 15, 2004 1:09 pm
Subject: Re: Still no luck with this query
> hi john,
>
> a very important issue doing or-mapping is having a clean object-
> model and clean
> db-schema. you say service has to point to a serviceinterest
> (1:1), but why is
> it actually a 1:n relation ship ? what is the meaning of the
> columns targetmsid
> and interestmsid. to me this table looks like an m:n-connection-
> table, like the
> person_project table in the ojb-testcases.
>
> jakob
>
> [EMAIL PROTECTED] wrote:
> > Thank you so much Jakob. You help lots of people here.
> Hopefully when I feel I understand OR mappings a bit better I can
> help some as well.
> >
> > I had not responded right away because I have been thinking
> about your solution. You turned the problem upside down making
> the keys match which works.
> >
> > The only downside to the solution which has been having me do
> that thinking part is that the serviceinterest table/object "was"
> kinda generic. By turning this upside down, I now map a seperate
> serviceinterest object to each service rather then a service to a
> generic serviceinterest. Though not a big deal it doubles the
> number of required mappings and reduces a bit of the abstraction.
> It also creates/stores more objects then I was hoping it would
> because i was not hoping ot have to hold onto all of the
> serviceinterest data, but just the service data (rstep1 is just
> one service type in the bigger problem).
> >
> > All in all, however, this is a HUGE improvement over not being
> able to get the query to work at all!!!
> >
> > Thank you sir.
> >
> > JohnE
> >
> >
> > ----- Original Message -----
> > From: Jakob Braeuchi <[EMAIL PROTECTED]>
> > Date: Friday, March 12, 2004 12:13 pm
> > Subject: Re: Still no luck with this query
> >
> >
> >>hi john,
> >>
> >>[EMAIL PROTECTED] wrote:
> >>
> >>
> >>>Sorry for the confusion. I tried to abbreviate to make it
> >>
> >>easier to read.
> >>
> >>>
> >>>In the end, I want to do this query:
> >>>
> >>> SELECT r.msid, r.rtitle, r.brief
> >>> FROM rstep1 r, serviceinterest si
> >>> WHERE ((si.targetmsid = 101)
> >>> AND (si.interestmsid = r.msid))
> >>>
> >>>
> >>>
> >>>Based on these two tables and the repository I gave previously:
> >>> CREATE TABLE rstep1 (
> >>> msid BIGINT(20) NOT NULL,
> >>> rtitle VARCHAR(70) NOT NULL,
> >>> brief VARCHAR(255) NULL,
> >>> PRIMARY KEY(msid),
> >>> )
> >>>
> >>> CREATE TABLE serviceinterest (
> >>> targetmsid BIGINT(20) UNSIGNED NOT NULL,
> >>> interestmsid BIGINT(20) UNSIGNED NOT NULL,
> >>> targetmid BIGINT(20) UNSIGNED NOT NULL,
> >>> interestmid BIGINT(20) UNSIGNED NOT NULL,
> >>> PRIMARY KEY(targetmsid, interestmsid)
> >>> )
> >>>
> >>>
> >>>
> >>>si.targetmsid=101, the subquery and one part of the
> >>
> >>serviceinterest primary key, should generate a list of
> >>serviceinterests.>
> >>
> >>>I want to return the one-to-one associated rstep1
> >>
> >>(RServiceInterestVO) for each of the those serviceinterests that
> >>were generated.
> >>
> >>why don't you define a 1:1 relationship pointing from
> >>ServiceInterest to
> >>RServiceInterestVO. imo the relationship pointing in the other
> >>direction
> >>(RServiceInterestVO to ServiceInterest) should be a 1:n
> relationship.>>
> >><class-descriptor
> >>class="com.model.services.interest.RServiceInterestVO" table="rstep1">
> >>
> >> <field-descriptor
> >> name="memberServiceId"
> >> column="msid"
> >> jdbc-type="BIGINT"
> >> primarykey="true"
> >> />
> >>...
> >> <collection-descriptor
> >> name="serviceInterests"
>
> >>
> >>class-ref="com.model.memberservice.ServiceInterestVO"
> >> auto-retrieve="true"
> >> auto-update="false"
> >> auto-delete="false">
> >> <inverse-foreignkey field-ref="targetMemberServiceId"/>
> >> </collection-descriptor>
> >></class-descriptor>
> >>
> >><class-descriptor
> >>class="com.model.memberservice.ServiceInterestVO"
>
> >>
> >>table="serviceinterest">
> >>
> >> <field-descriptor
> >> name="targetMemberServiceId"
> >> column="targetmsid"
> >> jdbc-type="BIGINT"
> >> primarykey="true"
> >> />
> >> <field-descriptor
> >> name="interestMemberServiceId"
> >> column="interestmsid"
> >> jdbc-type="BIGINT"
> >> primarykey="true"
> >> />
> >>...
> >> <reference-descriptor
> >> name="targetService"
>
> >>
> >>class-ref="com.model.memberservice.RServiceInterestVO"
> >> auto-retrieve="true"
> >> auto-update="false"
> >> auto-delete="false">
> >> <foreignkey field-ref="targetMemberServiceId"/>
> >> </reference-descriptor>
> >>
> >></class-descriptor>
> >>
> >>
> >>then it should be possible to query:
> >>
> >>Criteria crit = new Criteria();
> >>crit.addEqualTo("serviceInterests.targetMemberServiceId", 101);
> >>Query query = new QueryByCriteria(RServiceInterestVO.class, crit);
> >>broker.getCollectionByQuery(query);
> >>
> >>jakob
> >>
> >>
> >>>crit.addEqualTo("serviceInterest.targetMemberServiceId", 101);
> >>>crit.addEqualToField("serviceInterest.interestMemberServiceId",
> >>
> >>"memberServiceId");>
> >>
> >>>Yes I am trying to join with this addEqualToField, but notice
> >>
> >>onto the 2nd half of the primary key.
> >>
> >>>
> >>>I know my mapping is wrong, bit I don't know how to give a
> >>
> >>correct mapping for this problem. That is where my problem
> lies.
> >>In every other query I have done, there was a simple one to one
> >>primary key mapping that INNER JOIN worked well on. Here, the
> >>primary key is not fully used in the subquery and actually
> >>prevents this from working.
> >>
> >>>Hense I am asking for a new idea or direction. I don't know
> >>
> >>how to proceed. I am trying to learn.
> >>
> >>>JohnE
> >>>
> >>>
> >>>
> >>>----- Original Message -----
> >>>From: Jakob Braeuchi <[EMAIL PROTECTED]>
> >>>Date: Thursday, March 11, 2004 1:17 pm
> >>>Subject: Re: Still no luck with this query
> >>>
> >>>
> >>>
> >>>>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]
> >>>>
> >>>>
> >>>
> >>>
> >>>
> >>>----------------------------------------------------------------
> -
> >>
> >>----
> >>
> >>>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]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
