ManyToMany CRUD example

Hi @all,

I am working on a ManyToMany relationship with models, controllers, forms and form elements. So what I want to archive is the following:

  1. Configure a ManyToMany relationship => DONE (I guess, see example below)
  2. CRUD model operations for ManyToMany relationships including validation => PROGRESS (Need help on this)
  3. Form handling for ManyToMany relationships with creating a form element which handles the relationship in a visual way including displaying error messages => PROGRESS (Need help on this)

I guess I've done the first step already. Here is the code I've written so far:

MySQL Tables

Competitions

CREATE TABLE `competitions` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Teams

CREATE TABLE `teams` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) COLLATE utf8_bin NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

CompetitionsTeams

CREATE TABLE `competitions_teams` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `competition_id` int(11) unsigned NOT NULL,
  `team_id` int(11) unsigned NOT NULL,
  `weight` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `competition_id` (`competition_id`),
  KEY `team_id` (`team_id`),
  CONSTRAINT `competitions_teams_fk_1` FOREIGN KEY (`competition_id`) REFERENCES `competitions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `competitions_teams_fk_2` FOREIGN KEY (`team_id`) REFERENCES `teams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Phalcon Models

Competition


namespace test\Models;

use Phalcon\Mvc\Model\Validator\PresenceOf,
    Phalcon\Mvc\Model\Validator\StringLength;

class Competition extends Model {

  public function getSource() {
    return "competitions";
  }

  public function initialize() {
    $this->hasManyToMany(
      "id", 
      "test\\Models\\CompetitionTeam", 
      "competition_id", 
      "team_id",
      "test\\Models\\Team",
      "id", 
      array('alias' => 'CompetitionTeam')
    );
  }

  public function validation() {
    $this->validate(new PresenceOf(array(
      "field"   => "title",
      'message' => 'The title is required.'
    )));
    $this->validate(new StringLength(array(
      "field"   => "title",
      'min'     => 2,
      'messageMinimum' => 'The title is too short.'
    )));

    return !$this->validationHasFailed();
  }
}

Team


namespace test\Models;

use Phalcon\Mvc\Model\Validator\PresenceOf,
    Phalcon\Mvc\Model\Validator\StringLength;

class Team extends Model {

  public function getSource() {
    return "teams";
  }

  public function initialize() {
    $this->hasManyToMany(
      "id", 
      "test\\Models\\CompetitionTeam", 
      "team_id", 
      "competition_id",
      "test\\Models\\Competition",
      "id", 
      array('alias' => 'CompetitionTeam')
    );
  }

  public function validation() {
    $this->validate(new PresenceOf(array(
      "field"   => "title",
      'message' => 'The title is required.'
    )));
    $this->validate(new StringLength(array(
      "field"   => "title",
      'min'     => 2,
      'messageMinimum' => 'The title is too short.'
    )));

    return !$this->validationHasFailed();
  }
}

CompetitionTeam


namespace test\Models;

class CompetitionTeam extends Model {

  public function getSource() {
    return "competitions_teams";
  }

  public function initialize() {
    $this->belongsTo("competition_id", "test\\Models\\Competition", "id", array('alias' => 'Competition'));
    $this->belongsTo("team_id", "test\\Models\\Team", "id", array('alias' => 'Team'));
  }

  public function validation() {
      $this->validate(new PresenceOf(array(
        "field"   => "competition_id",
        'message' => 'The competition is required.'
      )));

      $this->validate(new PresenceOf(array(
        "field"   => "team_id",
        'message' => 'The team is required.'
      )));

      $this->validate(new PresenceOf(array(
        "field"   => "weight",
        'message' => 'The weight is required.'
      )));

    return !$this->validationHasFailed();
  }
}

Competition Controller

namespace test\Controller;

use test\Models\Competition as Competition,
    test\Models\CompetitionTeam as CompetitionTeam;

class CompetitionsController extends Controller {

  public function testAction() {

    $testCompetition = new Competition();
    $testCompetition->title = 'HAS MANY TO MANY TEST';

    $CompetitionTeams = array();

    $testCompetitionTeam1 = new CompetitionTeam();
    $testCompetitionTeam1->team_id = 1; // There is already a team with the id 1 in the database
    $CompetitionTeams[] = $testCompetitionTeam1;

    $testCompetition->CompetitionTeam = $CompetitionTeams;

    if(!$testCompetition->save()) {
      foreach($testCompetition->getMessages() as $message) {
        $this->log($message->getMessage());
      }
    }
}

The CompetitionController is currently just for testing and does not have included the CRUD actions. In the database is currently only one team added which has the id 1. No relationships and competitions are sored.

Current Test Result

  1. Currently the competitions and competition teams relationship is not stored cause I got the following error: "competition_id is required". This is right cause the competition is not created right now. But this should be handled by phalcon itself, correct? If not what is the best way to integrate this behavior?

