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 18:39:32 GMT
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
> 
> 
-- 
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