__isa__ SQL bug

[rdb]

Using __isa__ is have successfully set up a parent-child relationship between a parent "people" table and a number of child tables, such as "faculty", "staff" and "student". XF successfully joins these tables so that I can add/edit/delete data from parent and child tables simultaneously.

Then I got the idea of sorting the data in list view based on the person's name. So I put this at the top of the fields.ini file for the parent "people" table, which contains the name data:

Code: Select all

__sql__="SELECT p.* FROM people p ORDER BY p.surname asc, p.given_names asc"


When I click on the "people" tab in XF, I indeed get a list of all the people, ordered by name. However, when I click on the "faculty" tab, I the following expression on an otherwise blank screen:

Code: Select all

SELECT COUNT(*) FROM (select * from `faculty` as `t___child` left join (SELECT p.* FROM people p ORDER BY p.surname asc, p.given_names) as as `t___parent` on (`t___child`.`people_id`=`t___parent`.`id`)) as `faculty`


Notice that there is an error in the SQL code. Immediately following the inner SELECT expression (in parentheses) there is an "as as". I cut/pasted the SQL code into MySQL and of course it failed. I then deleted one of the "as"-es and the command executed successfullly.

I started snooping around for the source of the surplus "as", but have thus far been unsuccessful. This is largely due to my lack of familiarity with the XF code. Any advice on where I should start looking?

Thank-you.

[shannah]

I have fixed this issue in SVN
http://weblite.ca/svn/dataface/core/tru ... uilder.php

But in my tests it seems to destroy performance if you use __sql__ in both the parent and child tables at the same time. This is because Xataface uses a nested query to replace table names with the query defined in __sql__ and when inheritance is used this can result in 3 levels of nested queries which kill performance.

E.g. I had approx 6000 records in the parent table and only about 20 records in the child table - both with __sql__ defined an it caused list view load time to go from 0.3 seconds to 28 seconds.

I'll try to think of ways to overcome this in the future but can't think of anything *easy* right now.