title: Grails criteria or HQL for MySQL query with left join and date functions
body: <p>I have a MySQL database where sales of certain items are stored. I would like to compare the sales of two subsequent days in the past, e.g. 10 days in the past and 9 days in the past. I worked out the MySQL query to do so:</p>
  
  <pre><code>select s1.sales - s2.sales from ProductSales as s1
  left join ProductSales as s2 on s2.date &gt;= DATE(DATE_ADD(Now(), INTERVAL '-10' DAY)) and s2.date &lt;= DATE(DATE_ADD(Now(), INTERVAL '-9' DAY)) and s2.product_id = s1.product_id
  where s1.date &gt;= DATE(DATE_ADD(Now(), INTERVAL '-9' DAY));
  </code></pre>
  
  <p>I want to do this programmatically in a Grails application and I need to transform this query into a criterion or HQL query. How would I do this?</p>
  
  <p><strong>Update</strong>: I tried like this:</p>
  
  <pre><code>def date1 = new Date() - 2
  def date2 = new Date() - 1
  def sales = ProductSales.executeQuery( """
      select s1.sales - s2.sales from ProductSales s1
      left join ProductSales s2
      where s1.date &gt;= :date2
      and s2.date &gt;= :date1 and s2.date &lt; :date2 and s2.product_id = s1.product_id""",
  [ date1: date1, date2: date2 ] )
  </code></pre>
  
  <p>Unfortunately, this gives me:</p>
  
  <pre><code>ERROR hql.ast.ErrorCounter -  Path expected for join!
  ERROR hql.ast.ErrorCounter -  Invalid path: 's2.sales'
  ERROR hql.ast.ErrorCounter -  right-hand operand of a binary operator was null
  ERROR hql.ast.ErrorCounter -  Invalid path: 's2.date'
  ERROR hql.ast.ErrorCounter - &lt;AST&gt;:0:0: unexpected end of subtree
  ERROR hql.ast.ErrorCounter -  left-hand operand of a binary operator was null
  ERROR hql.ast.ErrorCounter -  Invalid path: 's2.date'
  ERROR hql.ast.ErrorCounter - &lt;AST&gt;:0:0: unexpected end of subtree
  ERROR hql.ast.ErrorCounter -  left-hand operand of a binary operator was null
  ERROR hql.ast.ErrorCounter -  Invalid path: 's2.product_id'
  ERROR hql.ast.ErrorCounter - &lt;AST&gt;:0:0: unexpected end of subtree
  ERROR hql.ast.ErrorCounter -  left-hand operand of a binary operator was null
  ERROR logging.impl.SLF4JLog - QuerySyntaxException occurred when processing request: [GET] /app/dashboard
  Path expected for join!
  </code></pre>
  
  <p><strong>Update 2</strong>: As I cannot answer my own question, I add my response here:</p>
  
  <p>Apparently this works as it should:</p>
  
  <pre><code>def now = new Date()
  def date1 = now - 2
  def date2 = now - 1
  def sales = ProductSales.executeQuery( """
      select s1.owner, s1.sales - s2.sales from ProductSales s1, ProductSales s2
      where s1.date &gt;= :date2 and s1.date &lt; :now 
      and s2.date &gt;= :date1 and s2.date &lt; :date2 and s2.product = s1.product""",
      [ date1: date1, date2: date2, now: now ]
      )
  </code></pre>
  
  <p>I'm not entirely sure what's the difference between a <code>left join</code> and a <code>select from T1, T2</code>, though. Appart from that, I added an upper bound (<code>now</code>) for the more recent entry and changed the <code>product_id</code> to <code>product</code>.</p>