CI Builder Question

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 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

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

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 


What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )