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

[Martin, Margaret]

Title: Message

Hi Abhay, I looked further into my query results and discovered that the queries are good if I do the selects for the entire object (even with a mix of AND and OR clauses). HOWEVER, if I do a select for a single column, or a count(*) with a where clause that references anything in any child object, then the query is not returning the results that I expected. It appears that the query for the entire object contains left outer joins, while the latter does not and that is the difference. It can be quite helpful to use the query analyzer to see the sql being generated for your query.   I am digging dipper into the query creation to try to solve this - if ANYONE out there has any tips where I should start looking, please let me know. I need this to work badly. I am on 0.9.3.9   Thanks - Margaret -----Original Message----- From: Abhay Kulkarni [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 22, 2002 1:11 PM To: [EMAIL PROTECTED] Subject: Re: [castor-dev] Querying parent depending on child value Hi Margaret,   Sorry for delay, I was out of the office for 2 days.   When I run the query with multiple attributes having AND/OR condition, one of the two things happen If it is AND condition, I get zero (0) rows selected. If it is OR condition, I get a union of all conditions.   Hope that helps.   Abhay   -----Original Message----- From: Martin, Margaret [mailto:[EMAIL PROTECTED]] Sent: Tuesday, May 21, 2002 5:50 PM To: [EMAIL PROTECTED] Subject: Re: [castor-dev] Querying parent depending on child value   Abhay, What were the results you were seeing when trying to run this type of query? I thought that this was working just fine for me, and now I am suspect...   Thanks - Margaret -----Original Message----- From: Abhay Kulkarni [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 3:15 PM To: [EMAIL PROTECTED] Subject: Re: [castor-dev] Querying parent depending on child value Yes it works if the query has *only one* column from ATTRIBUTE table. But it fails if I query on multiple columns from ATTRIBUTE table using and/or. e.g SELECT b FROM  BusinessObject b where (name like %somevalue%) and ((attributes.value like %someOtherValue%) and (attributes.lock != 1))   So I was planning to use exists, and realized Castor does not support it in phase 3. Then somebody suggested me to use CALL SQL to replace exists, which I am working on.   Thanks Abhay     -----Original Message----- From: Martin, Margaret [mailto:[EMAIL PROTECTED]] Sent: Friday, May 17, 2002 12:27 PM To: [EMAIL PROTECTED] Subject: Re: [castor-dev] Querying parent depending on child value   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 1.       id - Long 2.       name - String 3.       creationDate - Long 4.       attributes - List   Attribute 5.       id - Long 6.       busObj - BusinessObject 7.       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