Re: [castor-dev] Querying parent depending on child value

[Martin, Margaret]

Title: Message

In my case, I have the same type of object relationship and FWIF, in my mapping file, Attribute depends on BusinessObject. I don't think I have any "like" queries, but I would try something like:   SELECT b FROM  BusinessObject b where name like %somevalue% and attributes.value like %someOtherValue%   there seem to be some tricks with the quoting, " seems to work where I thought I needed  '   Good luck - Margaret -----Original Message----- From: Abhay Kulkarni [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 1:57 PM To: [EMAIL PROTECTED] Subject: Re: [castor-dev] Querying parent depending on child value I have different case here, My table structure is like Table - BUSINESS_OBJECT (ID (Primary Key), NAME, CREATION_DATE) Table - ATTRIBUTE (ID(Primary Key), OBJECT_ID(FK ref. BUSINESS_OBJECT), VALUE)   Class structure BusinessObject -          id - Long -          name - String -          creationDate - Long -          attributes - List   Attribute -          id - Long -          busObj - BusinessObject -          value - String   I need a query to select BOs depending on attribute values In pure SQL, I need something like select * from BUSINESS_OBJECT b where b.name like '%blah%' and b.id exists in (select a.object_id from ATTRIBUTE a where a.value like '%blah%')   Any suggestions ?   Thanks Abhay     -----Original Message----- From: Martin, Margaret [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 11:38 AM To: [EMAIL PROTECTED] Subject: Re: [castor-dev] Querying parent depending on child value   No, I use oql like this:   SELECT count(*) FROM com.mystuff.MyClass j WHERE parent.id = $1   hope this helps -   Margaret -----Original Message----- From: Abhay Kulkarni [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 12:56 PM To: [EMAIL PROTECTED] Subject: Re: [castor-dev] Querying parent depending on child value Margaret   Do you use CALL SQL for select count(*)? I need to do something like that to make it working in place of exists().   Thanks Abhay     -----Original Message----- From: Martin, Margaret [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 7:52 AM To: [EMAIL PROTECTED] Subject: Re: [castor-dev] Querying parent depending on child value   Gotcha - fwiw, I've been using select count(*) successfully as a cheap replacement for exists()...     -----Original Message----- From: Smith, Kevin [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 9:41 AM To: [EMAIL PROTECTED] Subject: Re: [castor-dev] Querying parent depending on child value Margaret -   Apologies, I wasn't clear in my previous response. This form of the query works fine for me. Its when you need to use an operation like exists() or such that I ran into the limitations of the current OQL implementation.   --Kevin -----Original Message----- From: Martin, Margaret [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 4:45 PM To: [EMAIL PROTECTED] Subject: Re: [castor-dev] Querying parent depending on child value Kevin -   I have successfully used:   select p from com.blah.Parent p where p.child.someValue = $1   where Parent has a 1-m relationship with child (in my case, child depends on Parent)...  what sort of problems do you see when trying to do this?     Margaret Martin -----Original Message----- From: Smith, Kevin [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 3:35 PM To: [EMAIL PROTECTED] Subject: Re: [castor-dev] Querying parent depending on child value You really need to take a look at the online docs at castor.exolab.org. They have a pretty good discussion of using pass through SQL statements and stored procedures.   --Kevin -----Original Message----- From: Abhay Kulkarni [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 4:32 PM To: [EMAIL PROTECTED] Subject: Re: [castor-dev] Querying parent depending on child value How do you use *pass through SQL statements* ?     -----Original Message----- From: Smith, Kevin [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 1:25 PM To: [EMAIL PROTECTED] Subject: Re: [castor-dev] Querying parent depending on child value   "Aha!", he said and smacked his forehead. I should have picked up on this before. I hope someone else can prove me wrong, but I don't believe this is possible given the current state of OQL development in Castor. I wasn't able to get it to work and had to resort to pass through SQL statements.   --Kevin -----Original Message----- From: Abhay Kulkarni [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 4:06 PM To: '[EMAIL PROTECTED]' Subject: RE: [castor-dev] Querying parent depending on child value Actually it's a list. I have a list *attributes* to BO so its 1-n relationship.     -----Original Message----- From: Smith, Kevin [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 12:52 PM To: [EMAIL PROTECTED] Subject: Re: [castor-dev] Querying parent depending on child value   So, I think the query would look like this:   select bo from BusinessObject bo where bo.Attribute.value = $1   $1 == some parameter   --Kevins -----Original Message----- From: Abhay Kulkarni [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 3:49 PM To: [EMAIL PROTECTED] Subject: Re: [castor-dev] Querying parent depending on child value Kevin, Actually it is 1-n relationship. I have 2 tables , BusinessObject (with Object_Id as PK) and Attribute BusinessObject has Attribute List. Attribute has Object_Id as FK. I need to get a BusinessObject for which Attribute value is something ***   Thanks Abhay   -----Original Message----- From: Smith, Kevin [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 12:38 PM To: [EMAIL PROTECTED] Subject: Re: [castor-dev] Querying parent depending on child value   Do you mean something like this:   select p from com.blah.Parent p where p.child.someValue = $1   If so, that's definitely possible for one-to-one parent/child relationships. I haven't quite figured out how to do one-to-many parent/children using OQL.   --Kevin -----Original Message----- From: Abhay Kulkarni [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 15, 2002 3:34 PM To: [EMAIL PROTECTED] Subject: [castor-dev] Querying parent depending on child value Hi   I need to query for parent depending on value of one of the child table column. I have parent-child relation defined in XML mapping. How do I build the query using OQL ? Is it possible for OQL to select parent using filter for child ?   Thanks Abhay