MyArtJaub\Webtrees\Module\Sosa\Services\SosaStatisticsService

Complexity

Total Complexity

23

Size/Duplication

Total Lines	535
Duplicated Lines	0 %

Importance

Changes	1
Bugs	0	Features	0

16 Methods

Rating	Name	Duplication	Size	Complexity
A	__construct()	0	4	1
A	statisticsByGenerations()	0	19	2
B	pedigreeCollapseByGenerationData()	0	134	3
A	isPdoSupported()	0	3	1
A	totalIndividuals()	0	5	1
A	statisticsByGenerationCumulativeData()	0	18	1
A	ancestorsDispersionForGeneration()	0	35	2
A	rootIndividual()	0	4	1
A	totalAncestors()	0	6	1
A	maxGeneration()	0	6	1
A	generationDepthStatsAtGeneration()	0	48	2
A	topMultipleAncestorsWithNoTies()	0	35	2
A	meanGenerationTime()	0	15	2
A	statisticsByGenerationBasicData()	0	14	1
A	totalAncestorsAtGeneration()	0	7	1
A	totalDistinctAncestors()	0	7	1

<?php

/**
 * webtrees-lib: MyArtJaub library for webtrees
 *
 * @package MyArtJaub\Webtrees
 * @subpackage Sosa
 * @author Jonathan Jaubart <dev@jaubart.com>
 * @copyright Copyright (c) 2009-2022, Jonathan Jaubart
 * @license http://www.gnu.org/licenses/gpl.html GNU General Public License, version 3
 */

declare(strict_types=1);

namespace MyArtJaub\Webtrees\Module\Sosa\Services;

use Fisharebest\Webtrees\Individual;
use Fisharebest\Webtrees\Registry;
use Fisharebest\Webtrees\Tree;
use Fisharebest\Webtrees\Contracts\UserInterface;
use Illuminate\Database\Capsule\Manager as DB;
use Illuminate\Database\Query\Builder;
use Illuminate\Database\Query\JoinClause;
use Illuminate\Support\Collection;
use stdClass;

/**
 * Service for retrieving Sosa statistics
 */
class SosaStatisticsService
{
    private UserInterface $user;
    private Tree $tree;

    /**
     * Constructor for Sosa Statistics Service
     *
     * @param Tree $tree
     * @param UserInterface $user
     */
    public function __construct(Tree $tree, UserInterface $user)
    {
        $this->tree = $tree;
        $this->user = $user;
    }

    /**
     * Check if PDO support the queries used in Sosa statistics.
     *
     * SQLite does not support RIGHT and FULL JOIN, and POWER/SQRT require a specific compilation flag.
     *
     * @return bool
     */
    public function isPdoSupported(): bool
    {
        return DB::connection()->getDriverName() !== 'sqlite';
    }

    /**
     * Return the root individual for the reference tree and user.
     *
     * @return Individual|NULL
     */
    public function rootIndividual(): ?Individual
    {
        $root_indi_id = $this->tree->getUserPreference($this->user, 'MAJ_SOSA_ROOT_ID');
        return Registry::individualFactory()->make($root_indi_id, $this->tree);
    }

    /**
     * Get the highest generation for the reference tree and user.
     *
     * @return int
     */
    public function maxGeneration(): int
    {
        return (int) DB::table('maj_sosa')
            ->where('majs_gedcom_id', '=', $this->tree->id())
            ->where('majs_user_id', '=', $this->user->id())
            ->max('majs_gen');
    }

    /**
     * Get the total count of individuals in the tree.
     *
     * @return int
     */
    public function totalIndividuals(): int
    {
        return DB::table('individuals')
            ->where('i_file', '=', $this->tree->id())
            ->count();
    }

    /**
     * Get the total count of Sosa ancestors for all generations
     *
     * @return int
     */
    public function totalAncestors(): int
    {
        return DB::table('maj_sosa')
            ->where('majs_gedcom_id', '=', $this->tree->id())
            ->where('majs_user_id', '=', $this->user->id())
            ->count();
    }

    /**
     * Get the total count of Sosa ancestors for a generation
     *
     * @return int
     */
    public function totalAncestorsAtGeneration(int $gen): int
    {
        return DB::table('maj_sosa')
            ->where('majs_gedcom_id', '=', $this->tree->id())
            ->where('majs_user_id', '=', $this->user->id())
            ->where('majs_gen', '=', $gen)
            ->count();
    }

