Opencart sort by manufacturer in Category pages

Question

Ok, I would like to add one more sort variable on category pages, that is to sort by Manufacturer. Running on 1.5.3.1 and have tried this: 1. catalog/controller/product/category.php added the following:

$this->data['sorts'][] = array(
            'text'  => $this->language->get('text_manufacturer_asc'),
            'value' => 'manufacturer-ASC',
            'href'  => $this->url->link('product/category', 'path=' . $this->request->get['path'] . '&sort=manufacturer&order=ASC' . $url)
        );

$this->data['sorts'][] = array(
                'text'  => $this->language->get('text_manufacturer_desc'),
                'value' => 'manufacturer-DESC',
                'href'  => $this->url->link('product/category', 'path=' . $this->request->get['path'] . '&sort=manufacturer&order=DESC' . $url)
            );

1. catalog/language/*/product/category.php added:

   $_['text_manufacturer_asc'] = '▲ -Proizvođaču- ▲'; $_['text_manufacturer_desc'] = '▼ -Proizvođaču- ▼';

2. catalog/model/catalog/product.php before: 'p.date_added' I inserted a line:

   'manufacturer'

and few lines below I changed the block with the following one:

    if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
    if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
        $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
    } elseif ($data['sort'] == 'p.price') {
        $sql .= " ORDER BY (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)";
    } elseif ($data['sort'] == 'manufacturer') {
        $sql .= " SELECT * FROM". DB_PREFIX . "product p ORDER BY manufacturer";
    } else {
        $sql .= " ORDER BY " . $data['sort'];
    }
} else {
    $sql .= " ORDER BY p.sort_order";   
}

But it is not working. Error says:

PHP Notice:  Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * FROMproduct p ORDER BY manufacturer ASC,...

and another one I have found:

PHP Notice:  Error: Unknown column 'special' in 'order clause'<br />Error No: 1054<br />SELECT p.product_id, (SELECT AVG(rating) AS total FROM review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id)...

Can anybody help?

Answer 1

You are almost there but You will have to modify it a little bit more.

First, You really do not want to order by manufacturer ID stored in product.manufacturer_id but by manufacturer name. In this case we need to modify the query in catalog\model\catalog\product.php model in getProducts method - find this line:

$sql .= " LEFT JOIN " . DB_PREFIX . "product_description pd ON (p.product_id = pd.product_id) LEFT JOIN " . DB_PREFIX . "product_to_store p2s ON (p.product_id = p2s.product_id) WHERE pd.language_id = '" . (int)$this->config->get('config_language_id') . "' AND p.status = '1' AND p.date_available <= NOW() AND p2s.store_id = '" . (int)$this->config->get('config_store_id') . "'";

(should be line 89 in OC 1.5.5.1) and before it add:

$sql .= " LEFT JOIN " . DB_PREFIX . "manufacturer m ON p.manufacturer_id = m.manufacturer_id";

Also You'll need to allow manufacturer as a sorting option:

$sort_data = array(
    'pd.name',
    'p.model',
    'p.quantity',
    'p.price',
    'rating',
    'p.sort_order',
    'p.date_added',
    'manufacturer', // <== this is added
);  

Now in the sorting section add sorting by manufacturer name:

if (isset($data['sort']) && in_array($data['sort'], $sort_data)) {
    if ($data['sort'] == 'pd.name' || $data['sort'] == 'p.model') {
        $sql .= " ORDER BY LCASE(" . $data['sort'] . ")";
    } elseif ($data['sort'] == 'p.price') {
        $sql .= " ORDER BY (CASE WHEN special IS NOT NULL THEN special WHEN discount IS NOT NULL THEN discount ELSE p.price END)";
    } elseif ($data['sort'] == 'manufacturer') { // <== this is added
        $sql .= " ORDER BY m.name";              // <== this is added
    } else {
        $sql .= " ORDER BY " . $data['sort'];
    }
} else {
    $sql .= " ORDER BY p.sort_order";   
}

And that's it.