Re: relationship query and entity qualifier

[Marcin Skladaniec]

Hi


The schema I'm using has a Contact related to Student and/or Tutor.



then
exp1.orExp(exp2)

resulted in sql :


as you can see the "join" bit has and AND:
t0.id = t1.contactId AND t0.id = t2.contactId



Marcin

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

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. 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); 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)) which makes this query return only contacts with both student and tutor role, regardless of the 'orExp'.

Hi Marcin,





Andrus


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

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.

Hi



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.



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

With regards
Marcin

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: I know that I could simply delete the relationship, but the goal is to avoid orphaned objects, and keep the history.

smime.p7s
Description: S/MIME cryptographic signature