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?

  • 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 '15 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 '15 at 10:53
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.

  • I don't think you have to use $query = $db->getQuery(true); after $query->clear(); – Artur Stępień Jul 12 '15 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:

  • How can I das this to my install.sql-file? – MyFault Jul 13 '15 at 6:51
  • In last of Useful links you will find sample. Just read them. – Artur Stępień Jul 13 '15 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.