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 tags Table collections_tags Table in my TableCollectionsTable.php: in my TableTagsTable.php: I can get all the Collections or all the Tags. It works. But if I try to get all the Collections with their

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

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 TableCollectionsTable.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 TableTagsTable.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 TemplateCollectionsindex.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 TableCollectionsTagsTable.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

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.

Advertisement

Answer