Symfony / Propel: How to Left Join

Contributed by David Kirk     
Tagged: Symfony  

A JOIN is a way to connect related database tables by the common values between them. A left join contains all of the rows from the primary table plus related members of the second database if they exist. Using joins can significantly reduce the number of database queries required to render a web page. This Tech-Recipe describes how to do a left join in Symfony using Propel.

It is common to have tables linked by foreign keys. Obtaining information from the linked table can be accomplished by a left join.

In the below example, I have a primary Patient table that is linked to a Doc table by foreign keys. Not only do I want to list the associated doctor with a patient, I also want to list the associated doctor’s pager number.

Here is an cropped example schema.yml of my database structure:
propel:
patient:
id:
name: varchar(255)
doc_id:

doc:
id:
name: varchar(255)
pager: varchar(255)


First I perform my left join in my actions class:
$c = new Criteria();
$c->addJoin(PatientPeer::DOC_ID, DocPeer::ID, Criteria::LEFT_JOIN);
$this->patients = PatientPeer::doSelect($c);


Finally, I output the data in my template:
foreach ($patients as $patient):
echo $patient->getDoc()->getPager();
endforeach;


While this template code would work without the join, this technique with the join is more efficient. Without the join, the getDoc() method of the Patient model would have to make an extra database query for each iteration of the loop. With the join, the data required to hydrate the Doc objects and the Patient data are all gathered with a single database query.