Welcome Guest, Not a member yet? Register   Sign In
CI4 JOIN gives duplicate (and more) results
#1

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
FROM
`eenheden` as 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.
Reply
#2

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

Not is 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 ;-)
Reply




Theme © iAndrew 2016 - Forum software by © MyBB