Problem with Many to Many

[tontof]

I have some problems using your README example.

First, I guess it should be:
$movies = new \Maphper\Maphper(new \Maphper\Datasource\Database($pdo, 'movie', 'id'));
instead of:
$movies = new \Maphper\Maphper(new \Maphper\Datasource\Database($pdo, 'actor', 'id'));

Then, I have to add

$movies[] = $movie1;
$movies[] = $movie2;

before using

$actor->movies[] = $movie1;
$actor->movies[] = $movie2;

otherwise it does not add movie to actor.
I don't know if this is an error in README or an unexpected behaviour of Maphper.

Finally, if I add editmode and the table does not exists

$actors = new \Maphper\Maphper(new \Maphper\DataSource\Database($pdo, 'actor', 'id', ['editmode' => true]));
$movies = new \Maphper\Maphper(new \Maphper\DataSource\Database($pdo, 'movie', 'id', ['editmode' => true]));
$cast = new \Maphper\Maphper(new \Maphper\Datasource\Database($pdo, 'cast', ['movieId', 'actorId'], ['editmode' => true]));

It does not save the second movie and actor correctly (with the above correction adding manually the movies).
My final dumb is:

CREATE TABLE "actor" (id INTEGER NOT NULL, `name` VARCHAR(255), PRIMARY KEY(id));
INSERT INTO "actor" VALUES(123,'Samuel L. Jackson');
CREATE TABLE "movie" (id INTEGER, `title` VARCHAR(255), PRIMARY KEY(id));
INSERT INTO "movie" VALUES(1,'Pulp Fiction');
CREATE TABLE "cast" (movieId VARCHAR(255) NOT NULL, actorId INTEGER NOT NULL, PRIMARY KEY(movieId, actorId));
INSERT INTO "cast" VALUES('1',123);
INSERT INTO "cast" VALUES('1',124);

Snakes on a Plane is not present and John Travolta is missing too except on cast with the 124 that appears

[tontof]

I forgot to point out I was using SqliteAdapter... Maybe that's the problem. I will try to check with Mysql.

[tontof]

I've tried with mysql and I've got different errors:
without

$movies[] = $movie1;
$movies[] = $movie2;

The movie table is not created but actor and cast tables are correctly saved.

Adding above lines, actor and movie tables are correctly saved but cast table is partially saved as I've got this PDO error:

Fatal error</b>:  Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1048 Column 'movieId' cannot be null' in /var/www/html/maphper/datasource/mysqladapter.php:83

cast table looks like:

+---------+---------+
| movieId | actorId |
+---------+---------+
| 1       |     123 |
| 2       |     123 |
+---------+---------+

For both SQLite and MySQL, it works correctly with the 2 added lines and without editmode.

For testing, I use this php file based on README example:

<?php
include('tests/autoload.php');

// Add a slash to comment out next line and use SQLite
/* For SQLITE
$pdo = new PDO('sqlite:test.db');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->query('DROP TABLE IF EXISTS actor');
$pdo->query('DROP TABLE IF EXISTS movie');
$pdo->query('DROP TABLE IF EXISTS cast');
$pdo->query('CREATE TABLE "actor" (id INTEGER NOT NULL, `name` VARCHAR(255), PRIMARY KEY(id));');
$pdo->query('CREATE TABLE "movie" (id INTEGER, `title` VARCHAR(255), PRIMARY KEY(id));');
$pdo->query('CREATE TABLE "cast" (movieId VARCHAR(255) NOT NULL, actorId INTEGER NOT NULL, PRIMARY KEY(movieId, actorId));');
// */

// Add a slash to comment out next line and use MySQL
/* For MySQL
$pdo = new PDO('mysql:dbname=maphpertest;host=127.0.0.1', 'root', '');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->query('DROP TABLE IF EXISTS actor');
$pdo->query('DROP TABLE IF EXISTS movie');
$pdo->query('DROP TABLE IF EXISTS cast');
$pdo->query('CREATE TABLE `actor` (`id` int(11) NOT NULL, `name` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;');
$pdo->query('CREATE TABLE `movie` (`id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `id` (`id`), KEY `title` (`title`)) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;');
$pdo->query('CREATE TABLE `cast` (`movieId` varchar(255) NOT NULL, `actorId` int(11) NOT NULL, PRIMARY KEY (`movieId`,`actorId`), KEY `actorid` (`actorId`), KEY `actorid_movieid` (`actorId`,`movieId`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;');
// */

//prevent any Date errors
date_default_timezone_set('Europe/London');
$actors = new \Maphper\Maphper(
    new \Maphper\DataSource\Database(
        $pdo, 'actor', 'id'
        //        ,['editmode' => true]
    )
);
$movies = new \Maphper\Maphper(
    new \Maphper\DataSource\Database(
        $pdo, 'movie', 'id'
        //        , ['editmode' => true]
    )
);
$cast = new \Maphper\Maphper(
    new \Maphper\Datasource\Database(
        $pdo, 'cast', ['movieId', 'actorId']
        //        , ['editmode' => true]
    )
);
$actors->addRelation('movies', new \Maphper\Relation\ManyMany($cast, $movies, 'id', 'movieId'));
$movies->addRelation('actors', new \Maphper\Relation\ManyMany($cast, $actors, 'id', 'actorId'));

$actor = new \stdclass;
$actor->id = 123;
$actor->name = 'Samuel L. Jackson';

