Just about like that? :)
String sql =
"SELECT DISTINCT #result('department.idx' 'Integer' 'idx') FROM
department LEFT JOIN user ON department.idx = user.department_idx
WHERE user.idx IS NULL";
SQLTemplate template = new SQLTemplate(Department.class,sql);
List departments =
DataContext.getThreadDataContext().performQuery(template);
should do the trick.
Robert
On Mar 6, 2008, at 3/62:50 PM , Scott Anderson 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?
