cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Scott Anderson" <sander...@airvana.com>
Subject RE: Get the number of FKs
Date Thu, 06 Mar 2008 20:50:01 GMT
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:blacknext@gmail.com] 
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 <mkienenb@gmail.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 <blacknext@gmail.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
<sanderson@airvana.com> wrote:
>  >  > Employee.Department is mandatory; I want to search for and 
> delete rogue  >  >  departments.
>  >  >
>  >  >
>  >  >
>  >  >  -----Original Message-----
>  >  >  From: Michael Gentry [mailto:blacknext@gmail.com]  >  >  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 
> <sanderson@airvana.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?
>  >  >  >
>  >  >
>  >
>

Mime
View raw message