+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: retrieve average rating on a join (/showthread.php?tid=81039)
retrieve average rating on a join - remesses_thegreat - 01-18-2022
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();
}
RE: retrieve average rating on a join - InsiteFX - 01-18-2022
This should help you out.
CodeIgniter 4 Users Guide - Query Builder Class - selectAvg
RE: retrieve average rating on a join - remesses_thegreat - 01-18-2022
(01-18-2022, 03:41 AM)InsiteFX Wrote: 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();
}