I'm currently working on the issue, I thought I could share my current
thoughts.
Post by Sebastien BigaretSELECT 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
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?
NB: 2==len([Person, Employee])
Is this what we want here, I fear I could miss something here? Any
comments will be appreciated!!
-- Sébastien.
[1] w/ INNER JOINs or the equivalent WHERE clause if inner joins aint
supported.
Post by Sebastien BigaretPost by John LentonOn 29 Jul 2004 10:30:02 +0200, Sebastien Bigaret
Post by Sebastien BigaretAgain, could you be a little more explicit, or exhibit a short
example? Sorry for getting back on this, but the only way I think this
can happen is the one I already posted (see below); if it's not the
case, this is a serious bug I definitely do not want to let escape.
sorry for the delay in answering, and the confusion when doing so. I
feel terribly embarrassed :(
Please don't feel embarrassed, that was worth waiting for it.
Post by John LentonHOWever, here goes.
With the attached model (which is ugly, and breaks some of your naming
conventions, but I just hacked out the attributes and renamed things
from the model being used by a team I'm helping to get up to speed),
Post by Sebastien Bigaretfrom Modeling.EditingContext import EditingContext
import Store
ec=EditingContext()
ec.fetch('Address')[0]
<Address.Address object at 0x4071c74c>
Post by Sebastien Bigareta = _
p = ec.fetch('Person', isDeep=1)[0]
<Employee.Employee object at 0x4072d88c>
<Employee.Employee object at 0x4072d88c>
1
False
<Modeling.FaultHandler.AccessArrayFaultHandler instance at 0x40733a4c>
[<Address.Address object at 0x4071c74c>]
Post by Sebastien Bigaretec.fetch('Address', 'Person.id == 1')
[]
Post by Sebastien Bigaretec.fetch('Address', 'Person.id == 1', isDeep=1)
[]
Wooo, I didn't even realize how serious this was. The 'isDeep' flag
is unfortunately of no help here, because the problem is not with
Address and its underlying hierarchy (controlled by 'isDeep' flag) but
because of the generated SQL statement which obviously doesn't take
SELECT DISTINCT t0.ID, t0.FK_PERSON
FROM ADDRESS t0, PERSON t1
WHERE ( PERSON.ID = 1 ) AND ( ADDRESS.FK_PERSON = PERSON.ID )
I've just submitted bug item #1017127 at
https://sourceforge.net/tracker/index.php?func=detail&aid=1017127&group_id=58935&atid=489335,
with your model and the corresponding code attached.
I'll search a definitive fix soon --my current thinking being about
the way that would allow even more complicated qualifiers to be
correctly handled as well, such as with: person.addresses.id in [2,3]
or w/ a qualifier traversing two or more relationships involving
entities with sub-entities.
Thanks a lot for the precise report,
-- Sébastien.