cakephp - How to reduce the amount of fields for _joinData in Cake 3.x?

Question

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',
    ]);

When I do a pagination like this:

       $this->paginate['contain'] = [
            'Approvings' => function (CakeORMQuery $query) {
                return $query->select([
                    'Approvings.id',
                    'Approvings.name',
                    'Approvings.company',
                    'EventTicketSalesApprovings.event_ticket_sale_id'
                ]);
            }
        ];
        $this->paginate['fields'] = [
            'EventTicketSales.id', 'EventTicketSales.event_id', 'EventTicketSales.billing_amount_in_sgd', 
            'EventTicketSales.payment_terms', 'EventTicketSales.invoice_number', 
            'EventTicketSales.due_date',
        ];

I get the following data:

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"
        }
    }
]

What I want

I would like to also control the amount of data I retrieve from _joinData

Ideally I want as few fields from _joinData as I can possibly retrieve.

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: {
            id: 335,
            contact_id: 63,
            event_ticket_sale_id: "06f39ba3-9a17-47c6-9374-24b49fb64665",
        }
    }
]

I actually don't even need the _joinData if I can help it.

Answer 1

You could for example iterate the collection and remove the properties that you're not interested in, something like:

$eventTicketSales->map(function ($row) {
    foreach ($row['approvings'] as &$approving) {
        unset($approving['_joinData']);
    }

    return $row;
});

Which could also be done in a result formatter of a query object:

$query->formatResults(function (CakeCollectionCollectionInterface $results) {
    return $results->map(function ($row) {
        foreach ($row['approvings'] as &$approving) {
            unset($approving['_joinData']);
        }

        return $row;
    });
});

Or, since you seem to want to apply this in JSON representations, you could mark the _joinData property of the Approving entity as hidden, given that you'd generally want to ditch that property when converting entities to arrays or JSON:

class Approving extends Entity {
    // ...

    protected $_hidden = [
        '_joinData'
    ];

    // ...
}

See also

• Cookbook > Collections
• Cookbook > Database Access & ORM > Query Builder > Queries Are Collection Objects
• Cookbook > Database Access & ORM > Query Builder > Adding Calculated Fields
• Cookbook > Database Access & ORM > Entities > Converting to Arrays/JSON > Hiding Properties