db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Leandro Rodrigo Saad Cruz <lean...@ibnetwork.com.br>
Subject Re: query for extents joining with null table
Date Wed, 09 Apr 2003 19:15:42 GMT
NOT SURE.. but I think this query only uses contents that are related to
topics and not categories !

I'm not an SQL expert, but I think thats the way it should be. What do
you think ?

On Wed, 2003-04-09 at 16:09, Jakob Braeuchi wrote:
> hi leandro,
> 
> why do you think an outer join would be appropriate ?
> 
> jakob
> 
> Leandro Rodrigo Saad Cruz wrote:
> 
> >On Wed, 2003-04-09 at 15:12, Jakob Braeuchi wrote:
> >
> >the problem is related to the first join of the SQL query.. it should be
> >an outer join, not a inner join !
> >
> >SELECT A0.HEADLINE,A0.ID 
> >FROM NEWS A0 INNER JOIN CONTENT_QUALIFIER A1 ON A0.ID=A1.CONTENT_ID
> >INNER JOIN TOPIC A2 ON A1.QUALIFIER_ID=A2.ID  <------------------- ERROR
> >LEFT OUTER JOIN CATEGORY A2E1 ON A1.QUALIFIER_ID=A2E1.ID 
> >WHERE A2.NAME =  'Cars'  OR A2E1.NAME =  'Cars'
> >
> >  
> >
> >>hi leandro,
> >>
> >>what does the sql look like ?
> >>imo this scenario can be simulated by changing the collection-descriptor 
> >>of ProductGroup to point to AbstractArticle instead of Article, or do 
> >>you define both Qualifier and BaseQualifierImpl in the repository ?
> >>
> >>jakob
> >>
> >>
> >>
> >>Leandro Rodrigo Saad Cruz wrote:
> >>
> >>    
> >>
> >>>Hi jacob. I wrote a test case to be used on path expressions testing.
> >>>
> >>>This is the class diagram:
> >>>
> >>>  _____________                    _______________
> >>> |             |                 *|               |
> >>> |<<interface>>|           ------ | <<interface>>
| 
> >>> |   Content   |          |       |   Qualifier   |
> >>> |_____________|          |       |_______________|
> >>>    ^                     |              ^
> >>>  __|______________       |        ______|____________
> >>> |                 |      |       |                   |
> >>> |  <<abstract>>   |------        |   <<abstract>>
   |
