Doctrine Query problem

Question

Hey I have some problems creating my query into doctrine:

My sql query looks like this:

select *
from fs_employee
where role_id = ?
and
id not in
(select e.id
from fs_employee e, fs_plane p
where role_id = ?
and p.pilot_id = e.id
and e.player_id = ?
)

So bassicaly I want to select every pilot employee with role_id = 20 that has not been assigned to a plane.

My doctrine query:

Doctrine_Query::Create()
->from('FsEmployee e')
->where('e.role_id = ?', $role)
->andWhere('e.id NOT IN (SELECT e.id FROM FsEmployee e, e.FsPlane p where e.role_id = ? and e.player_id = ?', $role, $id)
->execute();

My error:

Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 261904 bytes) in C:\sxampp\php\PEAR\symfony\plugins\sfDoctrinePlugin\lib\vendor\doctrine\Doctrine\Query\Tokenizer.php on line 329

Thanks for helping me out, David

Answer 1

its look like

the problem is the size of memory

seem that you get a huge set of results from query

try to Increase it in php.ini LIKE :

memory_limit = 200M

Answer 2

You can try adding ->limit(20) ento your entry to get first 20 entries or use sfDoctrinePager in Symfony(if you use it) to process other entries. There is also built-in Doctrine_Pager in Doctrine.

PS. try using Doctrine_Query::getSqlQuery() to get raw SQL query.

Answer 3

Since that my comment and the response by jeremiahd are identifying the problem, I will write it here again:

You forgot a closing ) in your subquery. It should look like:

->andWhere('e.id NOT IN (SELECT e.id FROM FsEmployee e, e.FsPlane p where e.role_id = ? and e.player_id = ?)', $role, $id)