app/Statistics/Repository/FamilyRepository.php
private function topTenGrandFamilyQuery(int $total): array
    {
        return DB::table('families')
            ->join('link AS children', static function (JoinClause $join): void {
                $join
                    ->on('children.l_from', '=', 'f_id')
                    ->on('children.l_file', '=', 'f_file')
                    ->where('children.l_type', '=', 'CHIL');
            })->join('link AS mchildren', static function (JoinClause $join): void {
                $join
                    ->on('mchildren.l_file', '=', 'children.l_file')
                    ->on('mchildren.l_from', '=', 'children.l_to')
                    ->where('mchildren.l_type', '=', 'FAMS');
            })->join('link AS gchildren', static function (JoinClause $join): void {
                $join
                    ->on('gchildren.l_file', '=', 'mchildren.l_file')
                    ->on('gchildren.l_from', '=', 'mchildren.l_to')
                    ->where('gchildren.l_type', '=', 'CHIL');
            })
            ->where('f_file', '=', $this->tree->id())
            ->groupBy(['f_id', 'f_file'])
            ->orderBy(DB::raw('COUNT(*)'), 'DESC')
            ->select('families.*')
            ->limit($total)
            ->get()
            ->map(Family::rowMapper())
            ->filter(GedcomRecord::accessFilter())
            ->map(static function (Family $family): array {
                $count = 0;
                foreach ($family->children() as $child) {
                    foreach ($child->spouseFamilies() as $spouse_family) {
                        $count += $spouse_family->children()->count();
                    }
                }

                return [
                    'family' => $family,
                    'count'  => $count,
                ];
            })
            ->all();
    }
private function topTenGrandFamilyQuery(int $total): array
    {
        return DB::table('families')
            ->join('link AS children', static function (JoinClause $join): void {
                $join
                    ->on('children.l_from', '=', 'f_id')
                    ->on('children.l_file', '=', 'f_file')
                    ->where('children.l_type', '=', 'CHIL');
            })->join('link AS mchildren', static function (JoinClause $join): void {
                $join
                    ->on('mchildren.l_file', '=', 'children.l_file')
                    ->on('mchildren.l_from', '=', 'children.l_to')
                    ->where('mchildren.l_type', '=', 'FAMS');
            })->join('link AS gchildren', static function (JoinClause $join): void {
                $join
                    ->on('gchildren.l_file', '=', 'mchildren.l_file')
                    ->on('gchildren.l_from', '=', 'mchildren.l_to')
                    ->where('gchildren.l_type', '=', 'CHIL');
            })
            ->where('f_file', '=', $this->tree->id())
            ->groupBy(['f_id', 'f_file'])
            ->orderBy(DB::raw('COUNT(*)'), 'DESC')
            ->select('families.*')
            ->limit($total)
            ->get()
            ->map(Family::rowMapper())
            ->filter(GedcomRecord::accessFilter())
            ->map(static function (Family $family): array {
                $count = 0;
                foreach ($family->children() as $child) {
                    foreach ($child->spouseFamilies() as $spouse_family) {
                        $count += $spouse_family->children()->count();
                    }
                }

                return [
                    'family' => $family,
                    'count'  => $count,
                ];
            })
            ->all();
    }
private function topTenGrandFamilyQuery ( int $ total ) : array { return DB :: table ( 'families' ) -> join ( 'link AS children' , static function ( JoinClause $ join ) : void { $ join -> on ( 'children.l_from' , '=' , 'f_id' ) -> on ( 'children.l_file' , '=' , 'f_file' ) -> where ( 'children.l_type' , '=' , 'CHIL' ) ; } ) -> join ( 'link AS mchildren' , static function ( JoinClause $ join ) : void { $ join -> on ( 'mchildren.l_file' , '=' , 'children.l_file' ) -> on ( 'mchildren.l_from' , '=' , 'children.l_to' ) -> where ( 'mchildren.l_type' , '=' , 'FAMS' ) ; } ) -> join ( 'link AS gchildren' , static function ( JoinClause $ join ) : void { $ join -> on ( 'gchildren.l_file' , '=' , 'mchildren.l_file' ) -> on ( 'gchildren.l_from' , '=' , 'mchildren.l_to' ) -> where ( 'gchildren.l_type' , '=' , 'CHIL' ) ; } ) -> where ( 'f_file' , '=' , $ this -> tree -> id ( ) ) -> groupBy ( [ 'f_id' , 'f_file' ] ) -> orderBy ( DB :: raw ( 'COUNT(*)' ) , 'DESC' ) -> select ( 'families.*' ) -> limit ( $ total ) -> get ( ) -> map ( Family :: rowMapper ( ) ) -> filter ( GedcomRecord :: accessFilter ( ) ) -> map ( static function ( Family $ family ) : array { $ count = 0 ; foreach ( $ family -> children ( ) as $ child ) { foreach ( $ child -> spouseFamilies ( ) as $ spouse_family ) { $ count += $ spouse_family -> children ( ) -> count ( ) ; } } return [ 'family' => $ family , 'count' => $ count , ] ; } ) -> all ( ) ; }
Find the couple with the most grandchildren.

@param int $total

@return array
Find the couple with the most grandchildren .