Does that SQL return all of the department PKs you want to delete?
On Thu, Mar 6, 2008 at 3:50 PM, Scott Anderson <[EMAIL PROTECTED]> wrote: > How would I build a SQLTemplate to do a query like this one? > > SELECT DISTINCT department.idx > FROM department > LEFT JOIN user ON department.idx = user.department_idx > WHERE user.idx IS NULL > > > -----Original Message----- > From: Michael Gentry [mailto:[EMAIL PROTECTED] > > > Sent: Thursday, March 06, 2008 3:34 PM > To: user@cayenne.apache.org > Subject: Re: Get the number of FKs > > You are right. I completely misread that. I'd still probably do raw > SQL or an SQLTemplate, though. > > Thanks, > > /dev/mrg > > On Thu, Mar 6, 2008 at 3:31 PM, Mike Kienenberger <[EMAIL PROTECTED]> > wrote: > > Michael, just to clarify, he doesn't want to work with Employees. He > > > wants to work with Departments. Scott wants to know how to do this > > without pulling in the Employee records. > > > > > > > > On 3/6/08, Michael Gentry <[EMAIL PROTECTED]> wrote: > > > If it is mandatory, it sounds like these records were created > > outside > of Cayenne (they are legacy, testing, etc records)? If > > that is the > case, the easiest thing to do is to just issue an SQL > > command directly > to the database (using whatever normal SQL > command you use). > > > Something like: > > > > > > delete from employee where departmentFK is null; > > If it is > > something that is happening regularly, though, and you want > to be > > able to nuke them from within your Cayenne application, you can > > > issue raw SQL to do the trick, too: > > > > > > DataContext dataContext = DataContext.createDataContext(); > > > SQLTemplate sqlQuery = new SQLTemplate(Customer.class, "delete from > > > > employee where departmentFK is null"); > > > dataContext.performNonSelectingQuery(sqlQuery); > > > > > > With this code, though, be careful that you don't have any of > > those > dangling employees in memory, as this totally bypasses the > > Cayenne > object graph. > > > > > > /dev/mrg > > > > > > > > > > > > On Thu, Mar 6, 2008 at 3:19 PM, Scott Anderson > <[EMAIL PROTECTED]> wrote: > > > > Employee.Department is mandatory; I want to search for and > > delete rogue > > departments. > > > > > > > > > > > > > > > > -----Original Message----- > > > > From: Michael Gentry [mailto:[EMAIL PROTECTED] > > Sent: > > Thursday, March 06, 2008 2:08 PM > > To: user@cayenne.apache.org > > > > > Subject: Re: Get the number of FKs > > > > It seems you mainly > > > want a list of employees who are not in a > > department, so doing > > a fetch on Employee where "department = null" > > > > might work (I'm not positive I've ever tried this, but it seems > > > logical > > to me). > > > > > > > > /dev/mrg > > > > > > > > On Thu, Mar 6, 2008 at 1:58 PM, 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? > > > > > > > > > > > > > > >
