+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Model-View-Controller (https://forum.codeigniter.com/forumdisplay.php?fid=10)
+--- Thread: CI Builder Question (/showthread.php?tid=90501)
CI Builder Question - Tomasiomat - 03-27-2024
Hello together,
I am at a loss. The following query over three tables gives me the following desired result.
Code:
SELECT
objekte.obj_strasse,
objekte.obj_plz,
objekte.obj_ort,
einheiten_art.einh_art_kuerz,
einheiten.einh_qm,
einheiten.einh_id
FROM
objekte
INNER JOIN einheiten ON einheiten.obj_id = objekte.obj_id
INNER JOIN einheiten_art ON einheiten_art.einh_art_id = einheiten.einh_art_id
gives the following result:
Silberstr. 5, 30655, Hannover, EG, 70, 1
Silberstr. 5 30655, Hannover DG, 80, 3
Parkstrasse 7 30175, Hannover EG, 140, 4
and that is correct so far.What is the corresponding implementation in CI-query builder?
PHP Code:
$all_obj = $this->builder->select('objekte.obj_strasse,
objekte.obj_id,
objekte.obj_plz,
objekte.obj_ort,
einheiten_art.einh_art_kuerz,
einheiten.obj_id,
einheiten.einh_qm');
$all_obj = $this->builder->join('einheiten', 'einheiten.obj_id = objekte.obj_id');
$all_obj = $this->builder->join('objekte', 'einheiten.obj_id = objekte.obj_id');
$all_obj = $this->builder->join('einheiten_art', 'einheiten_art.einh_art_id = einheiten.einh_art_id');
I always miss a field: 'Unknown column 'objekte.obj_id' in 'on clause'
Any help is appreciated.
Cheers, Thomas
PS: it works with a raw query
RE: CI Builder Question - Codinglander - 03-27-2024
(03-27-2024, 05:36 AM)Tomasiomat Wrote: Hello together,
I am at a loss. The following query over three tables gives me the following desired result.
Code:SELECT
objekte.obj_strasse,
objekte.obj_plz,
objekte.obj_ort,
einheiten_art.einh_art_kuerz,
einheiten.einh_qm,
einheiten.einh_id
FROM
objekte
INNER JOIN einheiten ON einheiten.obj_id = objekte.obj_id
INNER JOIN einheiten_art ON einheiten_art.einh_art_id = einheiten.einh_art_id
gives the following result:
Silberstr. 5, 30655, Hannover, EG, 70, 1
Silberstr. 5 30655, Hannover DG, 80, 3
Parkstrasse 7 30175, Hannover EG, 140, 4
and that is correct so far.
What is the corresponding implementation in CI-query builder?
PHP Code:$all_obj = $this->builder->select('objekte.obj_strasse,
objekte.obj_id,
objekte.obj_plz,
objekte.obj_ort,
einheiten_art.einh_art_kuerz,
einheiten.obj_id,
einheiten.einh_qm');
$all_obj = $this->builder->join('einheiten', 'einheiten.obj_id = objekte.obj_id');
$all_obj = $this->builder->join('objekte', 'einheiten.obj_id = objekte.obj_id');
$all_obj = $this->builder->join('einheiten_art', 'einheiten_art.einh_art_id = einheiten.einh_art_id');
I always miss a field: 'Unknown column 'objekte.obj_id' in 'on clause'
Any help is appreciated.
Cheers, Thomas
PS: it works with a raw query
Hello Thomas.
Where do you use the code ? In a controller? If yes, you should set the table first before running the builder. I see, you want to use the DOT-notation, but easier is set the "main" table and use the DOT-notation in the join-clause.
Greetings...
Kigh
RE: CI Builder Question - Tomasiomat - 03-27-2024
Hi Kai,
I missed this hint for the Builder class:
"As shown earlier, the FROM portion of your query can be specified in the $db->table() method.
Additional calls to from() will add more tables to the FROM portion of your query."
Now it works :-)
Cheers, Thomas
RE: CI Builder Question - InsiteFX - 03-27-2024
Multiple method calls can be made if you need several joins in one query.
If you need a specific type of [b]JOIN[/b] you can specify it via the third parameter of the method. Options are:
left, right, outer, inner, left outer, and right outer.
PHP Code:
$builder->join('comments', 'comments.id = blogs.id', 'left');
// Produces: LEFT JOIN comments ON comments.id = blogs.id