Zikula Blog

Zikula 1.1 has been released a while ago and I have to admit that it's maybe a little bit late for a "what is new" article, there are nevertheless two changes relating the JOIN processing of DBUtil which are worth to be mentioned.

---

The first one is the support of different join methods. The joinInfo array now has a new key "join_method" which can be one out of:

• LEFT JOIN
• RIGHT JOIN
• INNER JOIN

For backward compatibility LEFT JOIN is default.

The second one is a backport from FilterUtil in 2.0. A new function "DBUtil::expandColumnsWithJoinInfo($columns, $joinInfo)" helps to handle queries with JOINs.

What does it do? It takes the $column array of the main table and an array of join informations. Then it adds all joined fields to this array, named as you named them in the joinInfo. Also it prefixes the corresponding table alias to each field, also of the main table.

Why use it? The use of JOIN had two main problems:

1. You can't be sure what alias which table has, especially with external tools (FilterUtil) it's simply impossible!
2. It's ugly to use in WHERE clauses etc.

Now, let us see an example:

Let us expect, we have 3 tables named table_one table_two and table_three and we want to join table_two and table_three to table_one and have the join information for that in $joinInfo.

In 1.0 you wrote:

$pntables = pnDBGetTables();

	
$onecolumns = $pntables['table_one_column'];

$twocolumns = $pntables['table_two_column'];

$threecolumns = $pntables['table_three_column'];
	
$where = 'tbl.' . $onecolumns['id'] . ' = a.' . $twocolumns['id']

       . ' AND tbl.' . $onecolumns['id'] . ' = b.' . $threecolumns['id'];
	
$result = DBUtil::selectExpandedObjectArray('table_one', $joinInfo, $where);

In 1.1:

$pntables = pnDBGetTables();

$onecolumns = $pntables['table_one_column'];

$columns = DBUtil::expandColumnsWithJoinInfo($onecolumns, $joinInfo);

	
$where = '$columns[id] = $columns[two_id] AND $columns[id]  = $columns[three_id]';
	
$result = DBUtil::selectExpandedObjectArray('table_one', $joinInfo, $where);