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()
            ->where("price < field_originalPrice")

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
    Commented Nov 24, 2017 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
    Commented Nov 25, 2017 at 15:30

1 Answer 1


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()
    ->from('commerce_variants v')
    ->join('content c', 'v.id = c.elementId')
    ->where('v.price < c.field_originalPrice")
  • Awesome thank you Brad, will give this a shot!
    – mcclaskiem
    Commented Dec 4, 2017 at 23:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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