Delete with left join using Doctrine

I have the following DQL
 how do I make it in one query (  without the foreach )
 
 $q = Doctrine_Query::create()
                ->from('UserDepartment ud')
                ->leftJoin('ud.Department d')
                ->leftJoin('d.Company c')
                ->where('c.id = ?', $company_id)
                ->andWhere('ud.user_id = ?', $user_id)
                ;    
       $result = $q->execute();
       foreach ($result as $record) {
            $record->delete();
        }  
jadkaramAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

OnALearningCurveCommented:
Hi jadkaram,

I have never used DQL before but I have just taken a look at this page:

http://www.doctrine-project.org/documentation/manual/1_2/en/dql-doctrine-query-language

and using the examples given I think you should be able to use the code below.

The "echo $result" line should output the number of records affected (i.e deleted)

and I believe you can replace the line

$result = $q->execute();

with

echo $q->getSqlQuery();

to test your code, when you run it with this line in place of the execute command the output should be the SQL command that will be executed.

Hope this helps,

Mark.
$q = Doctrine_Query::create()
                ->delete('UserDepartment ud')
                ->leftJoin('ud.Department d')
                ->leftJoin('d.Company c')
                ->where('c.id = ?', $company_id)
                ->andWhere('ud.user_id = ?', $user_id)
                ;    
       $result = $q->execute();
		echo $rows;

Open in new window

OnALearningCurveCommented:
Sorry,

the last line of the attached code above should have been

echo $result  

and not

echo $rows

edited version below,

Cheers,

Mark
$q = Doctrine_Query::create()
                ->delete('UserDepartment ud')
                ->leftJoin('ud.Department d')
                ->leftJoin('d.Company c')
                ->where('c.id = ?', $company_id)
                ->andWhere('ud.user_id = ?', $user_id)
                ;    
       $result = $q->execute();
		echo $result;

Open in new window

jadkaramAuthor Commented:
sorry this does not work out.
In DQL there is some issue when using delete with left join..
the code you implemented gives me the error message:
Unknown component alias d
which means that after the delete it is not interpreting the left join"->leftJoin('ud.Department d')"   correctly
jadkaramAuthor Commented:
I tried this solution and strangely it worked..
thanks for your help anyway..
I'm posting the solution for reference

$q = Doctrine_Query::create()
                ->from('UserDepartment ud')
                ->leftJoin('ud.Department d')
                ->leftJoin('d.Company c')
                ->where('c.id = ?', $company_id)
                ->andWhere('ud.user_id = ?', $user_id)
                ;    
       $result = $q->execute();
       $result->delete();

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OnALearningCurveCommented:
Glad you got it sorted.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.