0

When creating a Catalog Rule, it currently includes products that have tier prices.

I would like to do the direct opposite of the following code

$select->joinInner(
     array('product_tierprice' => 'catalog_product_entity_tier_price'),
     'product_tierprice.entity_id=rp.product_id',
     array()
 );

The above code will limit Catalog Rules to just products with tier prices and it works. Attempting to do the opposite causes errors.

What I want to do is rp.product_id's that are not present in catalog_product_entity_tier_price. Something along the lines of the following, but actually works

$select->joinLeft(
    array('product_tierprice' => 'catalog_product_entity_tier_price'),
    'product_tierprice.entity_id=rp.product_id',
     array()
);
$select->where('rp.product_id NOT IN (product_tierprice.entity_id)');
0

With indexing enabled, I used 'catalog_product_index_price', selecting rows where Tier_Price column was null for that particular website.

$select->joinInner(
    array('pt' => 'catalog_product_index_price'),
    'pt.entity_id=rp.product_id AND pt.tier_price IS NULL AND pt.website_id='.$websiteId,
    array('pt.customer_group_id')           
);
$select->where('pt.customer_group_id IN(rp.customer_group_id)');

This can also be changed to remove the WHERE clause to be

$select->joinInner(
    array('pt' => 'catalog_product_index_price'),
    'pt.entity_id=rp.product_id AND pt.customer_group_id IN(rp.customer_group_id) AND pt.tier_price IS NULL AND pt.website_id='.$websiteId,
    array()         
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.