morning, I’m newbie in CakePHP 3.x, I want to fetch data with inner join two tables
RulesSigns table
and
ForwardsSigns table
below content tables in MySQL
RulesSigns
-----------------------------------------------------
ID | rule_id | sign_id | forwards_signs_id |
----|-------------|-----------|---------------------|
12 | 4 | 28 | 5 |
15 | 5 | 2 | 1 |
20 | 5 | 3 | 1 |
21 | 5 | 5 | 1 |
-----------------------------------------------------
ForwardsSigns
----------------------------------
ID | forward_id | sign_id |
----|----------------|-----------|
1 | 1 | 2 |
2 | 1 | 3 |
3 | 1 | 5 |
----------------------------------
RulesSigns Table
<?php
namespace App\Model\Table;
use Cake\ORM\Query;
use Cake\ORM\RulesChecker;
use Cake\ORM\Table;
use Cake\Validation\Validator;
class RulesSignsTable extends Table
{
public function initialize(array $config)
{
parent::initialize($config);
$this->setTable('rules_signs');
$this->setDisplayField('ID');
$this->setPrimaryKey('ID');
//compare field Sign
$this->belongsTo('Signs', [
'foreignKey' => 'sign_id',
'joinType' => 'INNER'
]);
}
}
?>
RulesSigns Controller
<?php
namespace App\Controller;
use App\Controller\AppController;
class RulesSignsController extends AppController
{
public function getsign()
{
$ruleSignsEntity = $this->RulesSigns->find('all')
->where(['forwads_sign_id' => $this->request->data['sign_id']])
->contain(['ForwardsSigns'])
->first();
//now content RulesSign table fill Sign table
$signGejala = $ruleSignsEntity->ForwardsSigns->sign_id;
$this->set('signGejala', $signGejala);
}
}
getsign.ctp
<?php if (!empty($signGejala->sign_id)): ?>
<table>
<tr><td><h4><strong>query RulesSign ke Gejala</strong></h4></td></tr>
<tr>
<th><?= __('Gejala') ?></th>
</tr>
<?php foreach ($signGejala as $sign): ?>
<tr>
<td><?= h($sign->sign_id) ?></td>
</tr>
<?php endforeach; ?>
</table>
<?php endif; ?>
the output inner join that I want is below
RulesSign table ForwardsSign table
----------------------------------------------------------------------------------
ID | rule_id | sign_id | forwards_signs_id | ID | sign_id |
----|-------------|-----------|---------------------|------------|----------------
| | | | | |
15 | 5 | 2 | 1 | 1 | 2 |
20 | 5 | 3 | 1 | 2 | 3 |
21 | 5 | 5 | 1 | 3 | 5 |
---------------------------------------------------------------------------------
the error messages shown here
Notice (8): Undefined index: sign_id [APP/Controller\RulesSignsController.php, line 82]
Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'forwads_sign_id' in 'where clause'
SQL Query:
SELECT RulesSigns.ID AS `RulesSigns__ID`, RulesSigns.rule_id AS `RulesSigns__rule_id`, RulesSigns.sign_id AS `RulesSigns__sign_id`, RulesSigns.forwards_sign_id AS `RulesSigns__forwards_sign_id`, ForwardsSigns.ID AS `ForwardsSigns__ID`, ForwardsSigns.forward_id AS `ForwardsSigns__forward_id`, ForwardsSigns.sign_id AS `ForwardsSigns__sign_id` FROM rules_signs RulesSigns LEFT JOIN forwards_signs ForwardsSigns ON ForwardsSigns.ID = (RulesSigns.forwards_sign_id) WHERE forwads_sign_id = :c0 LIMIT 1
I hope someone help me to fix join inner two tables,
thanks