> >>> | BaseContentImpl |*             | BaseQualifierImpl |
> >>> |_________________|              |___________________|
> >>>         ^                                  ^
> >>>         |                                  |
> >>>   ------------                         -------------
> >>>   |          |                         |           |             
> >>> ______    _______                   _______    __________
> >>>|      |  |       |                 |       |  |          | 
> >>>| News |  | Paper |                 | Topic |  | Category |
> >>>|______|  |_______|                 |_______|  |__________|
> >>>
> >>>
> >>>This is the test data that we have :
> >>>
> >>>   <News Id="1" Headline="Healf Care News (NONE)"/>
> >>>   <News Id="2" Headline="Ferrai for sale (CARS - BUYERS)"/>
> >>>   <News Id="3" Headline="Brazil World Cup Champion ! (SPORTS)"/>
> >>>
> >>>   <Paper Id="10" Date="today" Author="NONE"/>
> >>>   <Paper Id="11" Date="yesterday" Author="SALES MAN"/>
> >>>
> >>>   <Category Id="1" Name="Cars" />
> >>>   <Category Id="2" Name="Sports" />
> >>>
> >>>   <Topic Id="10" Name="Buyers" />
> >>>   <Topic Id="11" Name="Sellers" />
> >>>
> >>>   <ContentQualifier ContentId="2" QualifierId="1"/>
> >>>   <ContentQualifier ContentId="2" QualifierId="10"/>
> >>>   <ContentQualifier ContentId="3" QualifierId="2"/>
> >>>   <ContentQualifier ContentId="11" QualifierId="11"/>
> >>>
> >>>
> >>>So for example. if we query like this
> >>>
> >>>...
> >>>criteria.addEqualTo("qualifiers.name","Cars");
> >>>Query query = new QueryByCriteria(BaseContentImpl.class,criteria)
> >>>...
> >>>
> >>>The result of this query should have one instance of News with id=1.
> >>>The TestCase I wrote checks for this, and its failing because the result
> >>>is ZERO lenght.
> >>>
> >>>AFAIK the SQL query doesn't include tables for Qualifier or
> >>>BaseQualifierImpl but I think its not correct yet !
> >>>I don't know much about INNER | OUTER joins so I can't tell you what I
> >>>think is wrong .
> >>>
> >>>Could you help me ??
> >>>
> >>>
> >>>
> >>>
> >>>On Tue, 2003-04-08 at 15:04, Jakob Braeuchi wrote:
> >>> 
> >>>
> >>>      
> >>>
> >>>>hi leandro,
> >>>>
> >>>>afaik the problem was statement caching.
> >>>>did you have a look at the java class ?
> >>>>
> >>>>jakob
> >>>>
> >>>>Leandro Rodrigo Saad Cruz wrote:
> >>>>
> >>>>   
> >>>>
> >>>>        
> >>>>
> >>>>>Not sure !
> >>>>>
> >>>>>My early implementation included one RSIterator per concrete class
. I
> >>>>>don't know what was your rationale to perform only one stmt on the
> >>>>>database ! I'll write the test to help us on this discution !
> >>>>>
> >>>>>On Tue, 2003-04-08 at 14:12, Jakob Braeuchi wrote:
> >>>>>
> >>>>>
> >>>>>     
> >>>>>
> >>>>>          
> >>>>>
> >>>>>>hi leandro,
> >>>>>>
> >>>>>>the problem is the relationship referencing an abstract class.
in the 
> >>>>>>constructor of TableAlias an alias with NULL table is created.
> >>>>>>i'm thinking of completely skipping this abstract alias, but
 how ??