    /**
     * Get the total count of distinct Sosa ancestors for all generations
     *
     * @return int
     */
    public function totalDistinctAncestors(): int
    {
        return DB::table('maj_sosa')
            ->where('majs_gedcom_id', '=', $this->tree->id())
            ->where('majs_user_id', '=', $this->user->id())
            ->distinct()
            ->count('majs_i_id');
    }

    /**
     * Get the mean generation time, as the slope of the linear regression of birth years vs generations
     *
     * @return float
     */
    public function meanGenerationTime(): float
    {
        $row = DB::table('maj_sosa')
            ->where('majs_gedcom_id', '=', $this->tree->id())
            ->where('majs_user_id', '=', $this->user->id())
            ->whereNotNull('majs_birth_year')
            ->selectRaw('COUNT(majs_sosa) AS n')
            ->selectRaw('SUM(majs_gen * majs_birth_year) AS sum_xy')
            ->selectRaw('SUM(majs_gen) AS sum_x')
            ->selectRaw('SUM(majs_birth_year) AS sum_y')
            ->selectRaw('SUM(majs_gen * majs_gen) AS sum_x2')
            ->get()->first();

        return $row->n == 0 ? 0 :
            -($row->n * $row->sum_xy - $row->sum_x * $row->sum_y) / ($row->n * $row->sum_x2 - pow($row->sum_x, 2));
    }

    /**
     * Get the statistic array detailed by generation.
     * Statistics for each generation are:
     *  - The number of Sosa in generation
     *  - The number of Sosa up to generation
     *  - The number of distinct Sosa up to generation
     *  - The year of the first birth in generation
     *  - The year of the first estimated birth in generation
     *  - The year of the last birth in generation
     *  - The year of the last estimated birth in generation
     *  - The average year of birth in generation
     *
     * @return array<int, array<string, int|null>> Statistics array
     */
    public function statisticsByGenerations(): array
    {
        $stats_by_gen = $this->statisticsByGenerationBasicData();
        $cumul_stats_by_gen = $this->statisticsByGenerationCumulativeData();

        $statistics_by_gen = [];
        foreach ($stats_by_gen as $gen => $stats_gen) {
            $statistics_by_gen[(int) $stats_gen->gen] = array(
                'sosaCount'             =>  (int) $stats_gen->total_sosa,
                'sosaTotalCount'        =>  (int) $cumul_stats_by_gen[$gen]->total_cumul,
                'diffSosaTotalCount'    =>  (int) $cumul_stats_by_gen[$gen]->total_distinct_cumul,
                'firstBirth'            =>  $stats_gen->first_year,
                'firstEstimatedBirth'   =>  $stats_gen->first_est_year,
                'lastBirth'             =>  $stats_gen->last_year,
                'lastEstimatedBirth'    =>  $stats_gen->last_est_year
            );
        }

        return $statistics_by_gen;
    }

    /**
     * Returns the basic statistics data by generation.
     *
     * @return Collection<int, \stdClass>
     */
    private function statisticsByGenerationBasicData(): Collection
    {
        return DB::table('maj_sosa')
            ->where('majs_gedcom_id', '=', $this->tree->id())
            ->where('majs_user_id', '=', $this->user->id())
            ->groupBy('majs_gen')
            ->orderBy('majs_gen', 'asc')
            ->select('majs_gen AS gen')
            ->selectRaw('COUNT(majs_sosa) AS total_sosa')
            ->selectRaw('MIN(majs_birth_year) AS first_year')
            ->selectRaw('MIN(majs_birth_year_est) AS first_est_year')
            ->selectRaw('MAX(majs_birth_year) AS last_year')
            ->selectRaw('MAX(majs_birth_year_est) AS last_est_year')
            ->get()->keyBy('gen');
    }

