[question] Multiple flattened relationships in a single SelectQuery

From: Laszlo Spoor (lspoor_cayenn..otmail.com)
Date: Sat May 03 2003 - 07:15:16 EDT

  • Next message: martin ruff: "pooling, datacontext problem?"

    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

    _________________________________________________________________
    Add photos to your messages with MSN 8. Get 2 months FREE*.
    http://join.msn.com/?page=features/featuredemail

    This archive was generated by hypermail 2.0.0 : Sat May 03 2003 - 07:15:14 EDT