Answers.Body: <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>
  
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-04T23:57:45.830Z
Answers.Text: Awesome thank you Brad, will give this a shot!
Answers.UserDisplayName: mcclaskiem
Answers.ContentLicense: CC BY-SA 3.0
Answers.CreationDate: 2017-12-04T22:00:47.843Z
Answers.LastActivityDate: 2017-12-04T22:00:47.843Z
Answers.UserDisplayName: Brad Bell
Body: <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>
  
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-11-24T23:48:51.027Z
Comments.Text: You want _all_ of the variants that currently have a price less than their corresponding "field_originalPrice" value?  Or just a particular one?
Comments.UserDisplayName: Brad Bell
Comments.ContentLicense: CC BY-SA 3.0
Comments.CreationDate: 2017-11-25T15:30:04.247Z
Comments.Text: I'd like to get all the variants and then can group them by their associated product after the fact. Is this possible?
Comments.UserDisplayName: mcclaskiem
Tags: <plugin-craftcommerce><createcommand>
Title: Building a DbCommand to compare two columns on Variants
ThreadText: # 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 username_1, will give this a shot!  
  
  ---
  
  