blog.barros.ws » sfPropelActAsSortableBehaviorPlugin group patch

sfPropelActAsSortableBehaviorPlugin group patch

February 20th, 2009 by Carlos Barros

I guess that most Symfony developers should know this Symfony plugin, but for those who not, here’s an small quote:

“The sfPropelActAsSortableBehaviorPlugin is a symfony plugin that provides a new Propel behavior.Model classes with this behavior enabled become sortable, which means that they have new methods to deal with a position attribute.”

This Symfony plugin is really useful as it can save you a lot of code if you need to sort your objects, but in a recent project I worked on, I noticed this plugin lacks one functionality: it can’t handle “sorting groups” within the same object. For instance, suppose you have an Product and Category tables, and that each product belongs to a single category. Then, suppose you want to be able to sort your products so you can control how they show up in your pages. Using sfPropelActAsSortableBehaviorPlugin you can only sort the entire Product table, and that’s not really what we want. We need to be able to sort products within each category.
I guess this problem is really common, and as I had to handle this in a recent project, I decided to write a patch for this plugin, and I’d like to share it. Before one ask me why I didn’t submit it as a official ticket, the reason is that there is already a patch submitted to handle this issue, from almost a year ago, that was not released yet, and I bet most people (including me) is not aware of, so I’m writing this post to tell about this for other users, and maybe make Kris Wallsmith (current leader) release his version, that in fact seem to be more powerful than mine, as it support creating sorting groups using more than one column.

So, how does it work?? sfPropelActAsSortableBehaviorPlugin is really simple to use, all you need is a Integer column in your model, and then activate the new behavior for you class using something like this:

sfPropelBehavior::add('Item', array('act_as_sortable' => array('column' => 'rank')));

After applying this patch, the only thing you need to do is to add one more element to the above array:

sfPropelBehavior::add('Item', array('act_as_sortable' => array('column' => 'rank','group' => 'category_id')));

And that’s it. New sfPropelActAsSortableBehaviorPlugin will use “category_id” as a “sorting group”.

You can now use all methods provided by the new behavior to sort your objects, just like you used to do without the patch. Also, if you don’t have a group category, just omit it from behavior initialization, and it will work as if u didn’t apply this patch.

Find below the full patch:

diff -ur sfPropelActAsSortableBehaviorPlugin-0.6.1/lib/sfPropelActAsSortableBehavior.class.php sfPropelActAsSortableBehaviorPlugin-0.6.1-new/lib/sfPropelActAsSortableBehavior.class.php
--- sfPropelActAsSortableBehaviorPlugin-0.6.1/lib/sfPropelActAsSortableBehavior.class.php	2007-08-07 10:56:57.000000000 -0300
+++ sfPropelActAsSortableBehaviorPlugin-0.6.1-new/lib/sfPropelActAsSortableBehavior.class.php	2009-02-20 10:59:25.000000000 -0300
@@ -21,14 +21,18 @@
    *
    * @return mixed sortable object
    **/
-  public static function retrieveByPosition($peerClass, $position, $con = null)
+  public static function retrieveByPosition($peerClass, $position, $con = null, $group = null)
   {
     $rankColumnName = sfConfig::get('propel_behavior_act_as_sortable_'.self::getClassFromPeerClass($peerClass).'_column', 'rank');
-    $rankColumnPhpName = call_user_func(array($peerClass, 'translateFieldName'), $rankColumnName, BasePeer::TYPE_FIELDNAME, BasePeer::TYPE_COLNAME);
+    $rankColumnPhpName = call_user_func(array($peerClass, 'translateFieldName'), $rankColumnName, BasePeer::TYPE_FIELDNAME, BasePeer::TYPE_COLNAME);
+    $groupColumnName = sfConfig::get('propel_behavior_act_as_sortable_'.self::getClassFromPeerClass($peerClass).'_group');
+    if($groupColumnName) $groupColumnPhpName = call_user_func(array($peerClass, 'translateFieldName'), $groupColumnName, BasePeer::TYPE_FIELDNAME, BasePeer::TYPE_COLNAME);
+    
     if(!$con) $con = Propel::getConnection(constant("$peerClass::DATABASE_NAME"));
 
     $c = new Criteria;
-    $c->add($rankColumnPhpName, $position);
+    $c->add($rankColumnPhpName, $position);
+    if($groupColumnName) $c->add($groupColumnPhpName, $group);
 
     return call_user_func(array($peerClass, 'doSelectOne'), $c, $con); 
   }
