BelongsToMany configuration (hasMany through association)


#1

Three related tables are (1) products, (2) offers, and (3) offer_rows.

-- SQL

create table products(
	id serial not null auto_increment primary key,
	...
);
create table offers(
	id serial not null auto_increment primary key,
	...
);
create table offer_rows(
	id serial not null auto_increment primary key,

	product_id bigint(20) unsigned references products(id),
	offer_id bigint(20) unsigned not null references offers(id),

	...
);

Offer rows have some extra data in addition of being the associative table between products and offers, and thus I had configured them previously as:

(class OffersTable)
@property \Cake\ORM\Association\HasMany $OfferRows

// initialize
$this->hasMany('OfferRows', [
    'foreignKey' => 'offer_id'
]);

(class ProductsTable)
@property \Cake\ORM\Association\BelongsToMany $OfferRows

// initialize
$this->belongsToMany('OfferRows', [
    'foreignKey' => 'product_id',
    'joinType' => 'INNER',
]);


(class OfferRowsTable)
@property \Cake\ORM\Association\BelongsTo $Offers
@property \Cake\ORM\Association\HasOne $Products

// initialize
$this->belongsTo('Offers', [
       'foreignKey' => 'offer_id',
       'joinType' => 'INNER'
]);        
$this->hasOne('Products', [
	'className' => 'Products',
        'propertyName' => 'reference_product_obj',
        'foreignKey' => 'reference_product'        		
]);

Then I modified the association description to be:
(As to “why break working code”, this modification was due to an error when deleting entity, which reflected some error in association configuration and I figured out that it should be fixed and thus changed it accordingly.)

(class OffersTable)
@property \Cake\ORM\Association\BelongsToMany $Products

// initialize
$this->belongsToMany('Products', [
     'through' => 'OfferRows',
     'foreignKey' => 'offer_id',
     'joinType' => 'INNER',
     'joinTable' => 'offer_rows',
]);


(class ProductsTable)
@property \Cake\ORM\Association\BelongsToMany $Offers

// initialize
$this->belongsToMany('Offers', [
     'through' => 'OfferRows',
     'foreignKey' => 'product_id',
     'joinType' => 'INNER',
     'joinTable' => 'offer_rows',
]);
        

(class OfferRowsTable)
@property \Cake\ORM\Association\BelongsTo $Products
@property \Cake\ORM\Association\BelongsTo $Offers

// initialize
$this->belongsTo('Products', [
     'foreignKey' => 'product_id'
]);
$this->belongsTo('Offers', [
     'foreignKey' => 'offer_id',
     'joinType' => 'INNER'
]);

Used versions are:

aura/installer-default                1.0.0   Installs Aura packages using the Composer defaults.
aura/intl                             1.1.1   The Aura.Intl package provides internationalization (I18N) tools, specifically
cakephp/bake                          1.2.14  Bake plugin for CakePHP 3.0
cakephp/cakephp                       3.3.16  The CakePHP framework
cakephp/chronos                       1.1.2   A simple API extension for DateTime.
cakephp/debug_kit                     3.9.5   CakePHP Debug Kit
cakephp/migrations                    1.6.7   Database Migration plugin for CakePHP 3.0 based on Phinx
cakephp/plugin-installer              1.0.0   A composer installer for CakePHP 3.0+ plugins.
composer/ca-bundle                    1.0.7   Lets you find a path to the system CA bundle, and includes a fallback to the Mozilla CA bundle.
composer/composer                     1.4.2   Composer helps you declare, manage and install dependencies of PHP projects, ensuring you have the right stack everywhere.
composer/semver                       1.4.2   Semver library that offers utilities, version constraint parsing and validation.
composer/spdx-licenses                1.1.6   SPDX licenses list and validation library.
dnoegel/php-xdg-base-dir              0.1     implementation of xdg base directory specification for php
friendsofcake/cakepdf                 3.2.3   CakePHP plugin for creating and/or rendering Pdfs, several Pdf engines supported.
jakub-onderka/php-console-color       0.1     
jakub-onderka/php-console-highlighter v0.3.2  
jdorn/sql-formatter                   v1.2.17 a PHP SQL highlighting library
justinrainbow/json-schema             5.2.1   A library to validate a json schema.
mobiledetect/mobiledetectlib          2.8.25  Mobile_Detect is a lightweight PHP class for detecting mobile devices. It uses the User-Agent string combined with specific HTTP headers to detect the mo...
nikic/php-parser                      v3.0.6  A PHP parser written in PHP
psr/http-message                      1.0.1   Common interface for HTTP messages
psr/log                               1.0.2   Common interface for logging libraries
psy/psysh                             v0.8.8  An interactive shell for modern PHP.
ralouphie/mimey                       1.0.8   PHP package for converting file extensions to MIME types and vice versa.
riesenia/cakephp-duplicatable         v3.0.5  CakePHP ORM plugin for duplicating entities (including related entities)
robmorgan/phinx                       v0.6.5  Phinx makes it ridiculously easy to manage the database migrations for your PHP app.
seld/cli-prompt                       1.0.3   Allows you to prompt for user input on the command line, and optionally hide the characters they type
seld/jsonlint                         1.6.1   JSON Linter
seld/phar-utils                       1.0.1   PHAR file format utilities, for when PHP phars you up
symfony/config                        v3.3.2  Symfony Config Component
symfony/console                       v3.3.2  Symfony Console Component
symfony/debug                         v3.3.2  Symfony Debug Component
symfony/filesystem                    v3.3.2  Symfony Filesystem Component
symfony/finder                        v3.3.2  Symfony Finder Component
symfony/polyfill-mbstring             v1.4.0  Symfony polyfill for the Mbstring extension
symfony/process                       v3.3.2  Symfony Process Component
symfony/var-dumper                    v3.3.2  Symfony mechanism for exploring and dumping PHP variables
symfony/yaml                          v3.3.2  Symfony Yaml Component
zendframework/zend-diactoros          1.4.0   PSR HTTP Message implementations

How should I now access the relation data? Previously I just “linked” the offer_rows to offer (with the "contain" => ["OfferRows"]), and the offer had all the rows in “offer_rows”. Now one offer_row is accessible only through corresponding item in products-array (product."_joinData")).

// PREVIOUSLY retrieving the offer (in the Controller) was as simple as:
$offer = $this->Offers->get($id, [
      'contain' => ['OfferRows']
]);

// result was something like: offer => offer_rows [ 1 => row 1, 2 => row 2, ..]

NOW I must execute the following hand made function (from OffersTable) to make the same:

$offer = $this->get($id, [
     'contain' => ['Products'] // NB: this must be declared, or the below "this->OfferRows->find" fails!
]);
        
$offerRows = $this->OfferRows->find('all')->where(['offer_id' => $id])->toArray();
$offer->offer_rows = $offerRows;

return $offer;

I have tried to follow the instructions carefully from cakephp documentation, but because previously the offer rows data access was easy and now I must do some extra function calls to make the same I am suspecting I try to access/use the associative data in the wrong way.
(Furthermore, I am facing some problems with duplicatable plugin and have raised a question for possible issue there: https://github.com/riesenia/cakephp-duplicatable/issues/23).


#2

I updated the versions to the main post. Now the cakephp version is 3.3.16.

In addition to main post, I also wonder if I should use the “hasMany through” at all as the Offer can contain OfferRow which has null product_id.