Deleting rows in two different tables

Question

I'm trying to make function (task) and deleting rows in two tables. But I don't get this sql query working, I only got this error:

1064 - An error has occurred.

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INNER JOIN `#__codes2` WHERE #__codes' at line 2

What is the right way to doing this in Joomla?

public function delete_codes(){

  $id = $this->input->getInt('id'); 

  $db = JFactory::getDBO(); 
//$sql = "DELETE FROM `#__codes` WHERE `code_id` = $id";

  $sql = "DELETE FROM `#__codes` 
             INNER JOIN `#__codes2 
             WHERE #__codes.code_id = $id
             AND #__codes2.id = $id";

  $db->setQuery($sql); 
  $db->execute();
  
die();

}

Answer 1

Why don't you do it in 2 steps?

I would do something like the following code. As you can see I use name spacing (since Joomla 3.8), used quoteName the table + field names and added a try/catch block.

use Joomla\CMS\Factory;

public function delete_codes()
{
$id = $this->input->getInt('id'); 

$query = $db->getQuery(true)
->delete($db->quoteName('#__codes'))
->where($db->quoteName('code_id') . ' = ' . (int) $id);
$db->setQuery($query);

try
{
    $db->execute();
}
catch (RuntimeException $error)
{
    throw new Exception($error);
}

// Clear the query object before re-using it
$query->clear()
->delete($db->quoteName('#__codes2'))
->where($db->quoteName('id') . ' = ' . (int) $id);
$db->setQuery($query);

try
{
    $db->execute();
}
catch (RuntimeException $error)
{
    throw new Exception($error);
}

}

Answer 2

You can definitely delete rows from multiple tables in a single query, but Joomla doesn't offer a query-building technique to accommodate this.

If you absolutely demand that query builder methods must be used, then you'll need to conduct some "surgery" (which isn't very pretty) on the generated query before executing it -- see https://joomla.stackexchange.com/a/24776/12352

Whenever a framework or querybuilding class doesn't offer methods for specific sql tasks, it usually comes down to two reasons:

1. The author(s) didn't think to do it or didn't get around to doing it yet, or more likely...
2. The author(s) considered implementing a method which accommodates the technique, but alas there isn't a clean/guaranteed way to cater to the full spectrum of databases in circulation.

Since Joomla doesn't offer a single-query approach to deleting with JOINs, you can write your own raw sql. Luckily, the variable that you are injecting into your script will be cast as an integer by getInt() -- this means that your query will be stable/secure. Although as a general aside, I don't support the use of $_GET data whenever the database is going to be writing data (INSERT|UPDATE|DELETE) -- for these actions, I always promote $_POST.

Untested snippet:

public function delete_codes(){
    $db = JFactory::getDBO(); 
    $sql = "DELETE c1, c2
            FROM `#__codes` AS c1 
            INNER JOIN `#__codes2` AS c2 ON c1.code_id = c2.id
            WHERE c1.code_id = " . $this->input->getInt('id');
    $db->setQuery($sql); 
    $db->execute();
}

Effectively, you needed to:

• nominate the table aliases between the DELETE and FROM
• add the missing backtick after your second table declaration
• write the ON clause for your INNER JOIN

Some relevant pages to support the credibility of my advice:

• https://www.mysqltutorial.org/mysql-delete-join/
• Delete from 2 tables using INNER JOIN
• How do I delete from multiple tables using INNER JOIN in SQL server