@@ -41,16 +45,26 @@
    *
    * @return integer maximum position
    **/
-  public static function getMaxPosition($peerClass, $con = null)
+  public static function getMaxPosition($peerClass, $con = null, $group = null)
   {
-    $rankColumnName = sfConfig::get('propel_behavior_act_as_sortable_'.self::getClassFromPeerClass($peerClass).'_column', 'rank');
+    $rankColumnName = sfConfig::get('propel_behavior_act_as_sortable_'.self::getClassFromPeerClass($peerClass).'_column', 'rank');
+    $groupColumnName = sfConfig::get('propel_behavior_act_as_sortable_'.self::getClassFromPeerClass($peerClass).'_group');
+    
     if(!$con) $con = Propel::getConnection(constant("$peerClass::DATABASE_NAME"));
 
     $sql = sprintf('SELECT MAX(%s) AS max FROM %s', 
       $rankColumnName,
       constant("$peerClass::TABLE_NAME")
-    ); 
-    $rs = $con->prepareStatement($sql)->executeQuery();
+    ); 
+    
+    if($groupColumnName)
+    {
+    	$sql .= sprintf(" WHERE %s = ?",$groupColumnName);
+    	$stmt = $con->prepareStatement($sql);
+    	$stmt->setString(1,$group);	
+    }
+    else $stmt = $con->prepareStatement($sql);
+    $rs = $stmt->executeQuery();
     $rs->next();
 
     return $rs->getInt('max');
@@ -66,10 +80,13 @@
    *
    * @return array list of sortable objects
    **/
-  public static function doSelectOrderByPosition($peerClass, $order = Criteria::ASC, $criteria = null, $con = null)
+  public static function doSelectOrderByPosition($peerClass, $order = Criteria::ASC, $criteria = null, $con = null, $group = null)
   {
     $rankColumnName = sfConfig::get('propel_behavior_act_as_sortable_'.self::getClassFromPeerClass($peerClass).'_column', 'rank');
-    $rankColumnPhpName = call_user_func(array($peerClass, 'translateFieldName'), $rankColumnName, BasePeer::TYPE_FIELDNAME, BasePeer::TYPE_COLNAME);
+    $rankColumnPhpName = call_user_func(array($peerClass, 'translateFieldName'), $rankColumnName, BasePeer::TYPE_FIELDNAME, BasePeer::TYPE_COLNAME);
+    $groupColumnName = sfConfig::get('propel_behavior_act_as_sortable_'.self::getClassFromPeerClass($peerClass).'_group');
+    if($groupColumnName) $groupColumnPhpName = call_user_func(array($peerClass, 'translateFieldName'), $groupColumnName, BasePeer::TYPE_FIELDNAME, BasePeer::TYPE_COLNAME);
+    
     if(!$con) $con = Propel::getConnection(constant("$peerClass::DATABASE_NAME"));
 
     if ($criteria === null) 
@@ -90,7 +107,8 @@
     else
     {
       $criteria->addDescendingOrderByColumn($rankColumnPhpName);
-    }
+    }
+    if($groupColumnName) $c->add($groupColumnPhpName,$group);
 
     return call_user_func(array($peerClass, 'doSelect'), $criteria, $con); 
   }
