Link to home
Start Free TrialLog in
Avatar of jadkaram
jadkaram

asked on

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();
        }  
Avatar of OnALearningCurve
OnALearningCurve

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

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

Avatar of jadkaram

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of jadkaram
jadkaram

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Glad you got it sorted.