2

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?

2
  • Your bestia_users_handles_mapping table should have a column such as user_id, uid or any other name that associates the data in this table with the other one
    – Lodder
    Jul 10, 2015 at 10:51
  • It has. It has the field "handleid" - but the function does not know the handleid (which is the id of the #__bestia_users_handles entry).
    – MyFault
    Jul 10, 2015 at 10:53

2 Answers 2

0

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.

1
  • I don't think you have to use $query = $db->getQuery(true); after $query->clear(); Jul 12, 2015 at 23:41
1

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:

enter image description here

Useful links:

2
  • How can I das this to my install.sql-file?
    – MyFault
    Jul 13, 2015 at 6:51
  • In last of Useful links you will find sample. Just read them. Jul 13, 2015 at 9:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.