Entities without relation

[Ramiro Aparicio <ra...@prot-on.com>]

Hi,

An easy question, I want to know all elements of table A that does NOT 
have a relationship with table B
A->B 1:N so A id is stored on B

In plain SQL I am using:
"select * from A where idA not in (select idA from B);"
It is not really nice, but works

But using a Expressions seems a lot harder, I don't find anything like 
that, sure we have notInExp but I don't see a way to set the path to the 
entity it self, and having to extract all idA to use it with notInDbExp 
seems very ugly (if idA is exposed on A).

I know I can go and just use plain SQL, just for knowledge I would want 
to now if something as "simple" is impossible to be queried using 
Expressions

Ramiro Aparicio

Re: Entities without relation

[Michael Gentry <mg...@masslight.net>]

Hi Ramiro,

I think you can build your Expression such that the relationship from A to
B is null:

ExpressionFactory.matchExp(A.TO_B_PROPERTY, null)

(change the name of the entity/property as appropriate)

mrg



On Wed, Jan 30, 2013 at 12:01 PM, Ramiro Aparicio <
ramiro.aparicio@prot-on.com> wrote:

> Hi,
>
> An easy question, I want to know all elements of table A that does NOT
> have a relationship with table B
> A->B 1:N so A id is stored on B
>
> In plain SQL I am using:
> "select * from A where idA not in (select idA from B);"
> It is not really nice, but works
>
> But using a Expressions seems a lot harder, I don't find anything like
> that, sure we have notInExp but I don't see a way to set the path to the
> entity it self, and having to extract all idA to use it with notInDbExp
> seems very ugly (if idA is exposed on A).
>
> I know I can go and just use plain SQL, just for knowledge I would want to
> now if something as "simple" is impossible to be queried using Expressions
>
> Ramiro Aparicio
>

Re: Entities without relation

[Mike Kienenberger <mk...@gmail.com>]

This is from an old Cayenne 1.2 project, so the syntax may be a bit
different now.

Basically, you use an outer join and then noMatchExp() against null.
The joined record will have null for all values if the relationship is
empty.

The plus "+" indicates an outer join at this point.

      ExpressionFactory.noMatchExp(AuthorizationDocument.DEPENDENT_PERMIT_DOCUMENT_PROPERTY
            		+ "+"
            		+ "." + PermitDocument.AUTHORIZATION_DOCUMENT_PROPERTY,
            	null);





On Wed, Jan 30, 2013 at 12:01 PM, Ramiro Aparicio
<ra...@prot-on.com> wrote:
> Hi,
>
> An easy question, I want to know all elements of table A that does NOT have
> a relationship with table B
> A->B 1:N so A id is stored on B
>
> In plain SQL I am using:
> "select * from A where idA not in (select idA from B);"
> It is not really nice, but works
>
> But using a Expressions seems a lot harder, I don't find anything like that,
> sure we have notInExp but I don't see a way to set the path to the entity it
> self, and having to extract all idA to use it with notInDbExp seems very
> ugly (if idA is exposed on A).
>
> I know I can go and just use plain SQL, just for knowledge I would want to
> now if something as "simple" is impossible to be queried using Expressions
>
> Ramiro Aparicio