    /**
     * Returns the cumulative statistics data by generation
     *
     * @return Collection<int, \stdClass>
     */
    private function statisticsByGenerationCumulativeData(): Collection
    {
        $list_gen = DB::table('maj_sosa')->select('majs_gen')->distinct()
            ->where('majs_gedcom_id', '=', $this->tree->id())
            ->where('majs_user_id', '=', $this->user->id());

        return DB::table('maj_sosa')
            ->joinSub($list_gen, 'list_gen', function (JoinClause $join): void {
                $join->on('maj_sosa.majs_gen', '<=', 'list_gen.majs_gen')
                ->where('majs_gedcom_id', '=', $this->tree->id())
                ->where('majs_user_id', '=', $this->user->id());
            })
            ->groupBy('list_gen.majs_gen')
            ->select('list_gen.majs_gen AS gen')
            ->selectRaw('COUNT(majs_i_id) AS total_cumul')
            ->selectRaw('COUNT(DISTINCT majs_i_id) AS total_distinct_cumul')
            ->selectRaw('1 - COUNT(DISTINCT majs_i_id) / COUNT(majs_i_id) AS pedi_collapse_simple')
            ->get()->keyBy('gen');
    }

    /**
     * Returns the pedigree collapse improved calculation by generation.
     *
     * Format:
     *  - key : generation
     *  - values:
     *      - pedi_collapse_roots : pedigree collapse of ancestor roots for the generation
     *      - pedi_collapse_xgen : pedigree cross-generation shrinkage for the generation
     *
     * @return array<int, array<string, float>>
     */
    public function pedigreeCollapseByGenerationData(): array
    {
        if (!$this->isPdoSupported()) {
            return [];
        }

        $table_prefix = DB::connection()->getTablePrefix();

        $list_gen = DB::table('maj_sosa')->select('majs_gen')->distinct()
            ->where('majs_gedcom_id', '=', $this->tree->id())
            ->where('majs_user_id', '=', $this->user->id());

        /* Compute the contributions of nodes of previous generations to the current generation */
        $root_ancestors_contributions = DB::table('maj_sosa AS sosa')
            ->select(['list_gen.majs_gen AS gen', 'sosa.majs_gedcom_id', 'sosa.majs_user_id'])
            ->addSelect(['sosa.majs_i_id', 'sosa.majs_gen'])
            ->selectRaw(
                '(CASE ' .
                    ' WHEN ' . $table_prefix . 'sosa_fat.majs_i_id IS NULL' .
                    ' THEN POWER(2, ' . $table_prefix . 'list_gen.majs_gen - ' . $table_prefix . 'sosa.majs_gen - 1)' .
                    ' ELSE 0 ' .
                ' END)' .
                ' + (CASE ' .
                    ' WHEN ' . $table_prefix . 'sosa_mot.majs_i_id IS NULL' .
                    ' THEN POWER(2, ' . $table_prefix . 'list_gen.majs_gen - ' . $table_prefix . 'sosa.majs_gen - 1)' .
                    ' ELSE 0 ' .
                ' END) contrib'
            )
            ->joinSub($list_gen, 'list_gen', function (JoinClause $join): void {
                $join->on('sosa.majs_gen', '<', 'list_gen.majs_gen')
                    ->where('majs_gedcom_id', '=', $this->tree->id())
                    ->where('majs_user_id', '=', $this->user->id());
            })
            ->leftJoin('maj_sosa AS sosa_fat', function (JoinClause $join) use ($table_prefix): void {
                // Link to sosa's father
                $join->whereRaw($table_prefix . 'sosa_fat.majs_sosa = 2 * ' . $table_prefix . 'sosa.majs_sosa')
                    ->where('sosa_fat.majs_gedcom_id', '=', $this->tree->id())
                    ->where('sosa_fat.majs_user_id', '=', $this->user->id());
            })
            ->leftJoin('maj_sosa AS sosa_mot', function (JoinClause $join) use ($table_prefix): void {
                // Link to sosa's mother
                $join->whereRaw($table_prefix . 'sosa_mot.majs_sosa = 2 * ' . $table_prefix . 'sosa.majs_sosa + 1')
                    ->where('sosa_mot.majs_gedcom_id', '=', $this->tree->id())
                    ->where('sosa_mot.majs_user_id', '=', $this->user->id());
            })
            ->where('sosa.majs_gedcom_id', '=', $this->tree->id())
            ->where('sosa.majs_user_id', '=', $this->user->id())
            ->where(function (Builder $query): void {
                $query->whereNull('sosa_fat.majs_i_id')
                ->orWhereNull('sosa_mot.majs_i_id');
            });

        /* Identify nodes in the generations with ancestors who are also in the same generation.
         * This is the vertical/generational collapse that will reduce the number or roots.
         */
        $non_roots_ancestors = DB::table('maj_sosa AS sosa')
            ->select(['sosa.majs_gen', 'sosa.majs_gedcom_id', 'sosa.majs_user_id', 'sosa.majs_sosa'])
            ->selectRaw('MAX(' . $table_prefix . 'sosa_anc.majs_sosa) - MIN(' . $table_prefix . 'sosa_anc.majs_sosa)' .
                ' AS full_ancestors')
            ->join('maj_sosa AS sosa_anc', function (JoinClause $join) use ($table_prefix): void {
                $join->on('sosa.majs_gen', '<', 'sosa_anc.majs_gen')
                    ->whereRaw('FLOOR(' . $table_prefix . 'sosa_anc.majs_sosa / POWER(2, ' .
                        $table_prefix . 'sosa_anc.majs_gen - ' . $table_prefix . 'sosa.majs_gen)) = ' .
                        $table_prefix . 'sosa.majs_sosa')
                    ->where('sosa_anc.majs_gedcom_id', '=', $this->tree->id())
                    ->where('sosa_anc.majs_user_id', '=', $this->user->id());
            })
            ->where('sosa.majs_gedcom_id', '=', $this->tree->id())
            ->where('sosa.majs_user_id', '=', $this->user->id())
            ->whereIn('sosa_anc.majs_i_id', function (Builder $query) use ($table_prefix): void {
                $query->from('maj_sosa AS sosa_gen')
                ->select('sosa_gen.majs_i_id')->distinct()
                ->where('sosa_gen.majs_gedcom_id', '=', $this->tree->id())
                ->where('sosa_gen.majs_user_id', '=', $this->user->id())
                ->whereRaw($table_prefix . 'sosa_gen.majs_gen = ' . $table_prefix . 'sosa.majs_gen');
            })
            ->groupBy(['sosa.majs_gen', 'sosa.majs_gedcom_id', 'sosa.majs_user_id',
                'sosa.majs_sosa', 'sosa.majs_i_id']);

        /* Compute the contribution of the nodes in the generation,
         * excluding the nodes with ancestors in the same generation.
         * Nodes with a parent missing are not excluded to cater for the missing one.
         */
        $known_ancestors_contributions = DB::table('maj_sosa AS sosa')
            ->select(['sosa.majs_gen AS gen', 'sosa.majs_gedcom_id', 'sosa.majs_user_id'])
            ->addSelect(['sosa.majs_i_id', 'sosa.majs_gen'])
            ->selectRaw('1 AS contrib')
            ->leftJoinSub($non_roots_ancestors, 'nonroot', function (JoinClause $join): void {
                $join->on('sosa.majs_gen', '=', 'nonroot.majs_gen')
                    ->on('sosa.majs_sosa', '=', 'nonroot.majs_sosa')
                    ->where('nonroot.full_ancestors', '>', 0)
                    ->where('nonroot.majs_gedcom_id', '=', $this->tree->id())
                    ->where('nonroot.majs_user_id', '=', $this->user->id());
            })
            ->where('sosa.majs_gedcom_id', '=', $this->tree->id())
            ->where('sosa.majs_user_id', '=', $this->user->id())
            ->whereNull('nonroot.majs_sosa');

        /* Aggregate both queries, and calculate the sum of contributions by generation roots.
         * Exclude as well nodes that already appear in lower generations, as their branche has already been reduced.
         */
        $ancestors_contributions_sum = DB::connection()->query()
            ->fromSub($root_ancestors_contributions->unionAll($known_ancestors_contributions), 'sosa_contribs')
            ->select(['sosa_contribs.gen', 'sosa_contribs.majs_gedcom_id', 'sosa_contribs.majs_user_id'])
            ->addSelect(['sosa_contribs.majs_i_id', 'sosa_contribs.contrib'])
            ->selectRaw('COUNT(' . $table_prefix . 'sosa_contribs.majs_i_id) * ' .
                $table_prefix . 'sosa_contribs.contrib AS totalContrib')
            ->leftJoin('maj_sosa AS sosa_low', function (JoinClause $join): void {
                $join->on('sosa_low.majs_gen', '<', 'sosa_contribs.majs_gen')
                    ->on('sosa_low.majs_i_id', '=', 'sosa_contribs.majs_i_id')
                    ->where('sosa_low.majs_gedcom_id', '=', $this->tree->id())
                    ->where('sosa_low.majs_user_id', '=', $this->user->id());
            })
            ->whereNull('sosa_low.majs_sosa')
            ->groupBy(['sosa_contribs.gen', 'sosa_contribs.majs_gedcom_id', 'sosa_contribs.majs_user_id',
                'sosa_contribs.majs_i_id', 'sosa_contribs.contrib']);

        // Aggregate all generation roots to compute root and generation pedigree collapse
        $pedi_collapse_coll = DB::connection()->query()->fromSub($ancestors_contributions_sum, 'sosa_contribs_sum')
            ->select(['gen'])->selectRaw('SUM(contrib), SUM(totalContrib)')
            ->selectRaw('1 - SUM(contrib) / SUM(totalContrib) AS pedi_collapse_roots')  // Roots/horizontal collapse
            ->selectRaw('1 - SUM(totalContrib) / POWER ( 2, gen - 1) AS pedi_collapse_xgen') // Crossgeneration collapse
            ->groupBy(['gen', 'majs_gedcom_id', 'majs_user_id'])
            ->orderBy('gen')
            ->get();

        $pedi_collapse_by_gen = [];
        foreach ($pedi_collapse_coll as $collapse_gen) {
            $pedi_collapse_by_gen[(int) $collapse_gen->gen] = array(
                'pedi_collapse_roots'   =>  (float) $collapse_gen->pedi_collapse_roots,
                'pedi_collapse_xgen'   =>  (float) $collapse_gen->pedi_collapse_xgen
            );
        }
        return $pedi_collapse_by_gen;
    }

