Zend Framework: How to combine three tables in one query using Joins?

Question

I have three tables like this:

Person table:

person_id |    name     |   dob
--------------------------------
    1     |   Naveed    |  1988
    2     |   Ali       |  1985
    3     |   Khan      |  1987
    4     |   Rizwan    |  1984

Address table:

address_id |  street  |   city  |  state  | country
----------------------------------------------------
   1       | MAJ Road | Karachi |  Sindh  | Pakistan
   2       | ABC Road | Multan  |  Punjab | Pakistan
   3       | XYZ Road | Riyadh  |    SA   | SA

Person_Address table:

person_id | address_id
----------------------
   1      |     1
   2      |     2
   3      |     3

Now I want to get all records of Person_Address table but also with their person and address records like this by one query:

person_id|    name  |  dob  | address_id |  street  |   city  |  state  | country
----------------------------------------------------------------------------------
    1    |   Naveed |  1988 |    1       | MAJ Road | Karachi |  Sindh  | Pakistan
    2    |   Ali    |  1985 |    2       | ABC Road | Multan  |  Punjab | Pakistan 
    3    |   Khan   |  1987 |    3       | XYZ Road | Riyadh  |    SA   | SA

How it is possible using zend? Thanks

Answer 1

I'm not sure if you're looking for SQL to do the above, or code using Zend's facilities. Given the presence of "sql" and "joins" in the tags, here's the SQL you'd need:

SELECT p.person_id, p.name, p.dob, a.address_id, street, city, state, country
FROM person p
INNER JOIN Person_Address pa ON pa.person_id = p.person_id
INNER JOIN Address a ON a.address_id = pa.address_id

Bear in mind that the Person_Address tells us that there's a many-to-many relationship between a Person and an Address. Many Persons may share an Address, and a Person may have more than one address.

The SQL above will show ALL such relationships. So if Naveed has two Address records, you will have two rows in the result set with person_id = 1.

Answer 2

The reference guide is the best starting point to learn about Zend_Db_Select. Along with my example below, of course:

//$db is an instance of Zend_Db_Adapter_Abstract
$select = $db->select();
$select->from(array('p' => 'person'), array('person_id', 'name', 'dob'))
       ->join(array('pa' => 'Person_Address'), 'pa.person_id = p.person_id', array())
       ->join(array('a' => 'Address'), 'a.address_id = pa.address_id', array('address_id', 'street', 'city', 'state', 'country'));

It's then as simple as this to fetch a row:

$db->fetchRow($select);

In debugging Zend_Db_Select there's a clever trick you can use - simply print the select object, which in turn invokes the toString method to produce SQl:

echo $select; //prints SQL