1

I have an issue that I can't use the sortWhitelist in paginate to enable the sort function towards the "2nd level's associated model's columns. (it works with the 1st level associated model's). Let me describe it clearly:

Table Background:

  • Users: id, name
  • Tickets: id, code, name, price
  • TicketsDiscounts: id, ticket_code, user_id, price

There is a default price in Tickets, and sometimes there are discounted tickets, offering a cheaper price.

Wish to do:

I am making the view page of Tickets, which doesn't only show the Ticket itself's data, also a table showing

  • TicketDiscounts records whereTickets.ticket_code = Tickets.code, and
  • Users records where TicketDiscounts.user_id = Users.user_id.

Issue:

It actually works fine, til I want to add some sort functions for the table. I know there is a config key called sortWhitelist, but in this case it only supports "linking" TicketsDiscounts.

Like:

Tickets---->TicketsDiscounts [OK]
Tickets---->TicketsDiscounts---->Users [NOT OK]

In TicketsController.php:

public function view($id = null) {
    $ticket = $this->Ticket->get($id);
    $setting = [
        "sortWhitelist" => [
            "Users.id", "Users.name", "TicketsDiscounts.price"
        ]
    ];
    $paginate_discountsowners = $this->paginate(
        $this->Tickets->TicketsDiscounts
            ->findByTicketsCode($ticket->code)
             ->contain("Users"), $setting
    );
    $this->set('paginate_discountsowners', $paginate_discountsowners);
    $this->set('_serialize', ['paginate_discountsowners']);
}

In view.ctp:

<td><?= $this->Paginator->sort("id", "User ID", ["model" => "Users"]) ?></td>
<td><?= $this->Paginator->sort("name", "User Name", ["model" => "Users"]) ?></td>
<td><?= $this->Paginator->sort("price", "Discount Price", ["model" => "TicketsDiscounts"]) ?></td>

Researched: CakePHP - paginate and sort 2nd level association <-- This one is very close, but its CakePHP 2.x, I read it but hardly found this post useful for my case.

Pagination Sort in Cakephp 3.x <-- This is 3.x, well, but it is about 1-level associated model only.

Thank you for reading my post, any ideas are welcomed.

Community
  • 1
  • 1
Tsumannai
  • 134
  • 2
  • 12

1 Answers1

2

I stumbled over something similar. I have the structure Teachers -> Users -> Persons and want to sort by the person's forename.

TeachersController: 

public $paginate = ['sortWhitelist' => ['Persons.forename']];

View:

<?= $this->Paginator->sort('Persons.forename') ?>

So, in short I think you can just have to change your first and second sort links to refer to the fields Users.id and Users.name.

fehnomenal
  • 509
  • 3
  • 10