    /**
     * Return a Collection of the mean generation depth and deviation for all Sosa ancestors at a given generation.
     * Sosa 1 is of generation 1.
     *
     * Mean generation depth and deviation are calculated based on the works of Marie-Héléne Cazes and Pierre Cazes,
     * published in Population (French Edition), Vol. 51, No. 1 (Jan. - Feb., 1996), pp. 117-140
     * http://kintip.net/index.php?option=com_jdownloads&task=download.send&id=9&catid=4&m=0
     *
     * Format:
     *  - key : sosa number of the ancestor
     *  - values:
     *      - root_ancestor_id : ID of the ancestor
     *      - mean_gen_depth : Mean generation depth
     *      - stddev_gen_depth : Standard deviation of generation depth
     *
     * @param int $gen Sosa generation
     * @return Collection<int, \stdClass>
     */
    public function generationDepthStatsAtGeneration(int $gen): Collection
    {
        if (!$this->isPdoSupported()) {
            return collect();
        }

        $table_prefix = DB::connection()->getTablePrefix();
        $missing_ancestors_by_gen = DB::table('maj_sosa AS sosa')
            ->selectRaw($table_prefix . 'sosa.majs_gen - ? AS majs_gen_norm', [$gen])
            ->selectRaw('FLOOR(((' . $table_prefix . 'sosa.majs_sosa / POW(2, ' . $table_prefix . 'sosa.majs_gen -1 )) - 1) * POWER(2, ? - 1)) + POWER(2, ? - 1) AS root_ancestor', [$gen, $gen])   //@phpcs:ignore Generic.Files.LineLength.TooLong
            ->selectRaw('SUM(CASE WHEN ' . $table_prefix . 'sosa_fat.majs_i_id IS NULL AND ' . $table_prefix . 'sosa_mot.majs_i_id IS NULL THEN 1 ELSE 0 END) AS full_root_count')  //@phpcs:ignore Generic.Files.LineLength.TooLong
            ->selectRaw('SUM(CASE WHEN ' . $table_prefix . 'sosa_fat.majs_i_id IS NULL AND ' . $table_prefix . 'sosa_mot.majs_i_id IS NULL THEN 0 ELSE 1 END) As semi_root_count')  //@phpcs:ignore Generic.Files.LineLength.TooLong
            ->leftJoin('maj_sosa AS sosa_fat', function (JoinClause $join) use ($table_prefix): void {
                // Link to sosa's father
                $join->whereRaw($table_prefix . 'sosa_fat.majs_sosa = 2 * ' . $table_prefix . 'sosa.majs_sosa')
                ->where('sosa_fat.majs_gedcom_id', '=', $this->tree->id())
                ->where('sosa_fat.majs_user_id', '=', $this->user->id());
            })
            ->leftJoin('maj_sosa AS sosa_mot', function (JoinClause $join) use ($table_prefix): void {
                // Link to sosa's mother
                $join->whereRaw($table_prefix . 'sosa_mot.majs_sosa = 2 * ' . $table_prefix . 'sosa.majs_sosa + 1')
                ->where('sosa_mot.majs_gedcom_id', '=', $this->tree->id())
                ->where('sosa_mot.majs_user_id', '=', $this->user->id());
            })
            ->where('sosa.majs_gedcom_id', '=', $this->tree->id())
            ->where('sosa.majs_user_id', '=', $this->user->id())
            ->where('sosa.majs_gen', '>=', $gen)
            ->where(function (Builder $query): void {
                $query->whereNull('sosa_fat.majs_i_id')
                    ->orWhereNull('sosa_mot.majs_i_id');
            })
            ->groupBy(['sosa.majs_gen', 'root_ancestor']);

        return DB::table('maj_sosa AS sosa_list')
            ->select(['stats_by_gen.root_ancestor AS root_ancestor_sosa', 'sosa_list.majs_i_id as root_ancestor_id'])
            ->selectRaw('1 + SUM( (majs_gen_norm) * ( 2 * full_root_count + semi_root_count) /  (2 * POWER(2, majs_gen_norm))) AS mean_gen_depth')  //@phpcs:ignore Generic.Files.LineLength.TooLong
            ->selectRaw(' SQRT(' .
                '   SUM(POWER(majs_gen_norm, 2) * ( 2 * full_root_count + semi_root_count) /  (2 * POWER(2, majs_gen_norm)))' .     //@phpcs:ignore Generic.Files.LineLength.TooLong
                '   - POWER( SUM( (majs_gen_norm) * ( 2 * full_root_count + semi_root_count) /  (2 * POWER(2, majs_gen_norm))), 2)' .       //@phpcs:ignore Generic.Files.LineLength.TooLong
                ' ) AS stddev_gen_depth')
            ->joinSub($missing_ancestors_by_gen, 'stats_by_gen', function (JoinClause $join): void {
                $join->on('sosa_list.majs_sosa', '=', 'stats_by_gen.root_ancestor')
                    ->where('sosa_list.majs_gedcom_id', '=', $this->tree->id())
                    ->where('sosa_list.majs_user_id', '=', $this->user->id());
            })
            ->groupBy(['stats_by_gen.root_ancestor', 'sosa_list.majs_i_id'])
            ->orderBy('stats_by_gen.root_ancestor')
            ->get()->keyBy('root_ancestor_sosa');
    }

