photo credit: Urlaub in Valencia (Spanien) - 30.07.2009 via photopin (license)

How to add complex joins to your collection in Magento

I recently had the situation where I had to add a really complex field to a product collection in Magento. The client had a very special way of calculating product prices that depended on:

  • The supplier that delivered the product the cheapest (a product could have more than one supplier).
  • The customer that was logged in.
  • The special discount that applied to the organization the customer was assigned to.

I had the discount stuff up and running, but there was one major thing that still needed to be done: The product collection (on the category detail page) had to be sorted on price, but as I stated above, the price depended on multiple factors.
So how did I solve this?

The underlying problem

To calculate the correct discount and show the correct product price, a very complex module was created with some very complex SQL queries. The query joined 6 tables that kept track of organizations, suppliers, supplier prices and supplier-organization discounts. In the end the query worked like intended if you singled out a product, but if you had a product collection, Magento didn’t look at our magical query. Instead it just did it’s default behavior: it used he price stored in the price-attribute and without any discount applied to it. This was also the value used with sorting, filtering and pagination. Needless to say, that wasn’t the result we were looking for.

A simple solution

My first attempt was doing it the Magento way by utilizing the select()  and joinField()  / joinTable() -methods. This was very complex and hard to read or understand for simple mortals just like me, so I started thing about a more simple solution.
I already had the code that generated the correct SQL-query with all it’s joins that allowed me to calculate a product price by just entering a single product ID. So why couldn’t I just re-use that SQL-query? Some research on Internet showed me of a very nifty method that used Zend_Db_Expr to add a complete column as a single selectable (and sortable or filterable) value to the SQL-query Magento generates. It works like this:

Now the trick here is the last part. By using the columns() -method we can add a column to the SQL-query that in the end generates our product collection. If you look at the expression, you also see the use of the COALESCE -command. This one was also new for me. All it does is: if value 1 is NULL , take value 2, if value 2 is NULL , take value 3, etc. I added this check because sometimes the discount SQL would return NULL  (when no discount was found), and in that case the default price value should be returned.
The only thing that left to do now, is order our product collection by this new column:

And there you have it. I most likely impossible join-hell to add to your collection made simple by adding a custom column to your results.

Visitors give this article an average rating of 3.2 out of 5.

How would you rate this article?

Leave a Reply