CODEWITHSUNDEEP
cakephp-3.x
Bird87 ZA
Bird87 ZA

Reputation: 2160

Get sum of column in associated table

SETUP

I have the following DB setup:

// Contacts
,----,--------,---------,----------,
| id | name   | surname | is_agent |
|----|--------|---------|----------|
| 1  | Jhon   | Doe     | Yes      |
'----'--------'---------'----------'

// ContactAttributes
,----,------------------,
| id | own_transport    |
|----|------------------|
| 1  | Yes              |
'----'------------------'

// Reviews
,----,------------,-------------,--------,
| id | contact_id | reviewed_by | rating |
|----|------------|-------------|--------|
| 1  | 1          | 7           | 5      |
| 2  | 1          | 5           | 3      |
| 3  | 1          | 4           | 4      |
'----'------------'-------------'--------'

Contacts have a one to one relationship ContactAttributes and a one to many with Reviews. Reviewed by also links back to Contacts, but only to get the name and surname.

WHAT DO I WANT

I want to get the sum of ratings for a contact. So if I pull contact 1, I want to see review_total as 12.

WHAT HAVE I TRIED

From the docs I can see that I need to build my query like this:

$query = $contactsTable->find();
$query->select(['review_sum' => $query->func()->sum('Reviews.rating')])
    ->where([
        'Contacts.is_agent' => 'no',
        'ContactAttributes.own_transport' => 'Yes',
    ])
    ->contain(['ContactAttributes', 'Reviews'])
    ->first();

PROBLEM

I'm getting this error when I run the above:

Error: SQLSTATE[42P01]: Undefined table: 7 ERROR: missing FROM-clause entry for table "reviews" LINE 1: SELECT (SUM(Reviews.rating)) AS "review_sum" FROM contacts C... ^

And this is the query it tries to run:

SELECT (SUM(Reviews.rating)) AS "review_sum" FROM contacts Contacts LEFT JOIN contact_attributes ContactAttributes ON Contacts.id = (ContactAttributes.id) WHERE (Contacts.is_agent = :c0 AND ContactAttributes.own_transport = :c1) LIMIT 1

QUESTION

How can I solve this? What am I doing wrong or is there an easier way to do this?

EDIT 1

I have got it working like this but there has to be a more CakePHP way.

$contactsTable->find()
    ->select([
        'Contacts.id',
        'Contacts.firstname',
        'Users.username',
        'Contacts.lastname',
        'Contacts.id_number',
        'Contacts.coordinates',
        'review_sum' => 'SUM(reviews.rating)'
    ])
    ->leftJoin(['Reviews' => 'reviews'], 'Reviews.contact_id = Contacts.id')
    ->leftJoin(['Users' => 'users'], 'Users.id = Contacts.id')
    ->leftJoin(['ContactAttributes' => 'contact_attributes'], 'ContactAttributes.id = Contacts.id')
    ->where([
        'Contacts.is_agent' => 'no',
        'ContactAttributes.own_transport' => $job->own_transport
    ])
    ->orderDesc('review_sum')
    ->group('Users.username')
    ->group('Contacts.id')
    ->having(['SUM(Reviews.rating) IS NOT NULL']);

Upvotes: 0

Views: 195

Answers (3)

arilia
arilia

Reputation: 9398

Since you have a belongsToMany relationship you have to put the function inside the Contain call

$query
    ->where([
        'Contacts.is_agent' => 'no',
        'ContactAttributes.own_transport' => 'Yes',
    ])
    ->contain([
        'ContactAttributes', 
        'Reviews' => function($q) {
            $q->select([
                'review_sum' => $query->func()->sum('Reviews.rating') 
                ])
            ->group(['contact_id'])
    }])
->first();

Upvotes: 2

Chris H
Chris H

Reputation: 972

It looks like at the point when you're doing the sum, $query is only partially initialized with one table. Try replacing the first line with this. 

$query = $contactsTable->find()->contain(['ContactAttributes', 'Reviews']);

and remove the contain line at the end.

Upvotes: 0

Chris H
Chris H

Reputation: 972

I think your query is missing a JOIN. You have Contacts and ContactAttributes but not Reviews. You need to add something like this.

LEFT JOIN Reviews ON Contacts.id = Reviews.contact_id

Upvotes: 0

Related Questions