Yes; I think I figured out how to do the SQLTemplate.
SQLTemplate sqt = new SQLTemplate(Department.class, "SELECT DISTINCT " +
"#result('department.idx' 'int' 'idx') " +
"FROM department " +
"LEFT JOIN user ON department.idx = user.department_idx
" +
"WHERE user.idx IS NULL");
Which is great, since it even returns a list of HOLLOW departments. I do
actually end up looking at the department name, but that's purely for
logging.
-----Original Message-----
From: Michael Gentry [mailto:[EMAIL PROTECTED]
Sent: Thursday, March 06, 2008 3:52 PM
To: user@cayenne.apache.org
Subject: Re: Get the number of FKs
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?
> > > > >
> > > >
> > >
> >
>
