Replace element in array instead of making new one with join()

Question

I have two tables, one has two foreign keys, and the other has ids and the corrosponding subject. I want to make a function in my controller that returns an array with all the data from table 1, except for the subject ids which should be replaced with the actual subjects from the other table.

The problem I'm currently running into is that I join both subjects with the same element name in the array, resulting in one of them overriding the other. How can I make it so they are both different, or they just override the id in the array. I hope this makes sense, if not please ask for clarification in the comments.

My two tables

table 1: (klasgenoten)

naam	verplicht_profielvak1	verplicht_profielvak2	etc..
john	1	2

Table 2: (verplicht_profielvakken)

id	profielvak
1	math
2	science

My join code:

$builder = $db->table('klasgenoten'); // welke table gaat het over
        $builder->join('verplicht_profielvakken', 'klasgenoten.verplicht_profielvak1 = verplicht_profielvakken.id');
        $builder->select('klasgenoten.*, verplicht_profielvakken.profielvak');
        $query = $builder->get();
        $verplicht_profielvak1 = $query->getResultArray();

This returns an array with both the elements verplicht_profielvak1 and profielvak. The problem with this is that if I want to join multiple subjects, they override eachother. Example:

John from the table above in this case would only have science, because that one overrid math.

What I have tried:

Different types of joins, a foreach loop that replaces the values manually, rearranging my code, tried aliases but poorly, looked at several stackoverflow questions, all of which I could find were not applicable to my situation.

Does anybody know how to do this?

Answer 1

You have to use a join for every column of your klasgenoten table. Since the result will be stored in an array, multiple keys will be overwritten. You have to specify an alias for every join like this:

$builder = $db->table('klasgenoten'); // welke table gaat het over
// Note the alias of the table here
$builder->join('verplicht_profielvakken as vp1', 'klasgenoten.verplicht_profielvak1 = verplicht_profielvakken.id');
// Note the alias of the table here
$builder->join('verplicht_profielvakken as vp2', 'klasgenoten.verplicht_profielvak2 = verplicht_profielvakken.id');
// Select profielvak from each join with an unique alias
$builder->select('klasgenoten.*, vp1.profielvak as profilevak1, vp2.profielvak as profilevak2');
$query = $builder->get();
$verplicht_profielvak1 = $query->getResultArray();