Multiple count in ORM is not working

Question

I want to get count of tenancies based on differnet times

    $start_date = $end_date = $end_date1 = new \DateTime('first day of last month');

    $start_date = $start_date->format('Y-m-01 00:00:00');
    $end_date = $end_date->format('Y-m-t 23:59:59');
    $end_date1 = $end_date1->format('Y-m-'. date('d') .' 23:59:59');

    $start_date2 = $end_date2 = new \DateTime('now');
    $start_date2 = $start_date2->format('Y-m-01 00:00:00');
    $end_date2 = $end_date2->format('Y-m-d 23:59:59');

   $tenancyDetails = $this
        ->find()
        ->select([ 
            'total_last_month' => $this->find()->func()->count('Tenancy.id'),
            'count_last_month' => $this->find()->func()->count('Tenancy1.id'),
            'count_curr_month' => $this->find()->func()->count('Tenancy2.id'),
            'Tenancy.created', 'Tenancy1.created', 'Tenancy2.created',
        ])            
        ->leftJoin(['Tenancy1' => 'tenancy'],[
            'Tenancy1.active' => 1,
            'Tenancy1.stage !=' => 1,
            'Tenancy1.company_id' => $id,
        ])
        ->leftJoin(['Tenancy2' => 'tenancy'],[
            'Tenancy2.active' => 1,
            'Tenancy2.stage !=' => 1,
            'Tenancy2.company_id' => $id,
        ])
        ->where([
            'Tenancy.active' => 1,
            'Tenancy.stage !=' => 1,
            'Tenancy.company_id' => $id,
        ])
       ->where(function ($exp, $q) use ($start_date, $end_date) {
            return $exp->between('Tenancy.created', $start_date, $end_date);
        })
       ->where(function ($exp, $q) use ($start_date, $end_date1) {
            return $exp->between('Tenancy1.created', $start_date, $end_date1);
        })
       ->where(function ($exp, $q) use ($start_date2, $end_date2) {
            return $exp->between('Tenancy2.created', $start_date2, $end_date2);
        })->toArray();

Here is what I get. The number of counts are the same. Eventhough the datesare different

[
(int) 0 => object(App\Model\Entity\Tenancy) {

    'total_last_month' => (int) 4590,
    'count_last_month' => (int) 4590,
    'count_curr_month' => (int) 4590,
    'created' => object(Cake\I18n\FrozenTime) {

        'time' => '2016-03-01T10:57:21+00:00',
        'timezone' => 'UTC',
        'fixedNowTime' => false

    },
    'Tenancy1' => [
        'created' => '2016-03-01 10:57:21'
    ],
    'Tenancy2' => [
        'created' => '2016-04-04 10:59:42'
    ],
    '[new]' => false,
    '[accessible]' => [
        '*' => true
    ],
    '[dirty]' => [],
    '[original]' => [],
    '[virtual]' => [],
    '[errors]' => [],
    '[invalid]' => [],
    '[repository]' => 'Tenancy'

}
]

Even though I have written the query this way but still the same problem.

    $query = $tenancies->find();
    $query->select([ 
        'total_last_month' => $query->func()->count('Tenancy.id'),
    ])
    ->where([
        'Tenancy.active' => 1,
        'Tenancy.stage !=' => 1,
        'Tenancy.company_id' => $id,
    ]) 
    ->where(function ($exp) use ($start_date, $end_date) {
        return $exp->between('Tenancy.created', $start_date, $end_date);
    });

    $query->select([ 
        'count_last_month' => $query->func()->count('Tenancy1.id'),
    ])
    ->leftJoin(['Tenancy1' => 'tenancy'],[
        'Tenancy1.company_id' => $id,
    ])
    ->where([
        'Tenancy1.active' => 1,
        'Tenancy1.stage !=' => 1,
    ]) 
    ->where(function ($exp) use ($start_date, $end_date1) {
        return $exp->between('Tenancy1.created', $start_date, $end_date1);
    });

I can see the printed query which is correct.

Jason Joslin · Answer 1 · 2016-04-17T10:08:33.593

After looking at all the examples in the documentation it looks like you need to instantiate the ORM query builder instance before using the func() helpers.

http://book.cakephp.org/3.0/en/orm/query-builder.html#using-sql-functions

In theory $this->func() should work the same if the return is simply a string. but maybe using $this->func() results in a disconnect between the query instance you are trying to build and an unbound new instance?

try:

$tenancyDetails = $this>find();
$tenancyDetails->select([ 
            'total_last_month' => $tenancyDetails->find()->func()->count('Tenancy.id'),
            'count_last_month' => $tenancyDetails->find()->func()->count('Tenancy1.id'),
            'count_curr_month' => $tenancyDetails->find()->func()->count('Tenancy2.id'),
            'Tenancy.created',
            'Tenancy1.created',
            'Tenancy2.created',
        ])......

Edit

I checked the code for the count function in /ORM/query.php file. If no values are set cake will perform the count() query on the call $this->func->count(). The PHP compiler must be reading and therefore executing the count query before the query has been built, meaning before your conditions have been set. That's why you you need to instantiate the class first to stop the count query executing prematurely.

Multiple count in ORM is not working

1 Answers1