JOINS in Zend_Db_Select

OM uses Zend Version 1.12.16 Show archive.org snapshot .

Adding Another Table to the Query with JOIN

Many useful queries involve using a JOIN to combine rows from multiple tables. You can add tables to a Zend_Db_Select query using the join() method. Using this method is similar to the from() method, except you can also specify a join condition in most cases.

Copy// Build this query:
//   SELECT p."product_id", p."product_name", l.*
//   FROM "products" AS p JOIN "line_items" AS l
//     ON p.product_id = l.product_id
 
$select = $db->select()
    ->from(array('p' => 'products'), array('product_id', 'product_name'))
    ->join(
        array('l' => 'line_items'),
        'p.product_id = l.product_id')
;

The second argument to join() is a string that is the join condition. This is an expression that declares the criteria by which rows in one table match rows in the other table. You can use correlation names in this expression.

Note: No quoting is applied to the expression you specify for the join condition; if you have column names that need to be quoted, you must use quoteIdentifier() as you form the string for the join condition.

The third argument to join() is an array of column names, like that used in the from() method. It defaults to "*", supports correlation names, expressions, and Zend_Db_Expr in the same way as the array of column names in the from() method.

To select no columns from a table, use an empty array for the list of columns. This usage works in the from() method too, but typically you want some columns from the primary table in your queries, whereas you might want no columns from a joined table.

SQL has several types of joins. See the list below for the methods to support different join types in Zend_Db_Select.

INNER JOIN with the join(table, join, [columns]) or joinInner(table, join, [columns]) methods.

This may be the most common type of join. Rows from each table are compared using the join condition you specify. The result set includes only the rows that satisfy the join condition. The result set can be empty if no rows satisfy this condition.

All RDBMS brands support this join type.

LEFT JOIN with the joinLeft(table, condition, [columns]) method.

All rows from the left operand table are included, matching rows from the right operand table included, and the columns from the right operand table are filled with NULL if no row exists matching the left table.

All RDBMS brands support this join type.

RIGHT JOIN with the joinRight(table, condition, [columns]) method.

Right outer join is the complement of left outer join. All rows from the right operand table are included, matching rows from the left operand table included, and the columns from the left operand table are filled with NULL's if no row exists matching the right table.

Some RDBMS brands don't support this join type, but in general any right join can be represented as a left join by reversing the order of the tables.

FULL JOIN with the joinFull(table, condition, [columns]) method.

A full outer join is like combining a left outer join and a right outer join. All rows from both tables are included, paired with each other on the same row of the result set if they satisfy the join condition, and otherwise paired with NULL's in place of columns from the other table.

Some RDBMS brands don't support this join type.

CROSS JOIN with the joinCross(table, [columns]) method.

A cross join is a Cartesian product. Every row in the first table is matched to every row in the second table. Therefore the number of rows in the result set is equal to the product of the number of rows in each table. You can filter the result set using conditions in a WHERE clause; in this way a cross join is similar to the old SQL-89 join syntax.

The joinCross() method has no parameter to specify the join condition. Some RDBMS brands don't support this join type.

NATURAL JOIN with the joinNatural(table, [columns]) method.

A natural join compares any columns that appear with the same name in both tables. The comparison is equality of all the columns; comparing the columns using inequality is not a natural join. Only natural inner joins are supported by this API, even though SQL permits natural outer joins as well.

The joinNatural() method has no parameter to specify the join condition.

JoinUsing()

In addition to these join methods, you can simplify your queries by using the JoinUsing methods. Instead of supplying a full condition to your join, you simply pass the column name on which to join and the Zend_Db_Select object completes the condition for you.

Copy// Build this query:
//   SELECT *
//   FROM "table1"
//   JOIN "table2"
//   ON "table1".column1 = "table2".column1
//   WHERE column2 = 'foo'
 
$select = $db->select()
    ->from('table1')
    ->joinUsing('table2', 'column1')
    ->where('column2 = ?', 'foo');

Each of the applicable join methods in the Zend_Db_Select component has a corresponding 'using' method.

• joinUsing(table, join, [columns]) and joinInnerUsing(table, join, [columns])
• joinLeftUsing(table, join, [columns])
• joinRightUsing(table, join, [columns])
• joinFullUsing(table, join, [columns])

Right Join with orWhere

CopySELECT `m`.`cumulation`, `e`.`attribute_id` 
FROM `util_cumulation` AS `m`
RIGHT JOIN `eav_attribute` AS `e` 
ON e.attribute_id = m.attribute_id 
WHERE (e.attribute_code = 'coverage_amount') AND ((m.member_id = 157) OR (m.member_idd = 166))

Note the OR in WHERE clause is properly parenthesized.

Copy    public function getCumulation($memberId, $attributeCode)
    {
        $adapter = $this->_getReadAdapter();
        $select = $adapter->select()
            ->from(['m' => $this->getMainTable()], 'cumulation')
            ->joinRight(
                ['e' => $this->getTable('eav/attribute')],
                'e.attribute_id = m.attribute_id AND m.member_id = ' . $memberId,
                ['attribute_id']
            )
            ->where('(m.member_id = ?', $memberId)
            ->orWhere('m.member_idd = ?)', $memberIdd);

        return $adapter->fetchRow($select);
    }

Right Join To Include Right Table On Non-Existent Row

CopySELECT `m`.`cumulation`, `e`.`attribute_id` 
FROM `scicom_benefit_utilization_cumulation` AS `m`
 RIGHT JOIN `eav_attribute` AS `e` 
 ON e.attribute_id = m.attribute_id AND m.member_id = 1577
 WHERE (e.attribute_code = 'some_code')

It returns the following array if member_id row is missing in main table.

Copy array(2) {
  ["cumulation"] => NULL
  ["attribute_id"] => string(3) "207"
}