mpyw / compoships-eager-limit

src/Database/Query/Grammar/Concerns/CompilesMySqlGroupLimitByMultipleColumnPartition.php

<?php

namespace Mpyw\ComposhipsEagerLimit\Database\Query\Grammar\Concerns;

use Illuminate\Database\Query\Builder;
use Staudenmeir\EloquentEagerLimit\Grammars\Traits\CompilesMySqlGroupLimit;

/**
 * Trait CompilesMySqlGroupLimitByMultipleColumnPartition
 *
 * @mixin \Illuminate\Database\Query\Grammars\MySqlGrammar
 */
trait CompilesMySqlGroupLimitByMultipleColumnPartition
{
    use CompilesMySqlGroupLimit, CompilesGroupLimitByMultipleColumnPartition {
        CompilesGroupLimitByMultipleColumnPartition::compileRowNumber insteadof CompilesMySqlGroupLimit;
        CompilesMySqlGroupLimit::compileGroupLimit insteadof CompilesGroupLimitByMultipleColumnPartition;
    }

    /**
     * Compile a group limit clause for MySQL < 8.0.
     *
     * Derived from https://softonsofa.com/tweaking-eloquent-relations-how-to-get-n-related-models-per-parent/.
     *
     * @param  \Illuminate\Database\Query\Builder|\Mpyw\ComposhipsEagerLimit\Database\Query\Builder $query
     * @return string
     */
    protected function compileLegacyGroupLimit(Builder $query)
    {
        $limit = (int)$query->groupLimit['value'] + (int)$query->offset;

The property groupLimit does not seem to exist on Illuminate\Database\Query\Builder.

        $offset = $query->offset;

        $query->offset = null;
        $query->orders = (array)$query->orders;

        $partitionExpressions = [];
        $partitionAssignments = [];
        $partitionInitializations = [];
        $partitionOrders = [];

        if (is_array($query->groupLimit['column'])) {
            foreach ($query->groupLimit['column'] as $i => $column) {
                $wrappedColumn = $this->wrap(last(explode('.', $column)));

It seems like wrap() must be provided by classes using this trait. How about adding it as abstract method to this trait?

                $partitionExpressions[] = "@laravel_partition_$i = $wrappedColumn";
                $partitionAssignments[] = "@laravel_partition_$i := $wrappedColumn";
                $partitionInitializations[] = "@laravel_partition_$i := 0";
                $partitionOrders[] = ['column' => $column, 'direction' => 'asc'];
            }
        } else {
            $wrappedColumn = $this->wrap(last(explode('.', $query->groupLimit['column'])));

            $partitionExpressions[] = "@laravel_partition = $wrappedColumn";
            $partitionAssignments[] = "@laravel_partition := $wrappedColumn";
            $partitionInitializations[] = '@laravel_partition := 0';
            $partitionOrders[] = ['column' => $query->groupLimit['column'], 'direction' => 'asc'];
        }

        $partition = sprintf(
            ', @laravel_row := if(%s, @laravel_row + 1, 1) as laravel_row, %s',
            implode(' and ', $partitionExpressions),
            implode(', ', $partitionAssignments)
        );

        array_splice($query->orders, 0, 0, $partitionOrders);

        $components = $this->compileComponents($query);

It seems like compileComponents() must be provided by classes using this trait. How about adding it as abstract method to this trait?

        $sql = $this->concatenate($components);

It seems like concatenate() must be provided by classes using this trait. How about adding it as abstract method to this trait?

        $from = sprintf(
            '(select @laravel_row := 0, %s) as laravel_vars, (%s) as laravel_table',
            implode(', ', $partitionInitializations),
            $sql
        );

        $sql = 'select laravel_table.*' . $partition . ' from ' . $from . ' having laravel_row <= ' . $limit;

        if ($offset !== null) {

The condition $offset !== null is always true.

            $sql .= ' and laravel_row > ' . (int)$offset;
        }

        return $sql . ' order by laravel_row';
    }
}