Sorting does not work when there are many entries

[VladimirAfonin]

Bug Report

Q	A
BC Break	yes/no
Bundle version	^5.8
Symfony version	6
PHP version	^8.0.2

Summary

When I use pagination and sorting with a relatively small number of records, then everything works fine, when the number of records exceeds ~ 412K, then sorting does not work at all, not by one field, more precisely, it works only within records on one page, to For example, perPage = 25 records per page, and sorting occurs not by ALL 412K records, but only by records that are displayed on the current page.
Thank you very much in advance, your advice is greatly appreciated )

Current behavior

For example, if the records are slightly less than 412K, then everything works fine, if the number of records exceeds a little, then sorting within 'perPage ' works, I also want to note that I am passing the queryBuilder to the paginator

How to reproduce

Get records from DB:

public function getTransactionCommon($userId = null, array $orderBy = []): QueryBuilder
{
    $query = $this->_em
        ->getRepository(Transaction::class)
        ->createQueryBuilder('t');

    $this->getTransactionWhere($userId, $query);

    foreach ($orderBy as $key => $order) {
        $query = $query->orderBy("t.{$key}", $order);
    }
    
    return $query;
}

Next, i create paginator:

$page = $request->get('page', 1);
$limit = $request->get('itemsPerPage', 25);

$pagination = $paginator->paginate($this->getTransactionCommon(), $page, $limit);

Expected behavior

[garak]

What is the resulting query performed by Doctrine on the database? I mean the one you can read inside the profiler

[VladimirAfonin]

good day! I apologize for such a long answer, now in the second table in the database the number of users has become more than 310K and the problem is the same, sorting by id does not work for the entire number of users, but within those records that are displayed on the page (for example, 25 records per page), I will send a request within a day

[VladimirAfonin]

I do not yet understand the relationship between this god and the number of records from the database, as soon as the threshold of records in the database exceeds 310K, then sorting works within only those records that are displayed on the page, and not by the entire number

[garak]

The suggestion of providing here the resulting query is still valid.

[VladimirAfonin]

there is query '$this->findBy()' which goes to paginator -> $pagination = $paginator->paginate($this->findBy(), $page, $limit):

SELECT u0_.id AS id_0, u0_.email AS email_1, u0_.password AS password_2, u0_.verify_status AS verify_status_3, u0_.document_verify AS document_verify_4, u0_.document_is_upload AS document_is_upload_5, u0_.exclude_from_calculation AS exclude_from_calculation_6, u0_.address_is_upload AS address_is_upload_7, u0_.address_is_verify AS address_is_verify_8, u0_.fullname AS fullname_9, u0_.lastlogin AS lastlogin_10, u0_.lastip AS lastip_11, u0_.show_in_preview AS show_in_preview_12, u0_.is_have_deposit_limit AS is_have_deposit_limit_13, u0_.show_only_providers_preview AS show_only_providers_preview_14, u0_.total_debet AS total_debet_15, u0_.blocked_at AS blocked_at_16, u0_.address_verify_at AS address_verify_at_17, u0_.document_verify_at AS document_verify_at_18, u0_.address AS address_19, u0_.zipcode AS zipcode_20, u0_.phone AS phone_21, u0_.codephrase AS codephrase_22, u0_.calculation_at AS calculation_at_23, u0_.processing_commission AS processing_commission_24, u0_.withdraw_commission AS withdraw_commission_25, u0_.buy_voucher_commission AS buy_voucher_commission_26, u0_.activate_voucher_commission AS activate_voucher_commission_27, u0_.calculate_commission AS calculate_commission_28, u0_.automatic_activate_voucher AS automatic_activate_voucher_29, u0_.is_blocked AS is_blocked_30, u0_.birthday AS birthday_31, u0_.city AS city_32, u0_.company_name AS company_name_33, u0_.company_kind AS company_kind_34, u0_.company_url AS company_url_35, u0_.api_token AS api_token_36, u0_.form_token AS form_token_37, u0_.is_subscribed_to_notifications_by_email AS is_subscribed_to_notifications_by_email_38, u0_.document_uploaded_at AS document_uploaded_at_39, u0_.is_exchanger_working AS is_exchanger_working_40, u0_.created_at AS created_at_41, u0_.updated_at AS updated_at_42, u0_.icon_id AS icon_id_43, u0_.user_setting_id AS user_setting_id_44, u0_.currency_debet_id AS currency_debet_id_45, u0_.country_id AS country_id_46, u0_.document_verification_refusal_id AS document_verification_refusal_id_47, u0_.address_verification_refusal_id AS address_verification_refusal_id_48, u0_.block_status_id AS block_status_id_49, u0_.language_id AS language_id_50, u0_.account_manager_id AS account_manager_id_51, u0_.senior_account_manager_id AS senior_account_manager_id_52 FROM user u0_ INNER JOIN user_roles u2_ ON u0_.id = u2_.user_id INNER JOIN roles r1_ ON r1_.id = u2_.role_id INNER JOIN country_translation c3_ ON (c3_.translatable_id = u0_.country_id) WHERE r1_.id = ? ORDER BY u0_.id ASC

[VladimirAfonin]

I emphasize once again that the same query with a large amount of data, in my case it is more than 310K, either sorts by the entire number of records, as it should be, or sorts only within the records on the page

[garak]

Well, the query before the pagination has little value. I was asking for the query after the pagination applies.

[VladimirAfonin]

how can I watch it? $pagination = $paginator->paginate($this->findBy(), $page, $limit); - in $pagination i cant see debug sql methods

[garak]

Don't you have a log? The web debug toolbar?

[VladimirAfonin]

no

[garak]

So I guess we're pretty much done here. Can we close this issue?

[VladimirAfonin]

it turns out that the problem has not been solved with us and we are closing the ticket despite this?

[garak]

Well, if you don't cooperate to solve it, I'm afraid it will never be solved.