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:blacknex..mail.com]
> Sent: Thursday, March 06, 2008 3:34 PM
> To: use..ayenne.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 <mkienen..mail.com>
> 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 <blacknex..mail.com> 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
> <sanderso..irvana.com> wrote:
>>>> Employee.Department is mandatory; I want to search for and
>> delete rogue > > departments.
>>>>
>>>>
>>>>
>>>> -----Original Message-----
>>>> From: Michael Gentry [mailto:blacknex..mail.com] > > Sent:
>> Thursday, March 06, 2008 2:08 PM > > To:
>> use..ayenne.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
>> <sanderso..irvana.com> > > 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?
>>>>>
>>>>
>>>
>>
This archive was generated by hypermail 2.0.0 : Thu Mar 06 2008 - 15:55:30 EST