I will think about what you said. I have read the advanced manual so many times, but somehow didn't think about that as the solution. Soon enough OR mappings will flow like SQL does, but not yet!!! I have such a "common parent" for another piece of the model, but hadn't here.
Thank you for your suggestions. John ----- Original Message ----- From: Laurie Harper <[EMAIL PROTECTED]> Date: Tuesday, March 16, 2004 6:22 pm Subject: Re: Still no luck with this query > If all you're interested in is managing an N:M relationship > between > instances of a particular type (rows in a particular table), you > just > need a regular N:M mapping. See the Advanced OR Mapping tutorial > section > on this [1]. That takes care of if there's just one object/table > involved. The only difference is you end up with two collection > descriptors on one class descriptor, to describe the two sides of > the > relationship. > > If your object model has a different class for each service > (rstep1..N) > you need to define a pair of collection descriptors for each class. > > Finally, you'll probably want all the services to have a common > parent > class and have the collections be of that type, so you have the > same API > for getting the who's interested / who am I interested in data > accross > all services. > > Hope that helps, > > L. > > [1] http://db.apache.org/ojb/tutorial3.html#Mapping+m:n+associations > > [EMAIL PROTECTED] wrote: > > > 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] > > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
