For leftjoin you must remember the relationship between classes in doctrine in schema file.

I have used schema.yml file for my database and its relationship with tables, for example I am give only two tables definition
sfGuardGroup:
actAs: [Timestampable]
columns:
name:
type: string(255)
unique: true
description: string(1000)
relations:
Users:
class: sfGuardUser
refClass: sfGuardUserGroup
local: group_id
foreign: user_id
foreignAlias: Groups
Permissions:
class: sfGuardPermission
local: group_id
foreign: permission_id
refClass: sfGuardGroupPermission
foreignAlias: Groups

sfGuardUserGroup:
options:
symfony:
form: false
filter: false
actAs: [Timestampable]
columns:
user_id:
type: integer
primary: true
group_id:
type: integer
primary: true
relations:
User:
class: sfGuardUser
local: user_id
onDelete: CASCADE
Group:
class: sfGuardGroup
local: group_id
onDelete: CASCADE

Table look lie this ( I have added some data)

sf_guard_group

id   name   description
1     admin       –
2    vendor     –
3     user         –

sf_guard_user_group

user_id    group_id
1                      1
2                     3
3                     3
4                     3
5                     2
6                     2
7                     3

Now you want fetch group name by sf_guard_group table. If you look at relationship you will find that there is a relationship name Users

I have written a function in sfGuardGroupTable.class.php [ you will get it while you build the tables ]
public function getGroupName(){
$q = Doctrine_Query::create()
->select('sgg.name')
->from('sfGuardGroup sgg')
->leftJoin('sgg.Users gs');

$q->setHydrationMode(Doctrine_Core::HYDRATE_SCALAR);

 

echo $q->getSQLQuery();
return $q->execute();
}

you will get the answers like this if you call in respective scope

$groupid = Doctrine_Core::getTable(‘sfGuardGroup’)->getGroupName();

echo “<pre>”;

$groupid = Doctrine_Core::getTable(‘sfGuardGroup’)->getGroupName();

print_r($groupid);

 

Array
(
[0] => Array
(
[sgg_name] => admin
)

[1] => Array
(
[sgg_name] => client
)

[2] => Array
(
[sgg_name] => client
)

[3] => Array
(
[sgg_name] => client
)

[4] => Array
(
[sgg_name] => client
)

[5] => Array
(
[sgg_name] => vendor
)

[6] => Array
(
[sgg_name] => vendor
)

)

Some useful url:

http://www.doctrine-project.org/documentation/manual/1_2/en/dql-doctrine-query-language#join-syntax:on-keyword