I want to get all of the data based on the group by query result. What should I do to get more efficient resource? Thank's in advance

    $familyCardList = DB::table('congregations')
                    ->select('church_family_card_number', DB::raw('count(*) as total'))
                    ->groupBy('church_family_card_number')
                    ->get();

    foreach($familyCardList as $fam)
    {
        echo Congregation::where('church_family_card_number', $fam->church_family_card_number)->where('is_alive', 1)->where(function ($query) {
            $query->where('family_relationship_status_id', 1)
                ->orWhere('family_relationship_status_id', 2)
                ->orWhere('family_relationship_status_id', 3);
        })->first()->full_name . "<br>";
    }

CodePudding user response：

you have hit on one of the common problems with ORM (Object Relational Mapper) which is the N 1 problem. One query to get your seed data set and then N queries to loop through them and get other relevant data. I don't know much about Laravel and its ORM but one way would be to build a view on your database that calculates this for you and use the view instead of the raw table. Another would be to concatenate the card IDs together and pass them to the second query as a match. A third way would be to pull back ALL the cards and do the filtering, grouping and counts on the client side. This would make the query faster and reduce the load on the database but would likely increase your network traffic and expose more data to the client side where is it less secure and may not make your application run faster.

CodePudding user response：

I have done by doing this

        $familyMember = Congregation::where('is_alive', 1)->where(function ($query) {
            $query->where('family_relationship_status_id', 1)
                ->orWhere('family_relationship_status_id', 2)
                ->orWhere('family_relationship_status_id', 3);
        })->orderBy('church_family_card_number')->get();

        $familyCardList = $familyMember->unique('church_family_card_number')->values()->all();
        
        
        foreach ($familyCardList as $fam) 
        {
            echo $fam->full_name . " - " . $fam->family_relationship_status_id  . "<br>";
        }