Laravel group by, with relationship in different tables

Question

I am using laravel eloquent, trying to fetch information contained in different tables and compare them.

My tables are

tbl_checks
    id,check_id, person_id //person_id is the foreign key

tbl_persons
   id, category,

I would like to fetch all people in table tbl_checks and group the data by person category tbl_persons.category.

In normal SQL statement it's like:

select from tbl_checks group by tbl_person.category where the tbl_persons.id is contained in tbl_checks.person_id

In my models I have:

class PersonsModel extends Model {
    //connect to the next database
    protected $connection = 'inspection';
    protected $table = 'tbl_trucks';

    //relation with tbl_checks

    public function checks(){
        return $this->hasMany('App\TblChecks','person_id','id');
    }
}

How can I use the ELoquent model, and not the Db facade? To make it abit clear. Am trying to find all persons who are in the table_checks and ignore persons id who are not in tbl_checks

@Webinion The answer youve produced doesnt have an extra query to the related table, Am trying to get all persons contained in tbl_checks and ignore those that are not there and then get totals of those who are contained and group by person.category — Geoff, Apr 06 '18 at 16:59

score 21 · Answer 1 · answered Apr 06 '18 at 17:13

21

Assuming you have a person relationship in your TblChecks model:

TblChecks::with('person')->get()->groupBy('person.category');

answered Apr 06 '18 at 17:13

Jonas Staudenmeir

24,815
6
63
109

now am getting an error Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column – Geoff Apr 06 '18 at 17:50
now am getting an error Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column – Geoff Apr 06 '18 at 17:51
Please post the whole error message. – Jonas Staudenmeir Apr 06 '18 at 17:57
Try setting the value of `strict` to `false` in `config/database.php` – DevK Apr 06 '18 at 17:58
now after setting to false am getting a new error of Missing argument 1 for Illuminate\Support\Collection::get() – Geoff Apr 06 '18 at 18:08
Please post the `person` relationship. Did you exactly copy my code? – Jonas Staudenmeir Apr 06 '18 at 18:10
Thank you so much, you save me ! – Dessauges Antoine May 08 '18 at 11:29
This is the real solution. – Natnael Ghirma Jun 28 '21 at 10:42

score -1 · Answer 2 · answered Apr 06 '18 at 19:03

-1

If you are using Laravel >= 5.3, use Eloquent::has (Querying Relationship Existence):

$persons = PersonsModel::has('checks')->get();

For Laravel < 5.3, use Where Exists Clauses:

$persons = PersonsModel::whereExists(function ($q) {
    $q->select(DB::raw(1))->from('tbl_checks')
        ->whereRaw('tbl_persons.id=tbl_checks. person_id');
})->get();

answered Apr 06 '18 at 19:03

Ben

5,069
4
18
26

Not sure your versions are correct; `has()` and `whereHas()` are both working fine on my `"laravel/framework": "5.0.*",` project. – Tim Lewis Apr 06 '18 at 20:38

Laravel group by, with relationship in different tables

2 Answers2

Linked