Get ID of deleted database-entry

Question

I would like to create a delete-method, which deletes an entry in a table AND the attached entries in a second table.

This is my source so far:

        // Delete entry in users_handles
        $db = JFactory::getDbo();
        $query = $db->getQuery(true);            

        $conditions = array(
            $db->quoteName('userid') . ' = ' . $db->quote((int)$userid), 
            $db->quoteName('type') . ' = ' . $db->quote($type),
        );

        $query->delete($db->quoteName('#__bestia_users_handles'));
        $query->where($conditions);

        $db->setQuery($query);
        $result = $db->execute();

        $db = JFactory::getDbo();
        $query = $db->getQuery(true);            

        $conditions = array($db->quoteName('handleid') . ' = ' . GET ID FROM ENTRY THAT HAS BEEN DELETED BEFORE));

        $query->delete($db->quoteName('#__bestia_users_handles_mapping'));
        $query->where($conditions);

        $db->setQuery($query);
        $result = $db->execute();

Is this possible?

Answer 1

Here is an example of how your query would work

function delete($userid)
{
    // Delete entry in users_handles
    $db = JFactory::getDbo();
    $query = $db->getQuery(true);            

    $conditions = array(
        $db->quoteName('userid') . ' = ' . $db->quote((int)$userid), 
        $db->quoteName('type') . ' = ' . $db->quote($type),
    );

    $query->delete($db->quoteName('#__bestia_users_handles'));
    $query->where($conditions);

    $db->setQuery($query);
    $db->execute();

    $query->clear();

    $query = $db->getQuery(true);  

    $conditions = array($db->quoteName('handleid') . ' = ' . $userid));

    $query->delete($db->quoteName('#__bestia_users_handles_mapping'));
    $query->where($conditions);

    $db->setQuery($query);
    $db->execute();
}

You will see I've used $query->clear(); which clears the data from the query.

Then simply used $userid for both queries.

Answer 2

I assume you use MySQL and InnoDB. It would be faster, easier and resource/speed efficient to use MySQL FOREIGN KEY constraint and add CASCADE in ON DELETE in foreign key definition. In short it will drop all child table rows that has reference to parent table rows that are deleted. This also don't require any changes in model file. You delete only parent table rows (as model usually do) and database system do the rest. If you use MySQL Workbench to construct your database here you can find a place where you can make this work:

Useful links:

• MySQL foreign keys explanation.
• MySQL Workbench website.
• Good explanation on MySQL ON UPDATE/ON DELETE
• Another good explanation on MySQL ON UPDATE/ON DELETE