Opencart 2 - Echo Filter ID/Description in Category View

Question

I'm working on a Opencart 2 - Project and need to echo the Filter IDs for each Product seperatly in Category View (e.g. as additional Classes for the Product-Thumbs, to apply different Styles to vegan/vegetarian food).

I've been breaking my fingers for hours now, modyfing the Controller-, Model-, and View-Files, but can't get to a solution... After many (in hindsight stupid) attempts, i finally got stuck here:

In my catalog/view/theme/default/template/product/category.tpl i echo $product['filter_id'] for each thumb:

<?php foreach ($products as $number=>$product) { ?>
<div class="product-thumb">
//...
<span class="filters"><?php echo $product['filter_id']; ?></span>
//..
</div>
<?php } ?>

To get that Variable, i added it to product-data array in catalog/controller/product/category.php around line 215:

$data['products'][] = array(
                    'product_id'  => $result['product_id'],
                    'thumb'       => $image,
                    'name'        => $result['name'],
                    'description' => (html_entity_decode($result['description'], ENT_QUOTES, 'UTF-8')),
                    'price'       => $price,
                    'special'     => $special,
                    'tax'         => $tax,
                    'rating'      => $result['rating'],
                    'href'        => $this->url->link('product/product', 'path=' . $this->request->get['path'] . '&product_id=' . $result['product_id'] . $url),
                    'filter_id'   => $result['filter_id']
                );

...and modified getProducts() method (as this is the associated method called above to generate $result) in catalog/model/catalog/product.php around line 60 by adding the last SQL-Statement in Row 3:

public function getProducts($data = array()) {
        $sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special,
 (SELECT pf.filter_id FROM " . DB_PREFIX . "product_filter pf, " . DB_PREFIX . "product p WHERE pf.product_id = p.product_id) AS filter_id";
        //...

Unfortunately, this throws an 1242-Error: Subquery returns more than 1 row So I dug out Can I concatenate multiple MySQL rows into one field? and tried this:

$sql = "SELECT p.product_id, (SELECT AVG(rating) AS total FROM " . DB_PREFIX . "review r1 WHERE r1.product_id = p.product_id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT price FROM " . DB_PREFIX . "product_discount pd2 WHERE pd2.product_id = p.product_id AND pd2.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND pd2.quantity = '1' AND ((pd2.date_start = '0000-00-00' OR pd2.date_start < NOW()) AND (pd2.date_end = '0000-00-00' OR pd2.date_end > NOW())) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM " . DB_PREFIX . "product_special ps WHERE ps.product_id = p.product_id AND ps.customer_group_id = '" . (int)$this->config->get('config_customer_group_id') . "' AND ((ps.date_start = '0000-00-00' OR ps.date_start < NOW()) AND (ps.date_end = '0000-00-00' OR ps.date_end > NOW())) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, 
(SELECT GROUP_CONCAT(pf.filter_id SEPARATOR ', ') FROM " . DB_PREFIX . "product_filter pf, " . DB_PREFIX . "product p WHERE pf.product_id = p.product_id) AS filter_id";

As this didn't help either, i tried the same SQL-query (de-php'ized) in PHPMyAdmin, and it resulted in an empty result. The first Query went fine.

Maybe I'm doing it all the wrong way and it could be achieved much easier... I simply want to show all Filter_IDs related to a Product

EDIT: Tried Abdo Abel's way, got this in my Code:

catalog/model/catalog/product.php inside Class ModelCatalogProduct:

    public function getAllProductFilters($product_id) {
   $sql = "SELECT pf.filter_id FROM " . DB_PREFIX . "product_filter pf WHERE pf.product_id = '" . $product_id . "'";
    $query = $this->db->query($sql);

    if ($query->num_rows) {
        $filter_ids = "";
        foreach ($query->rows as $result) {
                $filter_ids .= $result['filter_id'].', ';
            }
        return $filter_ids;
    }
    else {
        return "no filter";
    }

}

In catalog/controller/product/category.php:

$data['products'][] = array(
                    'product_id'  => $result['product_id'],
//...
                    'filter_id'   => $this->model_catalog_product->getAllProductFilters($result['product_id'])
                );

What's really weird is that it even won't show the text "no filter" either I added in else-statement. Unfortunately, I'm doing this on my clients' webspace and I hardly got any chance to debug that normally...

EDIT 2: Changed the SQL above to suggested, still no effect... I wonder if something is wrong with my setup - I also edited the $product['description'] not to show those three dots on the Category-Page (see above, second code segment), but they're still there...

EDIT 3: OK, I'm giving up now... I just edited the $product['description'] to display the $price - still my template prints the decription (!!!)

...
                    'name'        => $result['name'],
                    'description' => $price,
                    'price'       => $price,
...

EDIT 4: Just before I was clear to wipe my installation and start over new, I got a hint from Miwisoft-Support, which everybody should know, who's using OC2 via Mijoshop in Joomla: After modifying Controller/Model-Files, head over to Admin Panel "Extensions" => "Modification" and hit the Refresh-Button in the top right corner. Mijoshop is using a copy of the original files after applying own modifications, so the changes in OC's root couldn't take effect. BTW got an PHP error now, so back to work and get this going ;) I'll keep you updated!

Last EDIT: works :) could have saved a bunch of time, if i knew this before...

Answer 1

change this tiny part in the query

(SELECT GROUP_CONCAT(pf.filter_id SEPARATOR ', ') FROM " . DB_PREFIX . "product_filter pf, " . DB_PREFIX . "product p WHERE pf.product_id = p.product_id) AS filter_id

to

(SELECT GROUP_CONCAT(pf.filter_id SEPARATOR ', ') FROM " . DB_PREFIX . "product_filter pf WHERE pf.product_id = p.product_id GROUP BY pf.product_id) AS filter_id

Edit

Since you have some problem with group_concat (which I don't recognize actually), here is another way of doing what you want:

(1) Create a separate model function that returns filters of a product given it's ID, the most suitable place for this function is to be in the file mode/catalog/product.php @ class ModelCatalogProduct, assume the name of that function is going to be getAllProductFilters, so the function implementation will be something like that:

public function getAllProductFilters($product_id)
{
    $sql = "here place a sql that returns a list of the filter ids for a given product";
    $res = $this->db->query($sql);
    // if the associated product has no filters, return an empty string
    if($res->num_rows == 0)
        return "";
    // else, you should make a loop that concatenates the values of what ever column you want in some variable
    $concatenated_values = "";
    // and finally return it !
    return $concatenated_values;
}

(2) Now, go to the file catalog/controller/product/category.php
and change this line:
'filter_id' => $result['filter_id']
to
'filter_id' => $this->model_catalog_product->getAllProductFilters($result['product_id'])

Answer 2

Just before I was clear to wipe my installation and start over new, I got a hint from Miwisoft-Support, which everybody should know, who's using OC2 via Mijoshop in Joomla: After modifying Controller/Model-Files, head over to Mijoshop Admin Panel, "Extensions" => "Modification" and hit the Refresh-Button in the top right corner. Mijoshop is using a copy of the original M-, C-files after applying own modifications, so the changes in OC's root couldn't take effect.