function to get combination name

[fransjaeger]

Hi

 

I use combinations for all my products and I am tired of prestashops productname on product lists isnt showing default combination name. So i fixed it using this function to get the attribute name.

    public function getAttributeName($id_product,$id_product_attribute){
        $product = new Product($id_product);
        if (!isset($product) || !Validate::isLoadedObject($product))
            return false;

        $groups = array();
        $attributes_groups = $product->getAttributesGroups($this->context->language->id);
        if (is_array($attributes_groups) && $attributes_groups)
            foreach ($attributes_groups as $k => $row)
                if ($id_product_attribute==$row['id_product_attribute'])
                    return $row['attribute_name'];
            return '?__?';
        return false;
    }

This isnt a great way at all to get the combination name

 

Can someone please improve/simplify my code please

 

Thanks in advance

[FullCircles]

You could just grab it directly, I believe this query should be alright for that purpose:

public function getAttributeName($id_product,$id_product_attribute){
    $product = new Product($id_product);
    if (!isset($product) || !Validate::isLoadedObject($product))
        return false;

    if(!empty($id_product_attribute)){
	$attName = Db::getInstance()->executeS('SELECT al.`name` FROM `'._DB_PREFIX_.'product_attribute` pa
		'.Shop::addSqlAssociation('product_attribute', 'pa').'
		'.Product::sqlStock('pa', 'pa').'
		LEFT JOIN `'._DB_PREFIX_.'product_attribute_combination` pac ON (pac.`id_product_attribute` = pa.`id_product_attribute`)
		LEFT JOIN `'._DB_PREFIX_.'attribute_lang` al ON (pac.`id_attribute` = al.`id_attribute`)
		'.Shop::addSqlAssociation('attribute', 'a').'
		WHERE pa.`id_product` = '.(int)$id_product.' AND al.`id_lang` = '.(int)$this->context->language->id.' AND pa.`id_product_attribute` = '.(int)$id_product_attribute.'
		GROUP BY id_product_attribute');
	if($attName)	return $attName[0]['name'];
    }
    return false;
}

Don't have a non live copy to test with however, so apologies if that doesn't

[fransjaeger]

Hi FullCircles

Thanks alot

I tried it and something seems to be wrong in the sql, cause its not returning the name

I also tried to change the sql, but i havent had success yet.

Here is my latest attempt 

    public function getAttributeName($id_product, $id_product_attribute){
    	if ($id_product && $id_product_attribute){
        	$attName = Db::getInstance()->getValue('
				SELECT al.`name` 
				FROM `'._DB_PREFIX_.'product_attribute` pa
					'.Shop::addSqlAssociation('product_attribute', 'pa').'
					'.Product::sqlStock('pa', 'pa').'
					LEFT JOIN `'._DB_PREFIX_.'product_attribute_combination` pac ON (pac.`id_product_attribute` = pa.`id_product_attribute`)
					LEFT JOIN `'._DB_PREFIX_.'attribute` a ON (a.`id_attribute` = pac.`id_attribute`)
					LEFT JOIN `'._DB_PREFIX_.'attribute_group` ag ON (ag.`id_attribute_group` = a.`id_attribute_group`)
					LEFT JOIN `'._DB_PREFIX_.'attribute_lang` al ON (a.`id_attribute` = al.`id_attribute`)
					'.Shop::addSqlAssociation('attribute', 'a').'
					WHERE pa.`id_product` = '.$id_product.'
						AND pa.`id_product_attribute` = '.$id_product_attribute.'
						AND al.`id_lang` = '.$id_lang.'
					GROUP BY id_attribute_group, id_product_attribute');
			if($attName)return $attName;
			return '?__?';
		}
		return false;
    }

Edited December 12, 2014 by michaelhjulskov (see edit history)

[fransjaeger]

I edited above ^

[FullCircles]

Hi Michael,

 

Hmm, can't spot any errors, but it's likely because I tried to make the query a little more concise and cut out some of the joins, must be one needed that I'm not spotting, try using this for the query instead, it's the full one from the original function, just with an amended where clause and only bringing back the attribute name

$sql = 'SELECT al.`name` AS attribute_name 
				FROM `'._DB_PREFIX_.'product_attribute` pa
				'.Shop::addSqlAssociation('product_attribute', 'pa').'
				'.Product::sqlStock('pa', 'pa').'
				LEFT JOIN `'._DB_PREFIX_.'product_attribute_combination` pac ON (pac.`id_product_attribute` = pa.`id_product_attribute`)
				LEFT JOIN `'._DB_PREFIX_.'attribute` a ON (a.`id_attribute` = pac.`id_attribute`)
				LEFT JOIN `'._DB_PREFIX_.'attribute_group` ag ON (ag.`id_attribute_group` = a.`id_attribute_group`)
				LEFT JOIN `'._DB_PREFIX_.'attribute_lang` al ON (a.`id_attribute` = al.`id_attribute`)
				LEFT JOIN `'._DB_PREFIX_.'attribute_group_lang` agl ON (ag.`id_attribute_group` = agl.`id_attribute_group`)
				'.Shop::addSqlAssociation('attribute', 'a').'
				WHERE pa.`id_product` = '.(int)$id_product.'
                                        AND pa.`id_product_attribute` = '.(int)$id_product_attribute.'
					AND al.`id_lang` = '.(int)$this->context->language->id.'
					AND agl.`id_lang` = '.(int)$this->context->language->id.'
				GROUP BY id_attribute_group, id_product_attribute
				ORDER BY ag.`position` ASC, a.`position` ASC, agl.`name` ASC';
$result = Db::getInstance()->executeS($sql);

Edited December 12, 2014 by FullCircles (see edit history)

[fransjaeger]

Hi

 

Thanks. Still not returning the attribute name, for some reason

 

Latest I tried this: (and i double checked that $id_lang, $id_product, $id_product_attribute have correct values)

    public function getAttributeName($id_lang, $id_product, $id_product_attribute){
        if ($id_product && $id_product_attribute){
            $attName = Db::getInstance()->getValue('
                SELECT al.`name` 
                FROM `'._DB_PREFIX_.'product_attribute` pa
                    '.Shop::addSqlAssociation('product_attribute', 'pa').'
                    '.Product::sqlStock('pa', 'pa').'
                    LEFT JOIN `'._DB_PREFIX_.'product_attribute_combination` pac ON (pac.`id_product_attribute` = pa.`id_product_attribute`)
                    LEFT JOIN `'._DB_PREFIX_.'attribute` a ON (a.`id_attribute` = pac.`id_attribute`)
                    LEFT JOIN `'._DB_PREFIX_.'attribute_group` ag ON (ag.`id_attribute_group` = a.`id_attribute_group`)
                    LEFT JOIN `'._DB_PREFIX_.'attribute_lang` al ON (a.`id_attribute` = al.`id_attribute`)
                    LEFT JOIN `'._DB_PREFIX_.'attribute_group_lang` agl ON (ag.`id_attribute_group` = agl.`id_attribute_group`)
                    '.Shop::addSqlAssociation('attribute', 'a').'
                WHERE pa.`id_product` = '.(int)$id_product.'
                    AND pa.`id_product_attribute` = '.(int)$id_product_attribute.'
                    AND al.`id_lang` = '.$id_lang.'
                    AND agl.`id_lang` = '.$id_lang.'
                GROUP BY id_attribute_group, id_product_attribute
                ORDER BY ag.`position` ASC, a.`position` ASC, agl.`name` ASC');
            if($attName)return $attName;
            return '?__?';
        }
        return false;
    }

Edited December 15, 2014 by michaelhjulskov (see edit history)

[FullCircles]

Think I might have to accept defeat then, I've no idea why those queries wouldn't be working, especially the one that was just a direct duplicate of the sql from the function

[fransjaeger]

Thanks for trying to help me
Maybe someone else can see whats wrong in these queries

[luisfer91]

Maybe it is late to answer, but for what I see the only problem in this query is that $id_lang has not been set, if you set it at the top:

public function getAttributeName($id_product, $id_product_attribute){
$id_lang = Context::getContext()->language->id;
// .... continue the function

It should work ok, I've tested and working.

Edited June 18, 2015 by luisfer91 (see edit history)

[amir hossein]

Hey guys , this is late to answer but this is solution:

just use this function to get combination name

    public function getAttributeName($id_lang,$id_product_attribute){
        
        return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
			SELECT al.*
			FROM '._DB_PREFIX_.'product_attribute_combination pac
			JOIN '._DB_PREFIX_.'attribute_lang al ON (pac.id_attribute = al.id_attribute AND al.id_lang='.(int) $idLang.')
			WHERE pac.id_product_attribute='.(int) $id_product_attribute);

    }

 

 

 

[amir hossein]

5 hours ago, ndiaga said:

You have an error in your code:  you  declare  $id_lang   and  calling  $idLang   .

 

    public function getAttributeName($id_lang,$id_product_attribute){
        
        return Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
			SELECT al.*
			FROM '._DB_PREFIX_.'product_attribute_combination pac
			JOIN '._DB_PREFIX_.'attribute_lang al ON (pac.id_attribute = al.id_attribute AND al.id_lang='.(int) $id_lang.')
			WHERE pac.id_product_attribute='.(int) $id_product_attribute);

    }

fixed

 

thanks