    /**
     * Return a collection of the most duplicated root Sosa ancestors.
     * The number of ancestors to return is limited by the parameter $limit.
     * If several individuals are tied when reaching the limit, none of them are returned,
     * which means that there can be less individuals returned than requested.
     *
     * Format:
     *  - value:
     *      - sosa_i_id : sosa individual
     *      - sosa_count: number of duplications of the ancestor (e.g. 3 if it appears 3 times)
     *
     * @param int $limit
     * @return Collection<\stdClass>
     */
    public function topMultipleAncestorsWithNoTies(int $limit): Collection
    {
        $table_prefix = DB::connection()->getTablePrefix();
        $multiple_ancestors = DB::table('maj_sosa AS sosa')
            ->select('sosa.majs_i_id AS sosa_i_id')
            ->selectRaw('COUNT(' . $table_prefix . 'sosa.majs_sosa) AS sosa_count')
            ->leftJoin('maj_sosa AS sosa_fat', function (JoinClause $join) use ($table_prefix): void {
                // Link to sosa's father
                $join->whereRaw($table_prefix . 'sosa_fat.majs_sosa = 2 * ' . $table_prefix . 'sosa.majs_sosa')
                    ->where('sosa_fat.majs_gedcom_id', '=', $this->tree->id())
                    ->where('sosa_fat.majs_user_id', '=', $this->user->id());
            })
            ->leftJoin('maj_sosa AS sosa_mot', function (JoinClause $join) use ($table_prefix): void {
                // Link to sosa's mother
                $join->whereRaw($table_prefix . 'sosa_mot.majs_sosa = 2 * ' . $table_prefix . 'sosa.majs_sosa + 1')
                ->where('sosa_mot.majs_gedcom_id', '=', $this->tree->id())
                ->where('sosa_mot.majs_user_id', '=', $this->user->id());
            })
            ->where('sosa.majs_gedcom_id', '=', $this->tree->id())
            ->where('sosa.majs_user_id', '=', $this->user->id())
            ->whereNull('sosa_fat.majs_sosa')   // We keep only root individuals, i.e. those with no father or mother
            ->whereNull('sosa_mot.majs_sosa')
            ->groupBy('sosa.majs_i_id')
            ->havingRaw('COUNT(' . $table_prefix . 'sosa.majs_sosa) > 1')    // Limit to the duplicate sosas.
            ->orderByRaw('COUNT(' . $table_prefix . 'sosa.majs_sosa) DESC, MIN(' . $table_prefix . 'sosa.majs_sosa) ASC')   //@phpcs:ignore Generic.Files.LineLength.TooLong
            ->limit($limit + 1)     // We want to select one more than required, for ties
            ->get();

        if ($multiple_ancestors->count() > $limit) {
            $last_count = $multiple_ancestors->last()->sosa_count;
            $multiple_ancestors = $multiple_ancestors->reject(
                fn (stdClass $element): bool => $element->sosa_count ===  $last_count
            );
        }
        return $multiple_ancestors;
    }

