CODEWITHSUNDEEP
phpmysqlcodeigniterjoin
Canser Yanbakan
Canser Yanbakan

Reputation: 3870

Joining 4 tables with Mysql and Codeigniter

I have 4 tables, that's all in a relationship, yes they love each other :p

I want to join these 4 tables in one query, here is my Codeigniter code to do this;

function list_products($limit = 10, $offset = 0, $category)
{
    $this->db->select('p.url, p.name, p.title, i.image');
    $this->db->from('products p');
    $this->db->join('product_categories c','c.id = r.category_id',
                    'left');
    $this->db->join('product_category_relations r',
                    'r.category_id = c.id','left');
    $this->db->join('product_images i',
                    'p.id = i.product_id AND i.default = 1','left');
    $this->db->where('c.url',$this->category_url.$category);
    $this->db->limit($limit,$offset);
    return $this->db->get()->result();
}

When i execute this function / query, the result is an error about defining "r".

Unknown column 'cms_r.category_id' in 'on clause'

The query:

SELECT p.url, p.name, p.title, i.image FROM (cms_products p) LEFT JOIN cms_product_categories c ON c.id = cms_r.category_id LEFT JOIN cms_product_category_relations r ON r.category_id = c.id LEFT JOIN cms_product_images i ON p.id = i.product_id AND i.default = 1 WHERE c.url = 'kategori/yilbasi' LIMIT 12

Can you help me?

Upvotes: 2

Views: 2412

Answers (2)

Kundan Prasad
Kundan Prasad

Reputation: 576

@Edward Ruchevits your query does not join because of r.category_id define after joining of product_category_relations r, you need Relationships between products and product_categories then your query something be like

$this->db->join('product_categories c','c.product_id = p.id','left');

Upvotes: 0

Edward Ruchevits
Edward Ruchevits

Reputation: 6696

You assign r to be similar to product_category_relations after you use it.

Here you write:

$this->db->join('product_categories c','c.id = r.category_id','left');

But what is r? You answer this question only in the next statement.

$this->db->join('product_category_relations r','r.category_id = c.id','left');

Provide sample SQL Create script, if you want concrete answer.

Upvotes: 0

Related Questions