Self joins

From: Patric Lichtsteiner (lists0..ichtsteiner.org)
Date: Thu Jan 18 2007 - 03:14:15 EST

  • Next message: Andrus Adamchik: "[ANN] Cayenne 2.0.2. and 1.2.2 released"

    Hi

    I'm using Cayenne 2.0.1 and I'm working with a model with a self join
    like this:

    <db-entity name="PathElement">
            <db-attribute name="ID" type="INTEGER" isPrimaryKey="true"
    isMandatory="true"/>
            <db-attribute name="Name" type="VARCHAR" isMandatory="true"
    length="100"/>
            <db-attribute name="ParentPathElementId" type="INTEGER"/>
    </db-entity>

    <db-relationship name="parentPathElement" source="PathElement"
    target="PathElement" toMany="false">
            <db-attribute-pair source="ParentPathElementId" target="ID"/>
    </db-relationship>
    <db-relationship name="subPathElements" source="PathElement"
    target="PathElement" toMany="true">
            <db-attribute-pair source="ID" target="ParentPathElementId"/>
    </db-relationship>

    <obj-entity name="PathElement"
    className="com.encodo.test.cayenne.PathElement"
    dbEntityName="PathElement">
            <obj-attribute name="name" type="java.lang.String"
    db-attribute-path="Name"/>
    </obj-entity>

    <obj-relationship name="parentPathElement" source="PathElement"
    target="PathElement" db-relationship-path="parentPathElement"/>
    <obj-relationship name="subPathElements" source="PathElement"
    target="PathElement" db-relationship-path="subPathElements"/>

    The database scheme and the classes are correctly generated with this
    definition. Inserting data with java code also works without problems,
    but executing a query does not work as I would expect it (the database
    contains a root PathElement and 2 sub PathElements):
        SelectQuery pathElementQuery = new SelectQuery(PathElement.class,
    Expression.fromString("parentPathElement = null"));
        pathElementQuery.addPrefetch("subPathElements");
        List<PathElement> rootPathElements =
    _context.performQuery(pathElementQuery);
        //Root element is found
        List<PathElement> subPathElements =
    rootPathElements.get(0).getSubPathElements();
        //subPathElements.size() is 0, that's wrong!

    The following queries are executed by Cayenne:
    SELECT t0.Name, t0.ParentPathElementId, t0.ID FROM PathElement t0 WHERE
    t0.ParentPathElementId IS NULL
    SELECT t0.Name, t0.ParentPathElementId, t0.ID FROM PathElement t0,
    PathElement t1 WHERE t0.ParentPathElementId = t1.ID AND
    (t0.ParentPathElementId IS NULL)

    IMHO, this query is wrong. The condition should be:
    t1.ParentPathElementId IS NULL

    What is going wrong here? Has anybody an idea?

    Thanks a lot
    Patric

    This archive was generated by hypermail 2.0.0 : Thu Jan 18 2007 - 03:15:05 EST