"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.

Tuberculate

Whenever receiving errors, please always post the complete error message, that is, including the full stacktrace (ideally copied from the logs where it is available in a properly readable fashion), even if the problem might be obvious to people who are familiar with CakePHP! – Garamond

And provide the SQL that generated the error. – Statism

It is almost always "wrong" to have PRIMARY KEY (id, ...) when id is AUTO_INCREMENT. – Statism

Here are tips on a more performant many:many table schema. – Statism

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.

Agglutination

Hi! Indeed, removing the primary key solves the problem. And everything is working as expected. Thanks! However I would like to know what it changes really : what's the difference between setting the field as primary and not setting it as primary? – Knowlton

Answer 2

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

Microgamete

Hi! Indeed, removing the primary key solves the problem. And everything is working as expected. Thanks! However I would like to know what it changes really : what's the difference between setting the field as primary and not setting it as primary? – Knowlton

I do know honestly, but suspect the framework makes the composite key a string, but keeps single primary key an integer on php side. And somewhere on the PHP that string gets in the way. – Microgamete

Answer 3

You haven't set the primary key to be the composite of id and user_id in your CollectionsTable class.

// In initialize method of CollectionsTable
$this->primaryKey(['id', 'user_id']);

Eno

Thanks, this solution worked also. But as I do not need a composite primary key, I just deleted the user_id primary key (not the field) and it works. Thanks to you I know how to set a composite primary key in CakePHP :) – Knowlton

No problem, Cake handles composite keys quite nicely I think! – Eno

Answer 4

I have the same issue. Found the solution from one of the comments from Inzamam Malik. User table was missing identity column and having duplicate identity value which was returning array. Cleared duplicate records and set identity and primary key, that solved the problem.

Contrivance