  2. Secondly the validation function of the relationship is not running. Cause I need to store extra fields in this relationship a validation should be done as well. How to handle this?

If you have further questions please feel free to ask.

Thanks for your help!

Michael

You can't store extra information in manyToMany relation. If You need to store weight, just change Competition manyToMany to:

public function initialize() {
    $this->hasMany(
      "id", 
      "test\\Models\\CompetitionTeam", 
      "competition_id", 
      array('alias' => 'CompetitionTeam') // I would change to competitionTeams, because it is array, not one object
    );
  }

And You have new model which store that: team A take part in competition B with weight C.

Hi Mateusz,

thanks for your reply. I've modified the code like you said and it is working mostly. Now I am at the validation of the HasMany-Items. But first here is the current code base (The MySQL structure is the same):

Phalcon Models

Competition

namespace test\Models;

use Phalcon\Mvc\Model\Validator\PresenceOf,
    Phalcon\Mvc\Model\Validator\StringLength;

class Competition extends Model {

  public function getSource() {
    return "competitions";
  }

  public function initialize() {
    $this->hasMany(
      "id", 
      "test\\Models\\CompetitionTeam", 
      "competition_id", 
      array('alias' => 'teams') // I changed to "teams" cause I already not that this is related to a competition
    );
  }

  public function validation() {
    $this->validate(new PresenceOf(array(
      "field"   => "co_title",
      'message' => 'The title is required.'
    )));
    $this->validate(new StringLength(array(
      "field"   => "co_title",
      'min'     => 2,
      'messageMinimum' => 'The title is too short.'
    )));
    $this->validate(new PresenceOf(array(
        "field"   => "co_weight",
        'message' => 'The weight is required.'
      )));

    return !$this->validationHasFailed();
  }
}

Team

namespace test\Models;

use Phalcon\Mvc\Model\Validator\PresenceOf,
    Phalcon\Mvc\Model\Validator\StringLength;

class Team extends Model {

  public function getSource() {
    return "teams";
  }

  public function initialize() {
    $this->hasMany(
      "id", 
      "test\\Models\\CompetitionTeam", 
      "team_id", 
      array('alias' => 'competitions')
    );
  }

  public function validation() {
    $this->validate(new PresenceOf(array(
      "field"   => "te_title",
      'message' => 'The title is required.'
    )));
    $this->validate(new StringLength(array(
      "field"   => "te_title",
      'min'     => 2,
      'messageMinimum' => 'The title is too short.'
    )));
    $this->validate(new PresenceOf(array(
        "field"   => "te_weight",
        'message' => 'The weight is required.'
      )));

    return !$this->validationHasFailed();
  }
}

CompetitionTeam (Hasn't changed)

namespace test\Models;

class CompetitionTeam extends Model {

  public function getSource() {
    return "competitions_teams";
  }

  public function initialize() {
    $this->belongsTo("competition_id", "test\\Models\\Competition", "id", array('alias' => 'Competition'));
    $this->belongsTo("team_id", "test\\Models\\Team", "id", array('alias' => 'Team'));
  }

  public function validation() {
      $this->validate(new PresenceOf(array(
        "field"   => "competition_id",
        'message' => 'The competition is required.'
      )));

      $this->validate(new PresenceOf(array(
        "field"   => "team_id",
        'message' => 'The team is required.'
      )));

      $this->validate(new PresenceOf(array(
        "field"   => "cote_weight",
        'message' => 'The weight is required.'
      )));

    return !$this->validationHasFailed();
  }
}

Competition Controller

namespace test\Controller;

use test\Models\Competition as Competition,
    test\Models\CompetitionTeam as CompetitionTeam;

class CompetitionsController extends Controller {

  public function testAction() {

    $testCompetition = new Competition();
    $testCompetition->title = 'HAS MANY TEST';

    $teams = array();

    $team1 = new CompetitionTeam();
    $team1->team_id = 1; // There is already a team with the id 1 in the database
    $team1->cote_weight = 1;
    $teams[] = $team1;

    $team2 = new CompetitionTeam();
    $team2->team_id = 2; // There is already a team with the id 2 in the database
    $teams[] = $team2;

    $testCompetition->teams = $teams;

    if(!$testCompetition->save()) {
      foreach($testCompetition->getMessages() as $message) {
        $this->log($message->getMessage());
      }
    }
}

Current Test Result

Actually the validation of the HasMany-Items is working and given me an error. The competition is not saved as expected cause one related item has a validation error (team2). When I have a look into the messages of the competition I see this error. But I am not able to detect which of the items has thrown the error. I need to know the item "index" cause I want to highlight the item in the rendered form. Any idea how to handle the validation of hasMany-related items correctly?

Thanks you, Michael