CODEWITHSUNDEEP
cakephppaginationcakephp-3.0
Kim Stacks
Kim Stacks

Reputation: 10812

How to control the fields to display for belongsToMany for pagination in Cake 3.x?

Situation

Using Cake 3.2.4

I have a EventTicketSales table

that

  $this->belongsToMany('Approvings', [
        'className' => 'Contacts',
        'joinTable' => 'event_ticket_sales_approvings',
        'targetForeignKey' => 'contact_id',
        'saveStrategy' => 'replace',
    ]);

So when I do a pagination in EventTicketSales table, I only want to display 2 fields from the Approvings, i.e. Approvings.name and Approvings.company.

What I have tried

I have tried the following:

$this->paginate["contain"] = ["Approvings"];
$this->paginate['fields'] = [
            'EventTicketSales.id', 'EventTicketSales.event_id', 'EventTicketSales.billing_amount_in_sgd', 
            'EventTicketSales.payment_terms', 'EventTicketSales.invoice_number', 
            'EventTicketSales.due_date',
  'Approvings.id', 'Approvings.name', 'Approvings.company'
        ];

I got an error message about JsonSerialize in a non related model.

  [Exception] Failed calling App\Model\Entity\User::jsonSerialize() #0 /var/virtual/WebApps/corri/vendor/cakephp/cakephp/src/View/JsonView.php(0

What I expected

I expected the results in the following: (I am showing in json format)

id: "06f39ba3-9a17-47c6-9374-24b49fb64665",
event_id: 7,
billing_amount_in_sgd: 7680.03,
payment_terms: "45 days",
invoice_number: "9191",
due_date: "2016-03-05T00:00:00+0800",
approvings: [
    {
        id: 63,
        name: "Jaime Jen",

        company: "Apple Company",

        _joinData: {
            contact_id: 63,
            id: 335,
            event_ticket_sale_id: "06f39ba3-9a17-47c6-9374-24b49fb64665",
            created: "2016-01-20T13:43:44+0800",
            modified: "2016-01-20T13:43:44+0800"
        }
    }
]

Upvotes: 0

Views: 317

Answers (1)

ndm
ndm

Reputation: 60463

The paginators fields option is being applied to the main query finder, and since belongsToMany assoications are being retrieved via a seaprate query, that option won't have any effect on it.

Instead, you have to configure your containments accordingly, that is, using a callback, or the fields option. Also note that when restricting hasMany or belongsToMany associations, you will have to also select the proper foreign keys.

$this->paginate['contain'] = [
    'Approvings' => function (\Cake\ORM\Query $query) {
        return $query->select([
            'Approvings.id',
            'Approvings.name',
            'Approvings.company',
            'EventTicketSalesApprovings.event_ticket_sale_id'
        ]);
    }
];

See also

Upvotes: 1

Related Questions