codeigniter join same table multiple values [duplicate]

Question

i need some codeigniter 3 help from you friends. its like 10 years ago when i did some more complex querys and my noobish JOIN-trys just gave me errors and questionmarks for hours.

lets say i have a mysql table covers

id, text, bgcolor_id, color_id
example : 1, "nice headline", 55, 88

and a table colors

id, value, name
example : 55, #FF0000, "red"
example : 88, #000000, "black"

how to "link" based on bgcolor_id, color_id in table covers

cover.bgcolor_id -> 
color.value AS bgcolorvalue
color.name AS bgcolorname

cover.color_id ->
color.value AS colorvalue
color.name AS colorname

my codeigniter model

public function list(){
$query = $this->db->query('SELECT * FROM covers ORDER BY id DESC');
return $query->result_array();
}

public function get($id){
$query = $this->db->query('SELECT * FROM covers WHERE id = ' . $id);
return $query->row();
}

M Khalid Junaid · Accepted Answer · 2017-10-19 04:59:58Z

3

Join twice your colors table

select c.*,c1.name bgcolorname,
c1.value bgcolorvalue,
c2.name colorname, 
c2.value colorvalue
from covers c
join colors c1 on c.bgcolor_id = c1.id
join colors c2 on c.color_id = c2.id

DEMO

answered Oct 19, 2017 at 4:59

M Khalid Junaid

64.6k10 gold badges93 silver badges120 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

scco Over a year ago

oh dude. men. im impressed - it works. i try to understand your query so hard, my brain is already smoking. now i think if i try to recreate this in "active records style" from codeigniter... hope i will get it after sleeping...

M Khalid Junaid Over a year ago

@scco yup give it a try with active record if you are unable to accomplish this then let me know i will add the active record version also

scco Over a year ago

hey m khalid junaid, do you know how i could advance your sql solution : when somebody deletes the color row so there is no id - the complete query will not appear or give errors. so is there a way to just "optional" join the colors when there ARE colors - otherwise just have empty fields ?

Gerson E. Aguirre · Accepted Answer · 2017-10-19 07:15:38Z

0

I strongly recommend you to use the query builder Reference

You could to something like.

$cv = 'covers';
        $cl1 = 'colors';
        $cl2 = 'colors';
        $get = array(
            $cv.'.id',
            $cv.'.text',
            $cv.'.bgcolor_id',
            $cv.'.color_id',
            $cl1.'.value as bgcolorvalue',
            $cl1.'.name as bgcolorname',
            $cl1.'.value as colorvalue',
            $cl1.'.name as colorname'
        );
        $this->db->select($get);
        $this->db->from($cv);
        $this->db->where($cv.'.id', 1);
        $this->db->join($cl1, $cv.'.bgcolor_id = ' . $cl1.'.id');
        $this->db->join($cl2, $cv.'.color_id = ' . $cl2.'.id');
        $result = $this->db->get()->result();

let me know if this works

answered Oct 19, 2017 at 7:15

Gerson E. Aguirre

8535 silver badges11 bronze badges

1 Comment

scco Over a year ago

hey hey exe - cause you wana know if it works - it produces : Not unique table/alias: 'colors' SELECT covers.id, covers.text, covers.bgcolor_id, covers.color_id, colors.value as bgcolorvalue, colors.name as bgcolorname, colors.value as colorvalue, colors.name as colorname FROM covers JOIN colors ON covers.bgcolor_id = colors.id JOIN colors ON covers.color_id = colors.id WHERE covers.id = 1

Haritsinh Gohil · Accepted Answer · 2018-09-19 10:48:54Z

0

As i have understood your question right then you want to join on colors table twice for bgcolor_id and color_id, so here i am providing solution for your question let me know if it works or something wrong with the solutuion, thanks.

 public function list(){
     $query = $this->db->select("covers.*, bg.value AS bgcolorvalue bg.name AS 
                            bgcolorname, colors.value AS colorvalue
                            colors.name AS colorname")
                 ->join("colors", "colors.id = covers.color_id", "left")
                 ->join("colors as bg", "bg.id = covers.bgcolor_id", "left")
                 ->get("covers")->result(); 
     return $query->result_array();
 }

answered Sep 19, 2018 at 10:48

Haritsinh Gohil

6,31053 silver badges52 bronze badges

Collectives™ on Stack Overflow

codeigniter join same table multiple values [duplicate]

3 Answers 3

3 Comments

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Linked

Related