how to select instances with empty to-many relation

From: Dirk Lattermann (dlat..lqualonde.de)
Date: Sat Jan 14 2006 - 06:57:22 EST

  • Next message: Andrus Adamchik: "Re: how to select instances with empty to-many relation"

    Hello,

    I'm using Cayenne1.2M10. I've got a data object class X with a to-one relation toX to the same data object class X (I don't think that the relation being reflexiv matters here). The corresponding inverse to-many relationship xArray is also defined.

    Now I would like to select the objects that aren't referenced by any other objects, i.e. their xArray is empty.

    In SQL, I would write a query like

    select * from x x1 where not exists (select 'X' from x x2 where x2.ref_to_x = x1.pk_x)

    One could also imagine an outer join of x x1 and x x2 on the above condition and selecting the rows with x2.pk_x is null.

    How can I achieve this using a SelectQuery? I tried it with a qualifier of

    qual = ExpressionFactory.matchExp("xArray", null);
    or
    qual = ExpressionFactory.matchExp("xArray.anyNotNullProperty", null);

    but that failed. The first one results in the query
    SELECT DISTINCT t0.col1, t0.col2, t0.pk_x, t0.ref_to_x FROM x t0, x t1 WHERE t0.pk_x = t1.ref_to_x AND (t0.pk_x IS NULL)
    obviously, this returns no rows.

    Is there probably the possibility to define a "calculated" property of class X that returns the size of the xArray List? I tried this, but I got

    org.objectstyle.cayenne.exp.ExpressionException: [v.1.2M10 January 5 2006] Can't resolve path component: [X.arraySize].

    I don't know if it's possible to add an obj-attribute to an obj-entity in the data map xml file that doesn't reference a db-attribute-path.

    Thank you,
    Dirk Lattermann

    This archive was generated by hypermail 2.0.0 : Sat Jan 14 2006 - 06:57:28 EST