    /**
     * Return a computed array of statistics about the dispersion of ancestors across the ancestors
     * at a specified generation.
     *
     * Format:
     *  - key : rank of the ancestor in generation G for which exclusive ancestors have been found
     *          For instance 3 represent the maternal grand father
     *          0 is used for shared ancestors
     *  - values:
     *      - branches: same as key
     *      - majs_i_id: xref of the ancestor at rank key in generation G, or null for shared ancestors
     *      - count_indi: number of ancestors exclusively in the ancestors of the ancestor at rank key
     *
     * For instance a result at generation 3 could be :
     * [
     *  0 => { branches: 0, majs_i_id: X1, count_indi: 12 } -> 12 ancestors are shared by the grand-parents
     *  1 => { branches: 1, majs_i_id: X2, count_indi: 32 } -> 32 ancestors are exclusive to the paternal grand-father
     *  2 => { branches: 2, majs_i_id: X3, count_indi: 25 } -> 25 ancestors are exclusive to the paternal grand-mother
     *  3 => { branches: 3, majs_i_id: X4, count_indi: 12 } -> 12 ancestors are exclusive to the maternal grand-father
     *  4 => { branches: 4, majs_i_id: X5, count_indi: 30 } -> 30 ancestors are exclusive to the maternal grand-mother
     * ]
     *
     * @param int $gen
     * @return Collection<int, \stdClass>
     */
    public function ancestorsDispersionForGeneration(int $gen): Collection
    {
        if (!$this->isPdoSupported()) {
            return collect();
        }

        $ancestors_branches = DB::table('maj_sosa')
            ->select('majs_i_id AS i_id')
            ->selectRaw('FLOOR(majs_sosa / POW(2, (majs_gen - ?))) - POW(2, ? -1) + 1 AS branch', [$gen, $gen])
            ->where('majs_gedcom_id', '=', $this->tree->id())
            ->where('majs_user_id', '=', $this->user->id())
            ->where('majs_gen', '>=', $gen)
            ->groupBy('majs_i_id', 'branch');

        $consolidated_ancestors_branches = DB::table('maj_sosa')
            ->fromSub($ancestors_branches, 'indi_branch')
            ->select('i_id')
            ->selectRaw('CASE WHEN COUNT(branch) > 1 THEN 0 ELSE MIN(branch) END AS branches')
            ->groupBy('i_id');

        return DB::table('maj_sosa')
            ->rightJoinSub(
                $consolidated_ancestors_branches,
                'indi_branch_consolidated',
                function (JoinClause $join) use ($gen): void {
                    $join->where('maj_sosa.majs_gedcom_id', '=', $this->tree->id())
                        ->where('maj_sosa.majs_user_id', '=', $this->user->id())
                        ->where('branches', '>', 0)
                        ->whereRaw('majs_sosa = POW(2, ? - 1) + branches - 1', [$gen]);
                }
            )
            ->select(['branches', 'majs_i_id'])
            ->selectRaw('COUNT(i_id) AS count_indi')
            ->groupBy(['branches', 'majs_i_id'])
            ->get()->keyBy('branches');
    }
}