MySQL Relationships: Query Problem

Question

MySQL Relationships: Query Problem

Navigation

#1 by (1 votes)

0

I have the following scenario

I need to search all consulta_tipos with consulta_itens aligned and check the ones in cliente_consulta_tipo and cliente_consulta_itens , in addition I need to search the fornecedores of consulta_itens and mark them according to the cliente_consulta_itens .

In practical terms: ConsultaTipo : A has ConsultaItem : 1 and 2. The client has only ConsultaItem : 2, which has Fornecedores X and Y.

I have tried every way to accomplish this search, but I have not been successful. In time: I'm programming in PHP (CakePHP).

php mysql cakephp-2

asked by anonymous 04.02.2016 / 20:44

1 answer

Protecting the php code [closed] Angular Links

score 1 · Accepted Answer

Take a look at the CakePHP documentation for the Model associations > (I put the 2.x version as I believe it to be your case) and see examples of uses of Model associations, as well as the use of the Recursive to be able to bring all items of tables that are associated.

Example

Template:

Relationship example n-n

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'
        ),
    );
}

1-n relationship

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;
    }
}