Mysql Relationships: Query Problem

Question

I have the following scenario

I need to get all the consulta_tipos with consulta_itens aligned and mark those that are in cliente_consulta_tipo and cliente_consulta_itens, also need to seek the fornecedores of consulta_itens and mark them in accordance with cliente_consulta_itens.

Practically speaking: ConsultaTipo: The has ConsultaItem: 1 and 2. The customer has available only the ConsultaItem: 2, which has the Fornecedores X and Y.

I’ve tried every way I can to perform this search, but I didn’t succeed. In time: I’m programming in PHP (Cakephp).

Answer 1

Take a look at the Cakephp documentation for associations between Models (I put the 2.x version because I believe it is your case) and see the examples of uses of Models associations, as well as the use of the attribute Recursive to be able to bring all table items that are associated.

Example

Model:

Example n-n relationship

Model Table1:

<?php
App::uses('AppModel', 'Model');
class Tabela1 extends AppModel {
    public $useTable = 'tabela_1';
    public $hasAndBelongsToMany = array(
      'Tabela2' =>
        array(
          'className' => 'Tabela2',
          'joinTable' => 'tabela_relacionamento_1_2',
          'foreignKey' => 'tabela_1_id',
          'associationForeignKey' => 'tabela_2_id'
        ),
    );

    public function getAll(){
      //Essa função irá retornar todos os itens da tabela_1 e também
      //retornar os os itens que estão associados a cada um dos itens.
      //Ex: $allItens = array(0 => array(
      //  'Tabela1' => array('id' => 1, 'att1' => 'atributo1', att2 => 'atributo2'),
      //  'Tabela2 => array('id' => 1, 'attr1' => 'attributo1', 'attr2' => 'attributo2)
      //));
      $allItens = $this->find('all',
        'recursive' => 2
      );
      return $allItens;
    }
}

Model Table2:

<?php
App::uses('AppModel', 'Model');
class Tabela2 extends AppModel {
    public $useTable = 'tabela_2';
    public $hasAndBelongsToMany = array(
      'Tabela1' =>
        array(
          'className' => 'Tabela1',
          'joinTable' => 'tabela_relacionamento_1_2',
          'foreignKey' => 'tabela_2_id',
          'associationForeignKey' => 'tabela_1_id'
        ),
    );
}

Relationship 1-n

Model Table3:

<?php
App::uses('AppModel', 'Model');
class Tabela3 extends AppModel {
    public $useTable = 'tabela_3';
    public $hasMany = array(
        'Tabela4' => array(
            'className'    => 'Tabela4',
            'foreignKey'   => 'tabela_3_id'
        )
      );
}

Model Table4:

<?php
App::uses('AppModel', 'Model');
class Tabela2 extends AppModel {
    public $useTable = 'tabela_4';
    public $belongsTo = array(
        'Tabela3' => array(
            'className'    => 'Tabela3',
            'foreignKey'   => 'tabela_3_id'
        )
    );

    public function getAll(){
      //Essa função irá retornar todos os itens da *tabela_3* e também
      //retornar os os itens que estão associados a cada um dos itens.
      //Ex: $allItens = array(0 => array(
      //  'Tabela4' => array('id' => 1, 'attribute1' => 'att1', attribute2 => 'att2'),
      //  'Tabela3 => array('id' => 1, 'atributo1' => 'at1', 'atributo2' => 'at2)
      //));
      $allItens = $this->find('all',
        'recursive' => 2
      );
      return $allItens;
    }
}