retrieve average rating on a join

Good Day 

I need help with result. I have a model that returns all business branches. 
I would like to join an additional table called Ratings and return the average rating for each company.

Does anyone have a simple script i can use. What's the best practice to achieve this.

Thanks in advance

PHP Code:

function getAllBranches()
    {
        $db      = \Config\Database::connect();
        $builder = $db->table('branches');
        $builder->join('companies', 'companies.id = branches.companyId');
        $builder->join('businessprofile', 'businessprofile.companyId = branches.companyId'); 
        
        $builder->where('paymentstatus', 'active');
        
        $query = $builder->get();
        
        return $query->getResult();
    } 


This should help you out.

CodeIgniter 4 Users Guide - Query Builder Class - selectAvg

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

This should help you out.

CodeIgniter 4 Users Guide - Query Builder Class - selectAvg

When i try adding selectAvg it returns only the rating field instead of the complete dataSet. 

Example of the code is below 

PHP Code:

function getAllBranches()
    {
        $db      = \Config\Database::connect();
        $builder = $db->table('branches');
        $builder->join('companies', 'companies.id = branches.companyId','left');
        $builder->join('businessprofile', 'businessprofile.companyId = branches.companyId'); 
        $builder->join('rating', 'rating.companyId = branches.companyId'); 
        $builder->selectAvg('rating'); 
    
        $builder->where('paymentstatus', 'active');
        $builder->groupBy(['name', 'location','category']);
        $query = $builder->get();
        
        return $query->getResult();
    } 


---

Thank you for the input. You have pointed me to the right direction. 

All I had to do was Add select('*) before selectAvg. Works as expected.

See code below 

PHP Code:

function getAllBranches()
    {
        $db      = \Config\Database::connect();
        $builder = $db->table('branches');
        $builder->join('companies', 'companies.id = branches.companyId','left');
        $builder->join('businessprofile', 'businessprofile.companyId = branches.companyId'); 
        $builder->join('rating', 'rating.companyId = branches.companyId'); 
        $builder->select('*'); 
        $builder->selectAvg('rating'); 
    
        $builder->where('paymentstatus', 'active');
        $builder->groupBy(['name', 'location','category']);
        $query = $builder->get();
        
        return $query->getResult();
    }