Author: Matt Daum

Recently I’ve been having one hell of a time with SQL Joins. It isn’t that I don’t understand how to use them, its that they have came to haunt me in completely opposite instances.

First: An application I had written a while ago that was suppose to be kept small, and very simple which continually was enlarged and became very big was running slow. I upgrade the person to our faster server believing it was just that our server that is meant for static sites couldn’t handle it. While it did greatly reduce the load time of the site(1/6) it was still slow for me. I looked into it and found that after all the alterations of the application which we had done, that one page had 1600 queries at its current state.

Before you think down upon our development abilities and our code efficiency let me put everything in perspective. The project was started as a very simple CRUD only database, that we did basically free for the a client who was a friend. Shortly after it continually was being expanded upon, and the friend could only afford minimal costs, so he asked us to just “hack” it together as it was a prototype. Well about a month later after development, we had possible one of the most hacked together prototypes for him. He never wanted to redo it from scratch so we could architect it correctly due to the cost. For the prototype, it worked fine and very fast. However the prototype turned into a beta test for the friend. He added much more information to the database, and the inefficient queries began to show. Now the application is loaded with information, and the inefficient queries are terrible. We have told him that if he plans to use this we should rewrite it from scratch as many other cool features could be used.

So back to the problem. Well we attempted to add JOINs to the program which should cut the number of queries by at least 1/10th. However when I added the left joins via Criteria/Propel I found that it was adding the joins twice. The reason they needed to be left joins is because the foreign keys were not required,

I noticed that for some reason the 1.1 forms do not allow for the rich date input that many people like to use, so I just created a new widget that allows for rich date input. See below for the widget’s code. It uses the input_date_tag widget in the ‘Form’ helper.

<?php
/**
* myWidgetFormRichDate is a rich date widget for 1.1+ forms
*
* @author Matt Daum matt [at] setfive.com
*/
class myWidgetFormRichDate extends sfWidgetFormDate
{

  /**
  * @param array $options An array of options
  * @param array $attributes An array of default HTML attributes
  *
  * @see sfWidgetForm
  */
  protected function configure($options = array(), $attributes = array())
  {
    parent::configure($options, $attributes);
  }

  /**
  * @param string $name The element name
  * @param string $value The value displayed in this widget
  * @param array $attributes An array of HTML attributes to be merged with the default HTML attributes
  * @param array $errors An array of errors for the field
  *
  * @return string An HTML tag string
  *
  * @see sfWidgetForm
  */
  public function render($name, $value = null, $attributes = array(), $errors = array())
  {
    //Get the date input function from Form helper
    use_helper('Form');
    //Make the widget rich
    $attributes['rich']=true;
    return input_date_tag($name,$value, $attributes);
  }
}

Today I was writing an administrator backend for a project we have. I had the code:

$old_feeds=RssFeedRelationPeer::doSelect($c);
if(count($old_feeds)>0){
   foreach ($old_feeds as $old) $old->delete();
}

I was trying to remove some old foreign constraints before I deleted the main object, however, for the longest time the “$old” objects were not deleting, but no errors were being thrown. I did the usual debugging, added a die() statement inside the count, it was going there. I added a die statement in the foreach, it went there. I thought, “It must not be pulling the right ‘old’ objects.” I then added a $old->getName(); to see exactly what objects it was going through-they were the right ones. It made no sense. I next tired after the $old->delete();to add a $old->save();. The error I received was “You cannot save an object that has been deleted.” This didn’t make sense, since the object was still in the database. It hit me then, I had this problem in the past. Propel hates tables without primary keys. I quickly just added a simple primary key to the model, and the delete statements worked.

Moral: If dealing with Propel and you are getting some unexpected behavior, with zero errors, check to see if you have a primary key; it may save you hours of head banging.