Unfortunately, that doesn't produce SQL with the proper JOIN type: SELECT DISTINCT t0.idx, t0.manager_idx, t0.name FROM department t0, user t1 WHERE t0.idx = t1.department_idx AND (t1.idx IS NULL)
In a one-to-many (one department, many users) relationship, this query doesn't make any sense, so perhaps this could be added as a feature request to Cayenne. I also tried matching against an empty list; that simply produced a ClassCastException, since Cayenne was expecting an Integer. -----Original Message----- From: Mike Kienenberger [mailto:[EMAIL PROTECTED] Sent: Thursday, March 06, 2008 3:29 PM To: [email protected] Subject: Re: Get the number of FKs I haven't tried it, but I would think that if anything were going to work out of the box, it would be expression = ExpressionFactory.matchExp(Department.EMPLOYEE_LIST_RELATIONSHIP_PROPERT Y, null); Department.EMPLOYEE_LIST_RELATIONSHIP_PROPERTY may be named differently in your generated classes -- it'd be equal to "employees", most likely. Unfortunately, I don't know if you can do a null comparision against a to-many relationship. If not, I suspect your next best bet is to create an SQLTemplate. I wouldn't bother with the intermediate step of fetching how many employees are in the list -- I'd simply pull back all departments that have no employees since you're planning on deleting them anyway, or at least department primary keys. On 3/6/08, Scott Anderson <[EMAIL PROTECTED]> wrote: > I've got a one-to-many relationship (employees belong to a > department), and I'd like to search for departments that have no employees. > Currently, I have something like: > > foreach(dept : departments) { > if(dept.getEmployees().size() == 0) > context.deleteObject(dept); > } > > When I do this, it has the effect of telling Cayenne to fetch data > rows for every employee in the database; this will not scale well. Is > there any way I can get the size of the array without doing a custom > SELECT query? >