@@ -159,6 +177,32 @@
   public function setPosition($object, $position)
   {
     return $object->setByName(sfConfig::get('propel_behavior_act_as_sortable_'.get_class($object).'_column', 'rank'), $position, BasePeer::TYPE_FIELDNAME);
+  }
+  
+  /**
+   * Gets the group of a sortable object
+   * 
+   * @param mixed sortable object
+   * 
+   * @return mixed group of the sortable object
+   */
+  public function getGroup($object)
+  {
+   	$groupColumnName = sfConfig::get('propel_behavior_act_as_sortable_'.get_class($object).'_group');
+   	if($groupColumnName) return $object->getByName($groupColumnName, BasePeer::TYPE_FIELDNAME);
+   	else return null;
+  }
+  
+  /**
+   * Sets the group of a sortable object
+   * 
+   * @param mixed sortable object
+   * @param mixed group value
+   */
+  public function setGroup($object, $group)
+  {
+  	$groupColumnName = sfConfig::get('propel_behavior_act_as_sortable_'.get_class($object).'_group');
+  	if($groupColumnName) return $object->setByName($groupColumnName, $group, BasePeer::TYPE_FIELDNAME);
   }
 
   /**
@@ -170,7 +214,7 @@
    **/
   public function getNext($object)
   {
-    return self::retrieveByPosition(get_class($object).'Peer', self::getPosition($object) + 1);
+    return self::retrieveByPosition(get_class($object).'Peer', self::getPosition($object) + 1, null, self::getGroup($object));
   }
 
   /**
@@ -182,7 +226,7 @@
    **/
   public function getPrevious($object)
   {
-    return self::retrieveByPosition(get_class($object).'Peer', self::getPosition($object) - 1);
+    return self::retrieveByPosition(get_class($object).'Peer', self::getPosition($object) - 1, null, self::getGroup($object));
   }
 
   /**
@@ -206,7 +250,7 @@
    **/    
   public function isLast($object)
   {
-    return self::getPosition($object) == self::getMaxPosition(get_class($object).'Peer');
+    return self::getPosition($object) == self::getMaxPosition(get_class($object).'Peer', null, self::getGroup($object));
   }
 
   /**
@@ -286,31 +330,36 @@
   {
     $class = get_class($object);
     $peerClass = $class.'Peer';
-    $rankColumnName = sfConfig::get('propel_behavior_act_as_sortable_'.$class.'_column', 'rank');
+    $rankColumnName = sfConfig::get('propel_behavior_act_as_sortable_'.$class.'_column', 'rank');
+    $groupColumnName = sfConfig::get('propel_behavior_act_as_sortable_'.$class.'_group');
     if(!$con) $con = Propel::getConnection(constant("$peerClass::DATABASE_NAME"));
 
     $oldPosition = self::getPosition($object);
     if ($oldPosition == $newPosition) return $oldPosition;
-
+
     try
     {
       $con->begin();
 
       // Move the object away
-      self::setPosition($object, self::getMaxPosition($peerClass) + 1);
+      self::setPosition($object, self::getMaxPosition($peerClass,null,self::getGroup($object)) + 1);
       $object->save();
 
       // Shift the objects between the old and the new position
-      $query = sprintf('UPDATE %s SET %s = %s %s 1 WHERE %s BETWEEN ? AND ?',
+      $query = sprintf('UPDATE %s SET %s = %s %s 1 WHERE %s BETWEEN ? AND ?%s',
         constant("$peerClass::TABLE_NAME"),
         $rankColumnName,
         $rankColumnName,
         ($oldPosition < $newPosition) ? '-' : '+',
-        $rankColumnName
-      );
+        $rankColumnName,
+        $groupColumnName?" AND $groupColumnName = ?":""
+        
+      );
+      
       $stmt = $con->prepareStatement($query);
       $stmt->setInt(1, min($oldPosition, $newPosition));
-      $stmt->setInt(2, max($oldPosition, $newPosition));
+      $stmt->setInt(2, max($oldPosition, $newPosition));
+      if($groupColumnName) $stmt->setString(3,self::getGroup($object));
       $stmt->executeQuery();
 
       // Move the object back in
@@ -357,9 +406,9 @@
   public function moveToBottom($object, $con = null)
   {
     $class = get_class($object);
-    $peerClass = $class.'Peer';
-
-    return self::moveToPosition($object, self::getMaxPosition($peerClass), $con);
+    $peerClass = $class.'Peer';
+    
+    return self::moveToPosition($object, self::getMaxPosition($peerClass,null,self::getGroup($object)), $con);
   }
 
   /**
@@ -378,7 +427,8 @@
   {
     $class = get_class($object);
     $peerClass = $class.'Peer';
-    $rankColumnName = sfConfig::get('propel_behavior_act_as_sortable_'.$class.'_column', 'rank');
+    $rankColumnName = sfConfig::get('propel_behavior_act_as_sortable_'.$class.'_column', 'rank');
+    $groupColumnName = sfConfig::get('propel_behavior_act_as_sortable_'.$class.'_group');
     if(!$con) $con = Propel::getConnection(constant("$peerClass::DATABASE_NAME"));
 
     try
@@ -386,14 +436,16 @@
       $con->begin();
 
       // Shift the objects with a position higher than the given position
-      $query = sprintf('UPDATE %s SET %s = %s + 1 WHERE %s >= ?',
+      $query = sprintf('UPDATE %s SET %s = %s + 1 WHERE %s >= ?%s',
         constant("$peerClass::TABLE_NAME"),
         $rankColumnName,
         $rankColumnName,
-        $rankColumnName
+        $rankColumnName,
+        $groupColumnName?" AND $groupColumnName = ?":""
       );
       $stmt = $con->prepareStatement($query);
-      $stmt->setInt(1, $position);
+      $stmt->setInt(1, $position);
+      if($groupColumnName) $stmt->setString(2,self::getGroup($object));
       $stmt->executeQuery();
 
       // Move the object in the list, at the given position
@@ -427,7 +479,7 @@
     // new records need to be initialized with position = maxPosition +1 unless position is already set
     if(!$object->getId() && !$object->isColumnModified($rankColumnPhpName))
     {
-      self::setPosition($object, self::getMaxPosition($peerClass)+1);
+      self::setPosition($object, self::getMaxPosition($peerClass,null,self::getGroup($object))+1);
     }
   } 
 
@@ -442,17 +494,20 @@
   {  
     $class = get_class($object);
     $peerClass = $class.'Peer';
-    $rankColumnName = sfConfig::get('propel_behavior_act_as_sortable_'.$class.'_column', 'rank');
+    $rankColumnName = sfConfig::get('propel_behavior_act_as_sortable_'.$class.'_column', 'rank');
+    $groupColumnName = sfConfig::get('propel_behavior_act_as_sortable_'.$class.'_group');
     if(!$con) $con = Propel::getConnection(constant("$peerClass::DATABASE_NAME"));
 
-    $query = sprintf('UPDATE %s SET %s = %s - 1 WHERE %s > ?',
+    $query = sprintf('UPDATE %s SET %s = %s - 1 WHERE %s > ?%s',
       constant("$peerClass::TABLE_NAME"),
       $rankColumnName,
       $rankColumnName,
-      $rankColumnName
+      $rankColumnName,
+      $groupColumnName?" AND $groupColumnName = ?":""
     );
     $stmt = $con->prepareStatement($query);
-    $stmt->setInt(1, $object->getPosition());
+    $stmt->setInt(1, $object->getPosition());
+    if($groupColumnName) $stmt->setString(2, self::getGroup($object));
     $stmt->executeQuery();
   }
 }

You can also download the patch here.
For those who want to take a look at Kris Wallsmith’s patch, just click here.

Share this post...

In symfony | 5 Comments

Tags:behavior,plugin,propel,symfony

Comments

COil

February 23rd, 2009 at 5:42

Hi Carlos,

Good job, could you commit this patch in the svn repository ? I find the plugin quiet useless without this ability to define "groups" within a table…

++ COil

Carlos Barros

February 25th, 2009 at 13:45

Hi COil… I don’t think I can commit this patch to svn… First reason is because I’m not developer of this plugin, and the other reason is they already have such a patch as a ticket (created by plugin leader, afaik), and that patch is better because it supports multiple columns group… I think one way to try to get this released is to go to the ticket @ http://trac.symfony-project.org/ticket/3240 and ask…. I’ll do it too…

Regards

Carlos

Carlos Barros

February 25th, 2009 at 13:49

Hi COil… I just noticed you already asked about the patch, some months ago… I just did my contribution…

Carlos

Carlos Barros

February 25th, 2009 at 13:57

Ooopps… just noticed the patch link was broken… Fixed it now……

COil

February 25th, 2009 at 16:07

Yes you are right ! It’s because i already had the same problem. I have added another comment. See you.

sfPropelActAsSortableBehaviorPlugin group patch

Comments

Blogroll

Categories

Calendar

February 2009
S	M	T	W	T	F	S
« Jan				Mar »
1	2	3	4	5	6	7
8	9	10	11	12	13	14
15	16	17	18	19	20	21
22	23	24	25	26	27	28