cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Michael Gentry" <blackn...@gmail.com>
Subject Re: Get the number of FKs
Date Thu, 06 Mar 2008 20:52:20 GMT
Does that SQL return all of the department PKs you want to delete?

On Thu, Mar 6, 2008 at 3:50 PM, Scott Anderson <sanderson@airvana.com> 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: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