CI4: JOIN gives duplicate (and more) results

I could use some help with a CodeIgniter 4 problem, and I hope there is a CI4 guru here who can help.

First some information about the setup of the website:
I have two mysql tablets (eenheden and statussen). The setup for these tables are:
Eenheden:

id (int, PRIMARY, AI)
roepnummer (vchar(6)
locatie (varchar(255))
status (int)
leden (varchar(255)
telefoon (varchar(255))

Example data:
id, roepnummer, locatie, status, leden, telefoon
1, ‘52-201’, ‘Centrale post’, 1, ‘Jan en henk’, ‘0612345678’
2, ‘52-24’, ‘Start/Finish’, 3, ‘Jolanda’, ‘0698765432’
3, ‘1597’, ‘Ambulance parkeer’, 2, ‘0645678923’

Statussen:

id (int, PRIMARY, AI)
status (varchar(255))
bgcolor (varchar(7))
txtcolor (varchar(7))

Example data:
id, status, bgcolor, txtcolor
1, ‘Beschikbaar’, ‘#FFFFFF’, ‘#000000
2, ‘Behandelen’, ‘#1d27ad’, ‘#FFFFFF
3, ‘Pauze’, ‘#FFFFFF’, ‘#959c97

I want to run this query:

Code:

SELECT e.id, e.roepnummer, e.locatie, e.telefoon, e.leden, statussen.status, statussen.bgcolor as bgcolor, statussen.txtcolor as txtcolor FROMeenhedenas e JOIN statussen ON e.status=statussen.id

I have the following code for that:

PHP Code:

$db = \Config\Database::connect();            
$builder = $db->table('eenheden');            
$builder->select('e.id, e.roepnummer, e.locatie, e.telefoon, e.leden, statussen.status as status, statussen.bgcolor as bgcolor, statussen.txtcolor as txtcolor');            
$builder->from('eenheden as e');            
$builder->join('statussen', 'e.status = statussen.id');                        
return $builder->get()->getResultArray(); 

Now I’m coming to the problem. I get far more results than I should. When the table Eenheden has only 1 row, I get 1 row as a result. When Eenheden has 2 rows, I get 4 results (2 results per row, with identical data). With 3 rows I get 9 results in my array (3 for each row in Eenheden). And so on.
Now, the query functions as expected when I write it out manually (as in the example above).

Am I missing something? Does CI4 add something to my query that makes this happen? Or does my code snippet not function as I think it functions? I’m at a loss. I have been wrecking my brain on this for nearly a week.

EDIT:

Managed to solve the problem with some help elsewhere. I removed the from line and changed ->table to:
$builder = $db->table(‘eenheden as e’);

Now it works as expected.

Sometimes one works on a problem so long (specially when new to a framework, as I am to CI in general) that you read over the solution. It was right there in the documentation. I hope someone else can learn from my mistake :wink:

Sponsor our Newsletter | Privacy Policy | Terms of Service