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

<pre><code>$results = craft()-&gt;db-&gt;createCommand()
    -&gt;select('v.*')
    -&gt;from('commerce_variants v')
    -&gt;join('content c', 'v.id = c.elementId')
    -&gt;where('v.price &lt; c.field_originalPrice")
    -&gt;queryAll();
</code></pre>

CC BY-SA 3.0
2017-12-04T23:57:45.830Z
Awesome thank you Brad, will give this a shot!
mcclaskiem
CC BY-SA 3.0
2017-12-04T22:00:47.843Z
2017-12-04T22:00:47.843Z
Brad Bell
<p>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.</p>

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

return $query;
</code></pre>

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

<p>Any help would be awesome! Thanks!</p>

CC BY-SA 3.0
2017-11-24T23:48:51.027Z
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
CC BY-SA 3.0
2017-11-25T15:30:04.247Z
I'd like to get all the variants and then can group them by their associated product after the fact. Is this possible?
mcclaskiem
<plugin-craftcommerce><createcommand>
Building a DbCommand to compare two columns on Variants
# Building a DbCommand to compare two columns on Variants

(Asked by: username_0 on 2017-11-24)

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!

**Tags:** plugin-craftcommerce, createcommand

> **Comments:**
> **username_1**: You want _all_ of the variants that currently have a price less than their corresponding "field_originalPrice" value?  Or just a particular one?  
> **username_0**: I'd like to get all the variants and then can group them by their associated product after the fact. Is this possible?  

---

## 1 answers

### Answer by username_1 on 2017-12-04. Score: 2 | Accepted answer

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();

> **Comments:**
> **username_0**: Awesome thank you Brad, will give this a shot!  

---

