1

I have a pretty complex (fancy?) query, which is working in pure sql but I need to translate it to be used with cakephp 3.0's orm.

This is the query:

select user_id, count(id)/period_diff(date_format(now(), '%Y%m'),
date_format(sent, '%Y%m')) AS average from invoices 
WHERE sent != '0000-00-00' GROUP BY user_id

And this is what I was trying:

$monthly = $this->find()
  ->select(function ($query) {
    return [
      'user_id',
      'average' => $query->func()->extract(
        'COUNT(Invoices.id)/PERIOD_DIFF(date_format(now(), "%Y%m"),
         date_format(Invoices.sent, "%Y%m"))'
      )
    ];
  })
  ->where([
    "Invoices.sent !=" => "0000-00-00",
  ])
  ->group('user_id');

The problem is, the extract method (which I'm not sure it's what I actually need) is available since CakePHP 3.1, but I'm using 3.0.

Is it the correct method? Can I make it work with 3.0 somehow, or is there something else I can use?

Oops D'oh
  • 941
  • 1
  • 15
  • 34
ToX 82
  • 1,064
  • 12
  • 35
  • Is upgrading to 3.1 out of the question? Incompatibilities are pretty minimal... – Greg Schmidt Jan 06 '16 at 06:04
  • Your raw SQL query doesn't use `EXTRACT`, so why would you try to use this with the query builder? – ndm Jan 06 '16 at 06:44
  • I can't upgrade to 3.1 because of (http://stackoverflow.com/questions/32757870/respond-as-xml-not-working-since-cakephp-3-1)[another problem] that I had previously. Anyway, I have managed to make it work with a raw expression: `$query->newExpr()->add('.......')`. – ToX 82 Jan 07 '16 at 08:50

0 Answers0