Jump to content

Trying to innerJoin two tables for extra check on pricesDrop-items


Recommended Posts

Hello Prestas, 

we extended the specific price rules functionality with an option "show_discount_front". 

Now on the sales-page I want to show items only with "show_discount_front = 1". 
Inside the SpecificPrice.php I'm trying to update the function getProductIdByDate, which get's called by "Product::getPricesDrop":

        $results = Db::getInstance(_PS_USE_SQL_SLAVE_)->executeS('
			SELECT sp.`id_product`, sp.`id_product_attribute`, spr.`id_specific_price_rule`
			FROM `' . _DB_PREFIX_ . 'specific_price` AS sp
			INNER JOIN `' . _DB_PREFIX_ . 'specific_price_rule` AS spr ON (sp.id_specific_price_rule=spr.id_specific_price_rule)
			WHERE	sp.id_shop ' . self::formatIntInQuery(0, $id_shop) . ' AND
					sp.id_currency ' . self::formatIntInQuery(0, $id_currency) . ' AND
					sp.id_country ' . self::formatIntInQuery(0, $id_country) . ' AND
					sp.id_group ' . self::formatIntInQuery(0, $id_group) . ' AND
					sp.from_quantity = 1 AND
					spr.show_discount_front = 1 AND
					sp.reduction > 0
		' . $query_extra);

If I var_dump the $results, I just get an "false". 

Anyone an idea what I'm doing wrong? 

Thanks in advance for every suggestion!
Cheers, Sebastian

Link to comment
Share on other sites

I found the problem. It was inside $query_extra. 

The newly joined table also has the columns "from" and "to".
Above that query there is the function computeExtraConditions(), where I added as last parameter $tablePrefix = '', and changed this at the end: 

        $query_extra .= ' AND ('.$tablePrefix.'`from` = \'0000-00-00 00:00:00\' OR \''.$beginning.'\' >= '.$tablePrefix.'`from`)'
                       .' AND ('.$tablePrefix.'`to` = \'0000-00-00 00:00:00\' OR \''.$ending.'\' <= '.$tablePrefix.'`to`)';

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...