Pull Request for Issue # .

Summary of Changes

Modified query->delete() method to permit control of adding the table to the FROM clause as well as the DELETE clause. Creates valid sql syntax to support joined tables.

Testing Instructions

Will create a test module if it makes sense.

Documentation Changes Required

Will add some documentation if it makes sense.

Additional Comments

This PR permits a DELETE query to include JOINs on multiple tables, using Joomla's query classes.

Previously, you could not execute the following:

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 trying to use the query classes such as 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']);

there is a problem 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.

This change modifies the query->delete() to the following:

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 you can build a multitable query like this:

$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.

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.