Join INNER field reference other table doesn't show

Need Help
1

Morning,

I have 2 tables

Countries table
+------+-------------+
|  ID  |   Negara    |
+------+-------------+
|   1  |  Indonesia  |
|   2  |  Malaysia   |
+------+-------------+

Users Table
+------+-----------------+------------+
|  ID  |    countrie_id  |     Nama   |
+------+-----------------+------------+
|   1   |         1      |      Arif  |
|   2   |         1      |      Jono  |
|   3   |         2      |    Mahmud  |
|   4   |         2      |     Abas   |
+-------+----------------+------------+

with table method below

Table countries
$this->hasMany('Users', [
		'foreignKey' => 'countrie_id'
]);

Table users
$this->belongsTo('Countries', [
      'foreignKey' => 'countrie_id'
]);

I want to join 2 tables with type ‘INNER’, below CountriesController

public function testajax()
	{
	  $queri = $this->Countries->find()
				->select(['ID', 'Negara'])
				->order(['Countries.Negara' => 'ASC'])
				->contain(['Users'])
				->join([
					'u' => [
						'table' => 'users',
						'type' => 'INNER',
						'conditions' => array('u.countrie_id = countries.id')
					]
				]);
		$this->set('queri', $queri);	
		//dd($queri->sql());
		//debug($queri);
	}

and inside testajax.ctp below this

<?php foreach ($queri as $countrie): ?>
  <tr>
	<td><?= h($countrie->ID)?></td>
	<td><?= h($countrie->Negara)?></td>
	<td><?= h($countrie->ID)?></td>
	<td><?= h($countrie->Nama)?></td>
   </tr>
<?php endforeach; ?>

The problem is reference field in table Users <td><?= h($countrie->Nama)?></td>
the result is null or empty.

I hope someone could help me, thanx

2

Nama is a field on the user record, not country. So $countrie->user->Nama should work.

3

but I get error display below