//save the actor
$actors[] = $actor;

//now add some movies to the actor
$movie1 = new \stdclass;
$movie1->title = 'Pulp Fiction';

// Without this line, it does not save $movie1 in movie table
$movies[] = $movie1;

$actor->movies[] = $movie1;

//now add some movies to the actor
$movie2 = new \stdclass;
$movie2->title = 'Snakes on a Plane';

// Without this line, it does not save $movie2 in movie table
$movies[] = $movie2;

$actor->movies[] = $movie2;

$actor = $actors[123];

echo $actor->name . ' was in the movies:' . "\n";

foreach ($actor->movies as $movie) {
    echo $movie->title . "\n";
}

$actor = new \stdclass;
$actor->id = 124;
$actor->name = 'John Travolta';
$actors[] = $actor;

//Find the movie 'Pulp Fiction and add it to John Travolta
$movie = $movies->filter(['title' =>'Pulp Fiction'])->item(0);
$actor->movies[] = $movie;

echo 'The actors in ' . $movie->title . ' are :' . "\n";
foreach ($movie->actors as $actor) {
    echo $actor->name . "\n";
}

[TRPB]

Thanks, I believe I've fixed this issue and another related one I had discovered previously and proved quite difficult to fix.

Previously this would not work:

$author = new stdclass;
$author->name = 'Tom';
$author->blogs = [];

$blog1 = new stdClass;
$blog1->title = 'Blog 1';
$author->blogs[] = $blog1;

$blog2 = new stdClass;
$blog2->title = 'Blog 2';
$author->blogs[] = $blog2;

$authors[] = $author;

Previously this would not give the correct results and the blogs would not be saved unless you saved the author prior to assigning the blogs:

$author = new stdclass;
$author->name = 'Tom';
$author->blogs = [];

$authors[] = $author;


$blog1 = new stdClass;
$blog1->title = 'Blog 1';
$author->blogs[] = $blog1;

$blog2 = new stdClass;
$blog2->title = 'Blog 2';
$author->blogs[] = $blog2;

I've changed the implementation so that the order of saving doesn't matter. Take a look at the tests testObjectGraphSaveMany and testObjectGraphSaveDeep I need to make a few more tweaks and have yet to test SQLite but it's 90% of the way there.

[TRPB]

SQLite is now fixed. There is still an issue with ManyMany when using auto-traversal (skipping the intermediate table)

[tontof]

I've tried with you're updated code but I've got this new error with sqliteadapter:

Fatal error: Call to a member function errorCode() on null in /var/www/vendor/level-2/maphper/maphper/datasource/database.php on line 177

It seems to work correctly with mysqladapter

I've got this error using very simple code:

<?php
  include("vendor/autoload.php");
  $pdo = new PDO('sqlite:test.db');
  $blogSource = new \Maphper\DataSource\Database($pdo, 'blog', 'id');
  $blogs = new \Maphper\Maphper($blogSource);
  $blog = new stdClass;

  $blog->title = 'My Blog Title';
  $blog->content = 'This is my first blog entry';

  //Store the blog using the next available ID
  $blogs[] = $blog;

  echo 'The new blog ID is :' . $blog->id;
  foreach ($blogs as $blog) {
    echo $blog->title . '<br />';
  }

The problem occurs when $blog is inserted into $blogs

However all tests run correctly for sqliteadapter...

PHPUnit 4.2.6 by Sebastian Bergmann.
Configuration read from /var/www/phpunit.xml
.............................................
Time: 1.34 minutes, Memory: 5.25Mb
OK (45 tests, 163 assertions)

BTW, I guess the test files needs modifications as for now, mysql driver is required for sqlite tests as pdo is constructed in construct and sqlite tests inherit from mysql one. I guess it's better to initialize pdo in setup function without calling parent ?

// for mysql
protected function setUp() {
     $this->pdo = new \PDO('mysql:dbname=maphpertest;host=127.0.0.1', 'u', '');
     $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
}

edit:

// for sqlite
protected function setUp() {
    $this->pdo = new PDO('sqlite:./test.db');
    $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}

[tontof]

I've found the problem

Fatal error: Call to a member function errorCode() on null

In the code, PDO errors are handle with exceptions. Thus, this line is needed in the example, in order to work correctly:

$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

For now, I guess everything is OK !

[TRPB]

Aha, well spotted! I'm going to re-open this as it's not desired behaviour. I fixed this for the MySQL wrapper but need to add the same for the SQLite version

[tontof]

No problem. What about the problem I raise about tests and the solution I've proposed with setUp function ?

[solleer]

I am still getting issues with ManyMany and I am using MySql

[solleer]

I figured it out. ManyMany doesn't work unless you set up both sides of the relation. So in the test if you comment out

$movies->addRelation('actors', new \Maphper\Relation\ManyMany($cast, $actors, 'aid', 'actorId'));

but leave

$actors->addRelation('movies', new \Maphper\Relation\ManyMany($cast, $movies, 'mid', 'movieId'));

Then you get this error

Fatal error: Cannot access empty property in C:\Users\Solle\OneDrive\Documents\Programing Stuff\Web development\serverRoot\GitHub\Maphper\maphper\relation\manymany.php on line 112

[solleer]

Could it be made that if the relation is not already added, then the ManyMany object adds the other relation.