Re: [Modeling-users] toOne relationship returns base class instead of subcl

[John L. <jl...@gm...>]

On 30 Aug 2004 23:09:40 +0200, Sebastien Bigaret
<sbi...@us...> wrote:
> 
> I'm currently working on the issue, I thought I could share my current
> thoughts.
> 
> So the problem w/ bug #1017127 is that the generated SQL is:
> >     SELECT DISTINCT t0.ID, t0.FK_PERSON
> >     FROM ADDRESS t0, PERSON t1
> >     WHERE ( PERSON.ID = 1 ) AND ( ADDRESS.FK_PERSON = PERSON.ID )
> 
> while it should take into account the class hierarchy, so that both
> PERSON and EMPLOYEE are traversed.
> 
> I was initially thinking of making as many SQL queries as there are
> pssible paths through the relationships (here, two: ADDRESS to PERSON,
> and ADDRESS to EMPLOYEE) --but then I remembered these could SQL queries
> could be UNIONed
> 
> So:
>     ec.fetch('Address', 'Person.id == 1')
> 
> should trigger the following query on the database [1]:
> 
>     SELECT DISTINCT t0.ID, t0.FK_PERSON
>       FROM ADDRESS t0
>           INNER JOIN EMPLOYEE t1 ON t0.FK_PERSON=t1.ID
>       WHERE t1.ID = 1
>     UNION
>     SELECT DISTINCT t0.ID, t0.FK_PERSON
>       FROM ADDRESS t0
>            INNER JOIN PERSON t1 ON t0.FK_PERSON=t1.ID
>       WHERE t1.ID = 1
> 
> NB: Incidentally, that UNION thing could also be a solution for the
>     current limitation of sort orderings that cannot be used w/ the
>     isDeep flag being set! Another story, though

yes, I was going to mention that in the other thread, but then I saw
that it involved modifying code all the way down to the database
adapter, and thought you'd be against it. Glad to know you aren't; you
do realize this will mean you need a second query to get at attributes
not present in the base class? Or would you select with the union of
all attributes, providing dummy values for those not present in each
table?

> Now the question is: suppose I have two plus two paths in the same fetch
> spec., such as in:
> 
>   AND:
>     ec.fetch('Address', 'person.id in [1,2,3] AND person.x.y like...')
> 
>   OR:
>     ec.fetch('Address', 'person.id in [1,2,3] OR person.x.y like...')
> 
> --> In the case of the 'AND' operator, do the two 'Person' refer to the
>     same table? In other words, do we get only two SELECTs union'ed?
> 
>     In the case of the 'OR' operator, do the two 'Person' refer
>     independant tables, either the same or different ones? In other
>     words, do we get 2*2=4 SELECTs union'ed?

if we were able to do sub-selects, we would (or could) write either as

    SELECT * FROM (SELECT * FROM Person UNION SELECT ... FROM Employee
) AS Person WHERE <qualifier>

(because that is what we mean, right?) and this reduces to

    SELECT * FROM Person WHERE <qualifier> UNION \
        SELECT ... FROM Employee WHERE <qualifier>
 
irrespective of <qualifier>.

> Is this what we want here, I fear I could miss something here?  Any
> comments will be appreciated!!

HTH---maybe it's me missing something, however; the "that is what we
mean" assumption is a strong one (in that it has big consquences).

-- 
John Lenton (jl...@gm...) -- Random fortune:
bash: fortune: command not found