MonthDecember 2012

Deleting Orphan Records in MySQL

Recently I ran into an issue with an application I had developed where I made the mistake of not deleting some child table records when the parent record was deleted. After the app had been in beta for a month or two, I realized that I had thousands of records in the child tables that had no parents and were thus “orphan” records.

After juggling a few ideas in my head, I decided the best approach to this would be to make a temporary table of just the orphan items and then delete from the child table where the ID matches an ID in my orphans table. What does this look like in MySQL?

CREATE TEMPORARY TABLE Orphans AS (
  SELECT ChildTable.id FROM ChildTable
  LEFT OUTER JOIN ParentTable ON ChildTable.RelationKey = ParentTable.RelationKey
  WHERE  ParentTable.RelationKey IS NULL
);
 
DELETE FROM ChildTable WHERE ChildTable.id IN (SELECT id FROM Orphans);
DROP TABLE Orphans;

How Does it Work?
Essentially, the first line of the query makes a new temporary table that we will delete when we are done:
CREATE TEMPORARY TABLE Orphans AS (

The second line selects the primary key (id) of every record in Child Table.
The third line creates a Left Outer Join with the parent table that used to have records associated with your orphaned records, based on whichever field you use to associate the two tables (RelationKey).
The fourth line specifies that we only want to select the ones where the RelationKey we provide is NULL, meaning it couldn’t find a parent element.

At this point we build the table and we now have a table of all orphaned records.
The last command just runs a DELETE query telling the database to delete any records that exist in both the ChildTable and the new Orphans table. Once that’s done, delete the Orphans table and the operation is done.

Composer with FuelPHP

As I noted in an earlier post about my switch from CodeIgniter to FuelPHP, I have since started moving much of my development from FuelPHP to Laravel. Most recently I have been using Laravel 4 and have become acquainted and infatuated with Composer packages. Composer is bringing the package functionality that PHP needs and PEAR can’t possibly deliver to the current PHP community. Unfortunately, the current version (1.4 as I write this) of FuelPHP doesn’t have Composer baked in yet. The beauty of Composer is that installing it into an application is so simple that this is no longer a barrier.

Note: FuelPHP 2.0, which is major milestone and change for FuelPHP, will supposedly include Composer. There is talk of a 1.x version including Composer in order to bridge the gap between the current releases and the much changed 2.0 release.

Setting Up Composer to Work With FuelPHP 1.x

1) Download Composer. Although obvious, this is a necessary step. I find that command line installation is easiest. Navigate to the root directory of your application and run the following code:

curl -s https://getcomposer.org/installer | php

2) At this point you should have a composer.phar file installed in your application’s root directory. The next step is telling Composer what packages you want to add to your project. Composer uses a file named composer.json, so create this file in the root directory. The most basic example is laid out below, but look for more examples and information in the Composer documentation.

{
     "config": {
        "vendor-dir": "fuel/app/vendor"
     },
     "require": {
        "monolog/monolog": "1.2.*"
     }
}

3) Now that we have the composer.phar file and a composer.json file with the packages we need, run the composer installation, which will download the packages and place them in the vendor directory specified in composer.json (in this case the default fuel vendor directory).

php composer.phar install

4) Once the installation has finished (may take some time, depending on the number of packages you have) we can assume that all packages are installed to the fuel/app/vendor directory and now we need to tell FuelPHP to look at this directory for our new packages.
Open your bootstrap file (fuel/app/bootstrap.php) and add the following code after the AutoLoader::register() that is already there:

require APPPATH.'vendor/autoload.php';

At this point you have finished the setup and installation of Composer.
The next step is to read the Composer getting started guide to make sure you’re up to speed with how it works.
The basics you will need to know right off the bat:

  • php composer.phar update – this command will update all your packages to the latest version based on criteria specified in your composer.json file.
  • php composer.phar dump-autoload – run this command whenever you add or remove packages so your autoload.php file can be updated.

© 2017 Craig Hooghiem

Theme by Anders NorenUp ↑