CakePHP searching multiple models

Question

I have a table called users and it is connected to coaches and clients.

I want to search in both users table and coaches table.

My tables look like this:

CREATE TABLE IF NOT EXISTS `team07`.`users` (
  `id` INT(11) NOT NULL,
  `email` VARCHAR(45) NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `usertype` CHAR(2) NOT NULL,
  `created` DATETIME NOT NULL,
  `modified` DATETIME NULL DEFAULT NULL,
  `firstname` VARCHAR(45) NOT NULL,
  `lastname` VARCHAR(45) NOT NULL,
  `phonenumber` VARCHAR(45) NOT NULL,
  `suburb` VARCHAR(45) NOT NULL,
  `state` VARCHAR(10) NOT NULL,
  `businessname` VARCHAR(45) NULL DEFAULT NULL,
  `image` BLOB NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `email_UNIQUE` (`email` ASC))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

And also

CREATE TABLE IF NOT EXISTS `team07`.`coaches` (
  `id` INT NOT NULL,
  `coachid` INT NOT NULL,
  `appstate` INT NOT NULL,
  `bio` VARCHAR(2048) NULL,
  `price` INT NULL,
  PRIMARY KEY (`id`),
  INDEX `COACH_INFOFK_idx` (`coachid` ASC),
  UNIQUE INDEX `coachid_UNIQUE` (`coachid` ASC),
  CONSTRAINT `COACH_INFOFK`
    FOREIGN KEY (`coachid`)
    REFERENCES `team07`.`users` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

This is what the code I am doing looks like:

  public function review()
    {
      //Select all users which are a Coach AND have appstate = 1 (Just signed up, in review)     
      $users = $this->paginate($this->Users);
      $users = $this->Users->find('all')->where(['Users.usertype'=>'CO', 'Coaches.appstate'=>'1']);

      $this->set(compact('users'));
      $this->set('_serialize', ['users']);
    }

However I am having issues because my page responds with

Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'Coaches.appstate' in 'where clause'

How do I fix this?

Arie B. Arie B. · Accepted Answer · 2017-05-15T12:00:48

If the association is understood by cake, you can use contain to load it: https://book.cakephp.org/3.0/en/orm/query-builder.html#loading-associations

So something like this:

public function review()
{
    //Select all users which are a Coach AND have appstate = 1 (Just signed up, in review)     
    $users = $this->paginate($this->Users);
    $users = $this->Users->find('all')->contain(['Coaches'])->where(['Users.usertype'=>'CO', 'Coaches.appstate'=>'1']);

    $this->set(compact('users'));
    $this->set('_serialize', ['users']);
}

CakePHP searching multiple models

1 Answers