The subselect in the Union Query may be with a big data

[digitalcraftlabs]

The subselect in the Union Query may be with a big data and this will be a lot for the implemetation

Exemple
(select * from User where login = aa) UNION ALL (select * from User where login = aa)

select * from User where login = aa we get 100000 rows
and the same for the other one

[digitalcraftlabs]

I used this function und solve the problem

protected function getCountForUnionPagination($query)
{
// Get the database connection
$conn = $this->getConnection();

    // Create a new query builder instance
    $qb = new QueryBuilder($conn, $conn->getQueryGrammar(), $conn->getPostProcessor());

    // Get the SQL query from the provided query object
    $sqlQuery = $query->toSql();

    // Replace the FROM clause of the SQL query with a COUNT() function
    $countQuery = preg_replace('/SELECT.*?FROM/i', 'SELECT COUNT(*) as total FROM', $sqlQuery);

    // Generate SQL to represent the provided query as a subquery
    $tableSql = sprintf('(%s) as table_count', $countQuery);

    // Generate raw SQL expression from the subquery SQL
    $tableSql = $conn->raw($tableSql);

    // Execute the query to get the count of rows
    $result = $qb->select([$conn->raw('SUM(total) as total_count')])
        ->from($tableSql)
        ->mergeBindings($query)
        ->first();

    return $result->total_count;
}