"Cannot convert value to string" when retrieving data (belongsToMany relationship)

Question

I'm using CakePHP 3.3.6 and MySQL 5.7.13.

I have these three tables in my database (amongst others) : collections, tags and the join table collections_tags.

collections Table

CREATE TABLE IF NOT EXISTS `database`.`collections` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `event_date` DATE NOT NULL,
  `url_slug` VARCHAR(45) NOT NULL,
  `status` TINYINT(1) NOT NULL DEFAULT 0,
  `user_id` INT UNSIGNED NOT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`, `user_id`),
  INDEX `fk_collections_users1_idx` (`user_id` ASC),
  CONSTRAINT `fk_collections_users1`
    FOREIGN KEY (`user_id`)
    REFERENCES `database`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
ENGINE = InnoDB

tags Table

CREATE TABLE IF NOT EXISTS `database`.`tags` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
  `created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`))
ENGINE = InnoDB

collections_tags Table

CREATE TABLE IF NOT EXISTS `database`.`collections_tags` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `collection_id` INT UNSIGNED NOT NULL,
  `tag_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`id`, `collection_id`, `tag_id`),
  INDEX `fk_collections_has_tags_tags1_idx` (`tag_id` ASC),
  INDEX `fk_collections_has_tags_collections1_idx` (`collection_id` ASC),
  CONSTRAINT `fk_collections_has_tags_collections1`
    FOREIGN KEY (`collection_id`)
    REFERENCES `database`.`collections` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_collections_has_tags_tags1`
    FOREIGN KEY (`tag_id`)
    REFERENCES `database`.`tags` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB

in my Table\CollectionsTable.php:

public function initialize(array $config)
{
    # A collection hasMany Sets
    $this->hasMany('Sets', [
        'dependent' => True,
    ]);

    # A Collection belongsTo a User
    $this->belongsTo('Users');

    # A Collection belongsToMany Tags
    $this->belongsToMany('Tags');
}

in my Table\TagsTable.php:

public function initialize(array $config)
{
    # A Tag belongsToMany Collections
    $this->belongsToMany('Collections');
}

I can get all the Collections or all the Tags. It works. But if I try to get all the Collections with their associated tags, I have this error :

This error happens when I have this in my Collections controller :

class CollectionsController extends AppController
{
    public function index()
    {
        $this->set('collections', $this->Collections->find('all', ['contain' => ['Tags']]));
    }
}

and this in my Template\Collections\index.ctp :

<h1>Hi, this is the Collection > Index page.</h1>

<?php foreach ($collections as $collection): ?>
<p>test</p>
<?php endforeach; ?>

I have no idea why... I tried creating a Table\CollectionsTagsTable.php file, but it did not make a difference.

Thanks for your help

EDIT : I tried changing the DATETIME fields by TIMESTAMP, and the TINYINT by INT, it did not change anything.

Answer 1

Tested it locally with similar setup. Seems like your primary key index "user_id" in collections table causes the problem here. By removing it, problem is gone.

I don't actually have much knowledge of composite keys and their usage in CakePHP3, so maybe someone with more experience would be able to tell, why this is failing.

Answer 2

Try to change your composite primary key on the collections to just id field.