Laszlo,
Since Craig, who did most of the original flattened relationships
implementation, is moving to other things, I guess I might dig into
this more myself. This does look like a query translator bug. This will
take some time though, since I am working on many other things. Could
you file this as a bug, so that we do not overlook it?
I know the answer to the "DISTINCT" question though: any time the
qualifier is built using "to-many" relationships, DISTINCT is enforced,
since joining from primary key, will result in duplication of the root
entity rows. There is no reason to bring them all to the client. Do you
have any confirmation that DISTINCT leads to serious performance
degradation?
Thanks
On Saturday, May 3, 2003, at 07:15 AM, Laszlo Spoor wrote:
> Cayenne 1.0.beta2
> MySQL 3.23 / 4.x
>
> Hi Cayenne Team,
>
> I have a problem when I write a SelectQuery where multiple flattened
> relationships occur.
>
> Some of my Object Model is:
> Photo --> Metainformation about a picture
> PhotoType --> E.g. scenery, overview, blueprint, etc.
> Complex --> Collection of houses
> HousingType --> E.g. appartment, villa, row house, etc
> [...etc...]
>
> Some of my Relationships:
> HousingType |(1:n)| Complexes
> Photo |(n:n)| PhotoTypes
> Photo |(n:n)| Complexes
> [...etc...]
>
> My (partial) data model:
> fotos maps to Photo
> photo_types maps to PhotoTypes
> complexes maps to Complex
> cpx_pto is an intersection table between complexes and Photos
> pte_pto is an intersection table between phototypes and Photos
>
> The flattened Relationships are mapped using the modeler, like (my ;-)
> example:
> http://www.objectstyle.org/cayenne/userguide/advmodel/
> flattenedrel.html. The Java code is:
>
> [...]
> SelectQuery qry = new SelectQuery (Photo.class);
> qry.setPageSize(15);
> qry.setDistinct(false);
>
> Expression qualifier
> = ExpressionFactory.matchExp("complexes.toHousingType.name",
> "villa");
>
> qry.setQualifier(qualifier);
>
> Expression pIt
> = ExpressionFactory.matchExp("fototypes.name", "blueprint");
>
> qry.andQualifier(pIt);
> qry.setLoggingLevel(Level.WARN);
>
> mFotoList = super.getDataContext().performQuery(qry);
> [...]
>
>
> This SQL-code that Cayenne generated is:
>
> SELECT DISTINCT t0.file_name
> , t0.url_foto
> , t0.url_thumbnail
> , t0.id
> FROM photos t0
> , cpx_pto t1
> , complexes t2
> , housing_types t3
> , fte_fto t4
> , phototypes t5
> WHERE t0.id = t1.fto_id
> AND t1.cpx_id = t2.id
> AND t2.hwe_id = t3.id
> AND t0.id = t4.fto_id
> AND t4.fte_id = t5.id
> AND ((t3.name = ?)
> AND (t4.name = ?))
> [params: 'villa', 'blueprint']
>
> Now, there are two points that I don't understand:
> - t4 should be t5
> As you can see, t4.name points to the intersection table, so the
> execution of
> the query fails. However, I expect it to point to phototypes.name
> (thus
> t5.name). If I replace it with t5, the query returns what I expect.
>
> - distinct
> qry.setDistinct(false) or qry.setDistinct(true), does not result in
> ommitting
> the distinct part of the query. Distincts are usually a bad idea,
> cause it
> results in a full table scan. It this expected behaviour?
>
> Can anyone please explain to me what is happening, why and what I
> should do to overcome this?
>
> Thanks in advance, Laszlo Spoor
This archive was generated by hypermail 2.0.0 : Sun May 04 2003 - 18:37:11 EDT