Hi all, I was trying to build a DELETE query with a LEFT JOIN on multiple tables, using Joomla's query classes. I note that the code obviously works fine with single table DELETEs, and I couldn't see a way to make it work for multiple. Here's the query I was trying to execute:

DELETE a, b, c, d
FROM #__table_a AS a
LEFT JOIN #__table_b AS b ON a.id=b.table_a_id
LEFT JOIN #__table_c AS c ON a.id=c.table_a_id
LEFT JOIN #__table_d AS d ON a.id=d.table_a_id
WHERE a.id = 15

This works fine when setting the above to a string $sql and calling $db->setQuery($sql), but when I try to use the query classes I initially set it up like this:

$query->delete('a')
   ->delete('b')
   ->delete('c')
   ->delete('d');
$query->from($db->qn(#__table_a', 'a'))
   ->leftJoin($db->qn('#__table_b', 'b') . ' ON a.id = b.table_a_id')
   ->leftJoin($db->qn('#__table_c', 'c') . ' ON a.id = c.table_a_id')
   ->leftJoin($db->qn('#__table_d', 'd') . ' ON a.id = d.table_a_id');
$query->where($db->qn('a.id') . ' = ' . (int) $pk['id']);

and is a problem with this in that the ->delete() class adds the DELETE element to the query, and also the table name to the FROM element, so its not possible to add joins. The query will only execute properly if there is one and only one table in the query. You end up with sql looking like this:

DELETE d
FROM a, b, c, d, #__table_a AS a
LEFT JOIN #__table_b AS b ON a.id=b.table_a_id
LEFT JOIN #__table_c AS c ON a.id=c.table_a_id
LEFT JOIN #__table_d AS d ON a.id=d.table_a_id
WHERE a.id = 15

which won't work. There are other ways to structure the query calls, but all of them seem to end up with invalid sql.

What I did was modify query.php from this:

public function delete($table = null)
{
    $this->type = 'delete';
    $this->delete = new JDatabaseQueryElement('DELETE', $table);

    if (!empty($table))
    {
        $this->from($table);
    }

    return $this;
}

to this:

public function delete($table = null, $addFrom = true)
{
    $this->type = 'delete';
    if(is_null($this->delete))
    {
        $this->delete = new JDatabaseQueryElement('DELETE', $table);
    }
    else
    {
        $this->delete->append($table);
    }

    if (!empty($table) && $addFrom)
    {
        $this->from($table);
    }

    return $this;
}

Then by building the query like this I was able to get it to work properly.

$query->delete('a', false)
      ->delete('b', false)
      ->delete('c', false)
      ->delete('d', false);

$query->from($db->qn(#__table_a', 'a'))
      ->leftJoin($db->qn('#__table_b', 'b') . ' ON a.id = b.table_a_id')
      ->leftJoin($db->qn('#__table_c', 'c') . ' ON a.id = c.table_a_id')
      ->leftJoin($db->qn('#__table_d', 'd') . ' ON a.id = d.table_a_id');

$query->where($db->qn('a.id') . ' = ' . (int) $pk['id']);

As an added bonus it does not appear to have any negative side effects on the rest of the codebase.

So my question is: is this a candidate for submission to the code? Two alternatives would be to just use the sql string without all the query classes, or enforcing referential integrity on the database and using a cascading delete. Neither seem as good a solution to me.

Thanks for your opinions.