1

I am trying to compare two fields located on Variants in Commerce. One is the price field and then I also have an original price field which is a custom field. I want to compare and get all variants where the price is less than the original price.

$query = craft()->db->createCommand()
            ->from('commerce_variants')
            ->where("price < field_originalPrice")
            ->queryAll();

return $query;

This is what I have so far but could be way off on this since that custom field is actually on the content table.

Any help would be awesome! Thanks!

  • 1
    You want all of the variants that currently have a price less than their corresponding "field_originalPrice" value? Or just a particular one? – Brad Bell Nov 24 '17 at 23:48
  • I'd like to get all the variants and then can group them by their associated product after the fact. Is this possible? – mcclaskiem Nov 25 '17 at 15:30
2

Completely untested, but this should return all variants whose price column's value is less than your custom field's originalPrice value.

$results = craft()->db->createCommand()
    ->select('v.*')
    ->from('commerce_variants v')
    ->join('content c', 'v.id = c.elementId')
    ->where('v.price < c.field_originalPrice")
    ->queryAll();
| improve this answer | |
  • Awesome thank you Brad, will give this a shot! – mcclaskiem Dec 4 '17 at 23:57

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.