CODEWITHSUNDEEP
phpsqlcakephpassociations
Mark
Mark

Reputation: 5100

CakePHP associations: handle null id

I have this model:

Proforma
  ->hasMany('ItemProformas', ['foreignKey' => 'proforma_id']);
  ->belongsTo('Customers', ['foreignKey' => 'customer_id']);
  ->belongsTo('ProformaStates', ['foreignKey' => 'proforma_state_id']);
  ->hasMany('Invoices', ['foreignKey' => 'proforma_id']);

ItemProformas
  ->belongsTo('Proformas', ['foreignKey' => 'proforma_id', 'joinType' => 'INNER']);
  ->belongsTo('ItemDeliveryNotes', ['foreignKey' => 'item_delivery_note_id']);

ItemDeliveryNotes
    ->belongsTo('DeliveryNotes', ['foreignKey' => 'delivery_note_id', 'joinType' => 'INNER']);
    ->belongsTo('ItemOrders', ['foreignKey' => 'item_order_id']);
    ->belongsTo('ItemOrdersTypes', ['foreignKey' => 'item_orders_type_id']);
    ->belongsTo('Products', ['foreignKey' => 'product_id']);

Each ItemProforma may have one ItemDeliveryNotes, otherwise the foreign key will be null. Here my paginate call:

$this->paginate = [
    'contain' => [
        'Customers',
        'ProformaStates',
        'ItemProformas' => ['ItemDeliveryNotes' => ['DeliveryNotes']]
    ]
];

With this model, I get all the itemProforma that have item_delivery_note_id set. Instead I'm interesed to get them all, even if item_delivery_note_id is null.

I'm not sure if belongsTo is correct here (I mean in ItemProformas definition). But hasOne implies it has one associated row, not may have one.

What is the correct syntax to retrieve all itemProformas even if they don't have any ItemDeliveryNote associated? But if they have, I need to retrieve the ItemDeliveryNote object as well.

Upvotes: 0

Views: 323

Answers (1)

ndm
ndm

Reputation: 60493

The association type depends on your schema. If the foreign key is in the source table, then it's belongsTo, if the foreign key is in the target table, then it's hasOne.

Whether a related record must exist primarily depends on the schema too, not on the type of association. If the foreign key is nullable, then the related record is optional. If and how you implement enforcing that constraint on application level is a different story.

That being said, ItemDeliveryNotes and DeliveryNotes are both belongsTo that will use joins by default, so both associations will be joined into the same query, and since you've configured the DeliveryNotes association to use an INNER join, it will exclude rows where no DeliveryNotes exist, which of course is also the case when no ItemDeliveryNotes exist.

Assuming your schema is modeled correctly/properly, you could for example change your association config to use a LEFT join by default in case applicable, or you could change the configuration for the containment on a per query basis (being it manually, or by using a custom finder):

$this->paginate = [
    'contain' => [
        'Customers',
        'ProformaStates',
        'ItemProformas' => [
            'ItemDeliveryNotes' => [
                'DeliveryNotes' => [
                    'joinType' => \Cake\Database\Query::JOIN_TYPE_LEFT,
                ],
            ],
        ],
    ],
];

Changing the fetching strategy for ItemDeliveryNotes could work too (though it might be quite taxing depending on the amount of records), ie using the select strategy instead of the join strategy, then the associated ItemDeliveryNotes records are being retrieved in a separate query, and thus won't affect retrieval of ItemProformas:

$this->paginate = [
    'contain' => [
        'Customers',
        'ProformaStates',
        'ItemProformas' => [
            'ItemDeliveryNotes' => [
                'strategy' => \Cake\ORM\Association::STRATEGY_SELECT,
                'DeliveryNotes',
            ],
        ],
    ],
];

Upvotes: 1

Related Questions