CakePHP Model::findAllThreaded Frustrations

Been working on a shopping cart system at work, and we were having some weird issues with the category hierarchy manager we had built. For some reason (randomly it seemed at first) we would add a category and then shuffle its parent category and suddenly it would disappear from the threaded list. After about an hour of trying to debug the code for findAllThreaded (show below) I finally found the culprit in our case.

 function __doThread($data, $root, $index = 0) {
  $out = array();
  $sizeOf = sizeof($data);

  for ($ii = $index; $ii < $sizeOf; $ii++) {
   if (($data[$ii][$this->alias]['parent_id'] == $root) || (($root === null) && ($data[$ii][$this->alias]['parent_id'] == '0'))) {
    $tmp = $data[$ii];

    if (isset($data[$ii][$this->alias][$this->primaryKey])) {
     $tmp['children'] = $this->__doThread($data, $data[$ii][$this->alias][$this->primaryKey], 0);//$ii);
    } else {
     $tmp['children'] = null;
    }

    $out[] = $tmp;
   }
  }

  return $out;
 }

Basically, the way the function is structured if a child comes before a parent in the findAll results that findAllThreaded uses then it won't find it because it only looks after the parent for any children results, which forces you to do one of two things:

1. In our case, I edited the line $tmp['children'] = $this->__doThread(... to pass '0' for the $index parameter instead of $ii. This forces the function to go through all results each time. In our case we are only using findAllThreaded in the admin area of our cart, so we felt any hit in performance wasn't too serious. In your case, it might be.

2. Ensure the order you are using for the findAll results pulls results that are already structured somewhat hierarchal. You could have an order field that you sort by, but it needs to be somewhat static so that it can't be easily changed and mess up your list again (hence using something like the built in modified date field in CakePHP could cause some issues).

Hope this helps you bakers out there. If you have a better idea on how to ensure findAllThreaded works properly, drop a line in the comments.

EDIT: Actually, you can simplify all this by just ordering you results by the parent_id. So far that seems to work great without having to edit any of the cake core code. Plus it won't eat up as many resources as having to loop through all the results every time.

Two Coding Styles Face Off

I notice that anytime I'm programming a new (CakePHP) site I'm often fighting internally about my coding practices.

In the blue corner is quick and serviceable. Quick and serviceable has come in winning many fights. His speed makes him difficult to corner, and he can be very adept at completing many things quickly. Unfortunately, this also leads to more problems as situations arise that were not considered originally making him more vulnerable.

In the red corner is longer and more thorough. He often takes a while to get going initially, leading to losses in shorter bouts. However, when entering the later rounds, he becomes a more serious opponent, and his record becomes even more impressive in closing out projects, I mean fighters, effectively.

We've been working on a new shopping cart site lately, and I have an add to cart script that checks the current product being added (along with any product attributes that may differentiate it) against the current shopping cart to see whether to add it as a new product or to an existing one.

My original inclination was to check by pulling all attribute groups for a product. Since they are associated with only that product I could easily assume that those attributes were also in the product in the cart. A simple loop through and comparison with the attributes in the cart should do the trick.

However, what if someone administering the site changed an attribute after someone added something to their cart, but before they want back and decided to buy some more. While that may not happen very often, it's just that type of thing that causes headaches and late night calls from clients wondering why a customer is angry with an issue with the website.

So instead of making that assumption, I loop through all the product attribute groups. Then, I check specifically if each product attribute group has actually been submitted by the customer (which originally I was going to just assume actually took place), and then check to make sure that the actual attribute values correspond before adding to a previous cart item or making a new cart entry.

What practices give you fits? Let me know in the comments.

MySQL and I are in a fight over MAX() and SUM()

I have a sort of love/hate relationship with MySQL. Although I probably shouldn't drag MySQL into this. SQL and I have a love/hate relationship. I love creating database driven sites since it allows for so many possibilities.

But I'm no genius database guru, so when I have a complex query to try to put together our relationship usually is on the outs.

I had been working on a solution to get a list of weekly stats for stats that get run daily. I managed to get a query working that allows me to find the weekly stats for a specific person for every week and sort them however I need.

SELECT SUM(Stats) as AllStats, Name, week(logdate) AS week_num, MIN(logdate) AS week_start, MAX(logdate) AS week_end FROM stat_table WHERE person_id = 'XXXX' GROUP BY week(logdate) ORDER BY AllStats DESC;

However, I wanted to expand that to allow the query to pull the top people in a group and find out what week was their best week. This is where it started to break down. I thought I could switch things around a bit and use the following query...

SELECT MAX(SUM(Stats)) as MaxStats, Name, person_id, week(logdate) AS week_num, MIN(logdate) AS week_start, MAX(logdate) AS week_end FROM stat_table WHERE group_id = 'XXX' GROUP BY week(logdate), person_id ORDER BY week_num DESC;

That was a no go. And the weird thing was if I took out the MAX() function at the beginning (and only select SUM(Stats)) I could run all stats for that group.

But, thanks to some really helpful people at SitePoint's MySQL forum (r937 in particular) I got a new, uber-complex subquerying query that seems to work great. I've posted it below for anyone else that is looking for a way to find the MAX() of a SUM() or wants to find weekly data when they have multiple records per week.

SELECT week_totals.person_id,
week_totals.Name,
week_totals.week_num,
week_totals.week_start,
week_totals.week_end,
week_totals.AllStats
FROM (
SELECT person_id
, Name,
, WEEK(logdate) AS week_num,
, MIN(logdate) AS week_start,
, MAX(logdate) AS week_end,
, SUM(Stats) AS AllStats
FROM stat_table
WHERE group_id = 'XXX'
GROUP
BY person_id
, WEEK(logdate)
) AS week_totals
INNER
JOIN (
SELECT week_num
, MAX(AllStats) AS max_stats
FROM (
SELECT person_id
, WEEK(logdate) AS week_num
, SUM(Stats) as AllStats
FROM stat_table
WHERE group_id = 'XXX'
GROUP
BY person_id
, WEEK(logdate)
) AS week_totals_1
GROUP
BY week_num
) AS week_max GROUP BY person_id;

MySQL and I may just be on speaking terms again.

Special thanks to Blogger for maintaining such wonderful formatting in the queries posted above.

SproutCore - A MVC Framework for Javascript

I've really been enjoying CakePHP more and more, and have just starting delving into the AJAX helper more and implementing that into some new sites. However, there is only so much functionality built into CakePHP, and you'd probably have to integrate one of the other AJAX frameworks out there (like jQuery, etc) to get something more 'Desktop-like'.

But after monitoring Apple's just finished WWDC and hearing about SproutCore, I dropped by the site to see what it's all about. It looks like Charles Jolley has put together a really nice framework that Apple has added to and leveraged for their new Mobile Me service.

From reading the site, though, it seems as though Ruby on Rails is required to get the framework installed. I'm hoping someone will put together a nice and easy way to integrate SproutCore with PHP (and hopefully with CakePHP) so you could leverage both frameworks together.

If anyone does, drop me a line.