zen cart SQL - Select products and order by price

SOLVED

See my answer below.

BACKGROUND

Anyone familiar with zen cart will know that it comes complete with the worlds most pointless product sort option aka the alpha drop down sorter.

Essentially what this does is allow you to select products by the letter/number they start with. Well, the starting letter of an item is rarely a useful criteria when I am shopping so I'm trying to create a more useful product sorter which will sort on date added, price and product name. and then filter based on attributes category.

PROBLEM

So I have managed to coerce the sorter to do my own query, which is working on everything except price.

Here is the SQL produced by my current set up for the price drop down:

SELECT DISTINCT p.products_id, p.products_type, p.master_categories_id
               ,p.manufacturers_id, p.products_price, p.products_tax_class_id
               ,pd.products_description
               ,IF(s.status = 1, s.specials_new_products_price, NULL)
                  AS specials_new_products_price
               ,IF(s.status =1, s.specials_new_products_price, p.products_price)
                  AS final_price
               ,p.products_sort_order
               ,p.product_is_call
               ,p.product_is_always_free_shipping
               ,p.products_qty_box_status

  FROM products p
  LEFT JOIN specials s on p.products_id = s.products_id
  LEFT JOIN products_description pd on p.products_id = pd.products_id
  JOIN products_to_categories p2c on p.products_id = p2c.products_id

  WHERE p.products_status = 1
    and pd.language_id = '1'
    and p2c.categories_id = '1'

  GROUP BY p.products_id
  ORDER BY final_price ASC

As you can see I am trying to sort based on final price which is an alias for either the normal price or the special price (if set). This works fine through phpmyadmin. Unfortunately this isn't working through zen cart, I get the php error:

PHP Fatal error: 1054:Unknown column 'final_price' in 'order clause' :: SELECT p.products_id, p.products_price_sorter, p.master_categories_id, p.manufacturers_id FROM products p LEFT JOIN specials s on p.products_id = s.products_id LEFT JOIN products_description pd on p.products_id = pd.products_id JOIN products_to_categories p2c on p.products_id = p2c.products_id WHERE p.products_status = 1\r\n and pd.language_id = '1'\r\n and p2c.categories_id = '1' GROUP BY p.products_id ORDER BY final_price ASC in /var/www/includes/classes/db/mysql/query_factory.php on line 101

So I looked at line 101 and it refers to an error handling method in the database abstraction class. Does anyone have any ideas what is going wrong here and how I might go about solving it?

Solution

Thank you to all who looked at this. I managed to solve this. Not sure why but I noticed from my error message that the alias was not making it through to the db abstraction class. However, somewhere along the way it is producing p.products_price_sorter. So i managed to sort on price by using :

ORDER BY p.products_price_sorter

Hope this helps someone

UPDATE

After I spent several days getting this to work and play nicely, this plugin was suggested by Scott Wilson: http://www.zen-cart.com/downloads.php?do=file&id=1612 So if you are reading this it may be worth checking out - it allows for custom sorting of your products on advanced search and category pages.