Trying to get property of non-object [ROOT\plugins\Nasa\src\Template\Countries\testajax.ctp, line 131

at the top CountriesController I was called use Cake\ORM\TableRegistry;

where’s part should be fix ?

4

below is debug to generate SQL

SELECT Countries.ID AS `Countries__ID`, Countries.Negara AS `Countries__Negara` FROM countries Countries INNER JOIN users u ON u.countrie_id = countries.id ORDER BY Countries.Negara ASC

how the part should be fix ?, thanx

5

Can you debug($countrie) to see what the actual data in there looks like? It might be different than expected due to the explicit join with a different alias.

6

the result after debug($countrie) is null

the cake version before 3.7.2 success join INNER,
while cake version curently used 3.7.4, the result is null

BugTestJoinINNER
BugTestJoinINNER1101×274 96.3 KB

7

That can’t be right. You have earlier in that table row got the results of $countrie->ID and $countrie->Negara, so $countrie is clearly not null.

8

null result inside $countrie->Nama which I called use join INNER from Users Table.

any idea more ?

9

Still awaiting the actual output of debug($countrie)

10

Thanx Zuluru, here’s below debug($countrie);

object(App\Model\Entity\Country) {

	'ID' => (int) 10,
	'Negara' => 'INDONESIA',
	'users' => [
		(int) 0 => object(App\Model\Entity\User) {

			'ID' => (int) 1,
			'countrie_id' => (int) 10,
			'province_id' => (int) 13,
			'region_id' => (int) 14,
			'district_id' => (int) 11,
			'subdistrict_id' => (int) 4,
			'Desa' => '',
			'Perumahan' => '',
			'Apartemen' => '',
			'Blok' => '',
			'Nomor' => '26',
			'RT' => '002',
			'RW' => '005',
			'postcode_id' => (int) 4,
			'Nama' => 'ARIS SETIAWAN',
			'Role' => 'Administrator',
			'photo' => null,
			'photo_dir' => null,
			'photo_size' => null,
			'photo_type' => null,
			'rek' => null,
			'rek_dir' => null,
			'rek_size' => null,
			'rek_type' => null,
			'Dibuat' => '15-04-2020 00:57:41',
			'Diubah' => null,
			'IP' => '',
			'[new]' => false,
			'[accessible]' => [
				'countrie_id' => true,
				'province_id' => true,
				'region_id' => true,
				'district_id' => true,
				'subdistrict_id' => true,
				'Desa' => true,
				'Perumahan' => true,
				'Apartemen' => true,
				'Jalan' => true,
				'Blok' => true,
				'Nomor' => true,
				'RT' => true,
				'RW' => true,
				'postcode_id' => true,
				'Handphone' => true,
				'Nama' => true,
				'Email' => true,
				'Password' => true,
				'Role' => true,
				'photo' => true,
				'photo_dir' => true,
				'photo_size' => true,
				'photo_type' => true,
				'rek' => true,
				'rek_dir' => true,
				'rek_size' => true,
				'rek_type' => true,
				'Dibuat' => true,
				'Diubah' => true,
				'IP' => true,
				'country' => true,
				'province' => true,
				'region' => true,
				'district' => true,
				'subdistrict' => true,
				'postcode' => true
			],
			'[dirty]' => [],
			'[original]' => [],
			'[virtual]' => [],
			'[hasErrors]' => false,
			'[errors]' => [],
			'[invalid]' => [],
			'[repository]' => 'Users'
		
		}
	],
	'[new]' => false,
	'[accessible]' => [
		'grupisland_id' => true,
		'Negara' => true,
		'grupisland' => true,
		'provinces' => true,
		'regions' => true,
		'districts' => true,
		'subdistricts' => true,
		'postcodes' => true,
		'users' => true
	],
	'[dirty]' => [],
	'[original]' => [],
	'[virtual]' => [],
	'[hasErrors]' => false,
	'[errors]' => [],
	'[invalid]' => [],
	'[repository]' => 'Countries'
}
11

So, you can see then that the last two cells of your row should have $countrie->users[0]->ID and $countrie->users[0]->Nama.

12

so, where’s the part should I to be fix…??,

13

Thanx @Zuluru that’s works join INNER, thanx for solution

FixSuccesJoin
FixSuccesJoin1099×232 85.7 KB

14

when you join a table, it doesn’t select the column automatically. Thats how sql works.

SELECT
  Countries.ID,
  Countries.Negara
FROM Countries
INNER JOIN Users ON Users.countrie_id = Countries.ID

is not the same as

SELECT
  Countries.ID,
  Countries.Negara,
  Users.ID,
  Users.Nama
FROM Countries
INNER JOIN Users ON Users.countrie_id = Countries.ID

Are not the same.
In your code you are using the first approach.

I suggest follow this approach

15

thanx @raul338 but still display bugs,
at the line $countrie->user[0]->Nama on the record joined result is looping only display my name, but inside table Users there’s 8 different name, could you give me any idea

16

How is you new query code like ?

17

You should probably clarify what it is that you’re actually trying to do. It’s hard to help you do what you want when the desired output is unknown to us.

18

thanx @Zuluru and @raul338 for give me support.

I will to expanding clarify that I mean. I have both different cake apps klinikucing and alseanasa.

  1. klinikucing CakeVersion 3.7.2 I have success test inner join, below this controller and TableMethod 
SignsController
public function bandingkan()
	{
		$query = $this->Signs->find('all')
				->order(['Signs.Gejala' => 'ASC'])
				->contain(['Forwards_signs'])
				->join([
					'forwards_signs' => [
						'table' => 'Forwards_signs',
						'type' => 'INNER',
						'conditions' => array('Forwards_signs.sign_id = signs.id')
					]
				]);		
		$this->set('query', $query);
  }

TableMethod SignsTable.php
$this->belongsToMany('Forwards', [
			'foreignKey' => 'sign_id',
			'targetForeignKey' => 'forward_id',
			'joinTable' => 'forwards_signs'
		]);

TableMethod ForwardsSigns.php
$this->belongsToMany('Signs', [
            'foreignKey' => 'forward_id',
            'targetForeignKey' => 'sign_id',
            'joinTable' => 'forwards_signs'
        ]);

display bandingkan.ctp
<?php foreach ($query as $sign): ?>
  <tr>
    <td><?= h($sign->ID) ?></td>
    <td><?= h($sign->Signno) ?></td>
    <td><?= h($sign->Gejala) ?></td>
    <td><?= h($sign->ID) ?></td>
    <td><?= h($sign->Gejala) ?></td>
   </tr>

the result is success join inner

join_inner_berhasil_signs
join_inner_berhasil_signs1110×490 193 KB

  1. alseanasa CakeVersion 3.7.4 there’s failed to display result join inner, below this controller and TableMethod 
CountriesController
public function testajax()
	{
	  //join INNER
	    $queri = $this->Countries->find()
					->select(['ID', 'Negara'])
					->order(['Countries.Negara' => 'ASC'])
					->contain(['Users'])
					->join([
						'u' => [
							'table' => 'users',
							'type' => 'INNER',
							'conditions' => array('u.countrie_id = countries.id')
						]
					]);
					
		$this->set('queri', $queri);
    }

TableMethod Countries
$this->hasMany('Users', [
	'foreignKey' => 'countrie_id'
  ]);

TableMethod Users
$this->belongsTo('Countries', [
     'foreignKey' => 'countrie_id'
 ]);

display testajax.ctp
<?php foreach ($queri as $countrie): ?>
  <tr>
   <td><?= h($countrie->ID) ?></td>
   <td><?= h($countrie->Negara) ?></td>
   <td><?= h($countrie->users[0]->ID) ?></td>
   <td><?= h($countrie->users[0]->Nama) ?></td>
   </tr>
<?php endforeach; ?>

the result inner join below

bugs_join_inner_alseanasa
bugs_join_inner_alseanasa1098×437 164 KB

  1. conclusion, the problem is below, while I used
h($countrie->users[0]->ID)
h($countrie->users[0]->Nama)

the same name is showing above picture no 2. While I checked accessed by http://localhost/alseanasa/countries/view/10 there’s 12 records each different names

ViewCountriesAlseanasa
ViewCountriesAlseanasa1102×379 158 KB

I think above is clearly and I hope any idea