cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Marcin Skladaniec <mar...@ish.com.au>
Subject Re: relationship query and entity qualifier
Date Mon, 26 Nov 2007 21:44:15 GMT

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
>


Mime
View raw message