> >>>>>>
> >>>>>>jakob
> >>>>>>
> >>>>>>
> >>>>>>Jakob Braeuchi wrote:
> >>>>>>
> >>>>>>  
> >>>>>>
> >>>>>>       
> >>>>>>
> >>>>>>            
> >>>>>>
> >>>>>>>hi leandro,
> >>>>>>>
> >>>>>>>method createTableAlias checks abstract classes :
> >>>>>>>
> >>>>>>> private TableAlias createTableAlias(ClassDescriptor cld,
String path)
> >>>>>>> {
> >>>>>>>     TableAlias alias;
> >>>>>>>     boolean lookForExtents = false;
> >>>>>>>
> >>>>>>>     if (cld.isAbstract())
> >>>>>>>     {
> >>>>>>>         getLogger().warn("Creating TableAlias for abstract

> >>>>>>>ClassDescriptor: " + cld.getClassNameOfObject());
> >>>>>>>     }
> >>>>>>>        if (!cld.getExtentClasses().isEmpty() &&
path.length() > 0)
> >>>>>>>     {
> >>>>>>>         lookForExtents = true;
> >>>>>>>     }
> >>>>>>>
> >>>>>>>     alias = new TableAlias(cld, "A" + m_pathToAlias.size(),

> >>>>>>>lookForExtents);
> >>>>>>>     m_pathToAlias.put(path, alias);
> >>>>>>>     return alias;
> >>>>>>>
> >>>>>>>
> >>>>>>>jakob
> >>>>>>>
> >>>>>>>Leandro Rodrigo Saad Cruz wrote:
> >>>>>>>
> >>>>>>>    
> >>>>>>>
> >>>>>>>         
> >>>>>>>
> >>>>>>>              
> >>>>>>>
> >>>>>>>>Hi Jacob !
> >>>>>>>>
> >>>>>>>>I'm upgrading from 0.9.8 to 1.0.rc2 an I got this problem
:
> >>>>>>>>
> >>>>>>>>_________________       ________________
> >>>>>>>>|                |     |                |
> >>>>>>>>| AbstractClassA |1---*| AbstractClassB |
> >>>>>>>>|________________|     |________________|
> >>>>>>>>     ^                       ^
> >>>>>>>>     |                       |
> >>>>>>>>-------------          ---------------     |        
  |          
> >>>>>>>>|              |
> >>>>>>>>____         ____       ____          ____
> >>>>>>>>|    |       |    |     |    |        |    |
> >>>>>>>>| A1 |       | A2 |     | B1 |        | B2 |
> >>>>>>>>|____|       |____|     |____|        |____|
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>When I use OJB like this :
> >>>>>>>>
> >>>>>>>><java>
> >>>>>>>>Criteria crit = new Criteria()
> >>>>>>>>crit.addEqualTo("allBs.name",someName)
> >>>>>>>>QueryByCriteria query = new QueryByCriteria(AbstractClassA.class,crit);
> >>>>>>>>Collection result = broker.gteCollectionByQuery(query)
> >>>>>>>><java>
> >>>>>>>>
> >>>>>>>>I get an error because ojb is joinning with three tables
> >>>>>>>>AbstractClassB - NO TABLE
> >>>>>>>>B1             - TABLE_B_1
> >>>>>>>>B2             - TABLE_B_2
> >>>>>>>>
> >>>>>>>><sql>
> >>>>>>>>SELECT * FROM TABLE_A_1 A0 INNER JOIN ((((((TABLE_A_VERSUS_B
A1 INNER 
> >>>>>>>>JOIN null
> >>>>>>>>A2 ON A1.AID_ID=A2.AID) LEFT OUTER JOIN TABLE_B_1 A2E1
ON
> >>>>>>>>A1.AID=A2E1.ID) LEFT OUTER JOIN TABLE_B_2 A2E2 ON ...
> >>>>>>>></sql>
> >>>>>>>>
> >>>>>>>>We are trying to join with the table that AbstractClassB
should have,
> >>>>>>>>but it doesn't ! I looked at SqlQueryStatement.java and
I'm not sure
> >>>>>>>>where I have to make modifications for this to work.
> >>>>>>>>
> >>>>>>>>When I first implemented extent aware path expressions
I put a log
> >>>>>>>>warning (see createTableAlias(ClassDescriptor cld, String
path)) about
> >>>>>>>>abstract classes, but I think there is not place anymore
where we check
> >>>>>>>>for this !
> >>>>>>>>
> >>>>>>>>Can you help me solve this problem !
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>
> >>>>>>>>      
> >>>>>>>>
> >>>>>>>>           
> >>>>>>>>
> >>>>>>>>                
> >>>>>>>>
> >>>>>>>---------------------------------------------------------------------
> >>>>>>>To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> >>>>>>>For additional commands, e-mail: ojb-dev-help@db.apache.org
> >>>>>>>
> >>>>>>>
> >>>>>>>    
> >>>>>>>
> >>>>>>>         
> >>>>>>>
> >>>>>>>              
> >>>>>>>
> >>>>>>---------------------------------------------------------------------
> >>>>>>To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> >>>>>>For additional commands, e-mail: ojb-dev-help@db.apache.org
> >>>>>>
> >>>>>>
> >>>>>>  
> >>>>>>
> >>>>>>       
> >>>>>>
> >>>>>>            
> >>>>>>
> >>>>---------------------------------------------------------------------
> >>>>To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> >>>>For additional commands, e-mail: ojb-dev-help@db.apache.org
> >>>>
> >>>>
> >>>>   
> >>>>
> >>>>        
> >>>>
> >>---------------------------------------------------------------------
> >>To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> >>For additional commands, e-mail: ojb-dev-help@db.apache.org
> >>
> >>
> >>    
> >>
> 
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
> 
> 
-- 
Leandro Rodrigo Saad Cruz
IT - Inter Business Tecnologia e Servicos (IB)
http://www.ibnetwork.com.br
http://jakarta.apache.org/ojb


Mime
View raw message