Re: [litesql] Search for an attribute from the base type

[Kitten <kit...@gm...>]

> Hi all,
>
> <object name="School" >
>    <field name="name" type="string" />
> </object>
> <object name="Person" >
>    <field name="name" type="string" />
> </object>
> <object name="Student" inherits="Person" >
>    <field name="classroom" type="string" />
> </object>
> <object name="Teacher" inherits="Person" >
>    <field name="salary" type="integer" />
> </object>
> <relation>
>    <relate object="School" handle="people" limit="one" />
>    <relate object="Person" handle="school" />
> </relation>
>
> I have a particular school, and I want to select all students from
> that school in a particular classroom. Is there any way I can do this
> without constructing the SQL manually: (select * from Student_ where
> id_ in (select Person1_ from School_Person_ where School2_ =
> SCHOOL_ID_HERE))
>
> School school(db);
>
> school.people().get( ?? ); // anything I can put in here?

So now I have it down to:

student = select<Student>(db, Student::Classroom == "SomeClassroom" &&
Student::Id.in(school.people().get().idQuery())).one();

but this is three or four times slower than:

student = select<Student>(db, Student::Classroom == "SomeClassroom");

by itself. The resulting query of the first one is:

SELECT Person_.id_,Person_.type_,Student_.classroom_ FROM
Person_,Student_ WHERE ((Student_.classroom_ = 'CLASSROOM_NAME_HERE')
and (Person_.id_ in (SELECT Person_.id_ FROM Person_ WHERE Person_.id_
in (SELECT Person_School_.Person1_ FROM Person_School_ WHERE
Person_School_.School2_ = 'SCHOOL_ID_HERE')))) and (Person_.id_ =
Student_.id_)

notice the Person_.id_ in (SELECT Person_.id_ FROM Person_ WHERE
Person_.id_ in (...