relationship query and entity qualifier

[Marcin Skladaniec <ma...@ish.com.au>]

Hi

I have problems with entity qualifier and relationships. I defined  
"isDeleted" column in few tables, and when the record is deleted is  
not really removed from the database. In cayenne modeller I specified  
a entity qualifier to not include those deleted objects in any query,  
but I found that it does not always work:

For one to many relation:
- entity qualifier works well when going "to many"
- entity qualifier does not work  when going "to one"
For one to one relations it does not work at all.

I know that I could simply delete the relationship, but the goal is to  
avoid orphaned objects, and keep the history.

Is it a bug in cayenne, or a desired behaviour ?

With regards
Marcin

Re: relationship query and entity qualifier

[Marcin Skladaniec <ma...@ish.com.au>]

Hi

I have worked around this problem by inverting the relationship (it  
was one-to-one) and putting the fk on the other side. The problem I  
have now is that there is no simple way of querying for objects with  
this relationship set.
The schema I'm using has a Contact related to Student and/or Tutor.
Before I could query on "contact.studentid is not null" to find all  
contacts with student role, now it is not possible and I had to add a  
calculated boolean column "isStudent" in Contact table on which I can  
query on. Is there a better way ?

After inverting the relationship yet another problem I found on my way  
was that query with expression like this:
exp1 = ExpressionFactory.noMatchExp(Contact.STUDENT_PROPERTY 
+"."+Student.CREATED_ON_PROPERTY, null);
exp2 = ExpressionFactory.noMatchExp(Contact.TUTOR_PROPERTY 
+"."+Tutor.CREATED_ON_PROPERTY, null);
then
exp1.orExp(exp2)

resulted in sql :
SELECT t0.id FROM Contact t0, Student t1, Tutor t2 WHERE t0.id =  
t1.contactId AND t0.id = t2.contactId AND ((t1.createdOn IS NOT NULL)  
OR (t2.createdOn IS NOT NULL))

as you can see the "join" bit has and AND:
t0.id = t1.contactId AND t0.id = t2.contactId
which makes this query return only contacts with both student and  
tutor role, regardless of the 'orExp'.


Marcin

On 27/11/2007, at 7:14 AM, Andrus Adamchik wrote:

> Hi Marcin,
>
> Probably a bug in faulting... The analysis below is from memory. I  
> haven't checked the code to confirm it :-). Cayenne always assumes  
> that if a given object contains an FK to a target object (non- 
> flattened to-one), that a target object exists. This can potentially  
> save a trip to the DB (e.g. if all you want to do is to check  
> whether target is null or not). This is based on the assumption of  
> referential integrity, which by itself may be overly optimistic, and  
> you just poked another hole in it - if a target entity has a  
> qualifier, non-null FK in the source does not mean much.
>
> So I'd say open a Jira improvement request and we'll add an extra  
> condition check for super-lazy faulting of to-ones.
>
> Andrus
>
>
> On Nov 26, 2007, at 2:45 AM, Marcin Skladaniec wrote:
>
>> Hi
>>
>> I have problems with entity qualifier and relationships. I defined  
>> "isDeleted" column in few tables, and when the record is deleted is  
>> not really removed from the database. In cayenne modeller I  
>> specified a entity qualifier to not include those deleted objects  
>> in any query, but I found that it does not always work:
>>
>> For one to many relation:
>> - entity qualifier works well when going "to many"
>> - entity qualifier does not work  when going "to one"
>> For one to one relations it does not work at all.
>>
>> I know that I could simply delete the relationship, but the goal is  
>> to avoid orphaned objects, and keep the history.
>>
>> Is it a bug in cayenne, or a desired behaviour ?
>>
>> With regards
>> Marcin
>

Re: relationship query and entity qualifier

[Andrus Adamchik <an...@objectstyle.org>]

Hi Marcin,

Probably a bug in faulting... The analysis below is from memory. I  
haven't checked the code to confirm it :-). Cayenne always assumes  
that if a given object contains an FK to a target object (non- 
flattened to-one), that a target object exists. This can potentially  
save a trip to the DB (e.g. if all you want to do is to check whether  
target is null or not). This is based on the assumption of referential  
integrity, which by itself may be overly optimistic, and you just  
poked another hole in it - if a target entity has a qualifier, non- 
null FK in the source does not mean much.

So I'd say open a Jira improvement request and we'll add an extra  
condition check for super-lazy faulting of to-ones.

Andrus


On Nov 26, 2007, at 2:45 AM, Marcin Skladaniec wrote:

> Hi
>
> I have problems with entity qualifier and relationships. I defined  
> "isDeleted" column in few tables, and when the record is deleted is  
> not really removed from the database. In cayenne modeller I  
> specified a entity qualifier to not include those deleted objects in  
> any query, but I found that it does not always work:
>
> For one to many relation:
> - entity qualifier works well when going "to many"
> - entity qualifier does not work  when going "to one"
> For one to one relations it does not work at all.
>
> I know that I could simply delete the relationship, but the goal is  
> to avoid orphaned objects, and keep the history.
>
> Is it a bug in cayenne, or a desired behaviour ?
>
> With regards
> Marcin