Unknown column (table prefix, backticks)

[dani0332]

I made a simple one-many relationship and encounter an error as follows:

Error Number: 1054

Unknown column 'tdz_`standard_design.dress_mode_id' in 'field list'

SELECT `tdz_``standard_design`.`dress_mode_id`, `tdz_``standard_design`.`price` FROM `tdz_standard_design` WHERE `tdz_standard_design`.`dress_mode_id` IN('1')

Filename: E:/xampp/htdocs/darzee/system/database/DB_driver.php

Line Number: 691

NOTES:

1. The tdz prefix has been set in application/config/development/database.php
2. I did try to mention the prefix in the string and dbprefix function of CI but didn't helped.

My models are: Dress_categories_m

class Dress_categories_m extends MY_Model
{
    public $rules = array(
        'name' => array(
            'field' => 'name',
            'label' => 'Category Name',
            'rules' => 'trim|required'
        ),
        'price' => array(
            'field' => 'price',
            'label' => 'Category Price',
            'rules' => 'trim'
        ),
    );

    /**
     * Dress_categories_m constructor.
     */
    public function __construct()
    {
        $this->table = $this->db->dbprefix('dress_categories');
        $this->primary_key = 'id';
        parent::__construct();

        $this->has_many['standard_designs'] = array(
            'foreign_model' => 'standard_design_m',
            'foreign_table' => 'standard_design',
            'foreign_key' => 'dress_mode_id',
            'local_key' => 'id'
        );
    }
}

Standard_design_m

class Standard_design_m extends MY_Model
{
    /**
     * Standard_design_m constructor.
     */
    public function __construct()
    {
        $this->table =  $this->db->dbprefix('standard_design');
        $this->primary_key = 'id';
        parent::__construct();

        $this->has_one['dress_categories'] = array(
            'foreign_model' => 'dress_categories_m',
            'foreign_table' => 'dress_categories',
            'foreign_key' => 'id',
            'local_key' => 'dress_mode_id'
        );
    }
}

I am writing this query in DressCategory Controller

public function getStdDesigns(){
            $this->load->model('Dress_categories_m', 'dc', TRUE);
            print_r($this->dc->fields('name')->with_standard_designs('fields:price')->get(1));

}

It works fine with remove 'fields:price' as : print_r($this->dc->fields('name')->with_standard_designs()->get(1));

However, I have speculated that these backticks are causing the issue: SELECTtdz_standard_design`.`dress_mode_id

I modify some code in MY_Model.php and resolved the issue, but I just wanted to make sure that this change that I made wont cause further issues in future, by asking you about this change. And perhaps you can give me a better solution.

In method join_temporary_results($data) I removed the highlighted backticks that resolved the issue.

[avenirer]

If you tell the model that the table name in the relation is "standard_design", it will take your word for it...

$this->has_many['standard_designs'] = array(
            'foreign_model' => 'standard_design_m',
            'foreign_table' => 'standard_design',
            'foreign_key' => 'dress_mode_id',
            'local_key' => 'id'
        );

So, even without those backticks, I don't think you will solve the problem.

Anyway, I had a problem in the past that made me put those backticks in there (don't ask me what problems... I don't remember), so I would have to take a deeper look at this problem.

[dani0332]

@avenirer Yes, my table name in fact is 'standard_design'.

If you're referring to $this->has_many['standard_designs'] , I think this standarddesigns is used for **`with*`** which could be anything, right?

Anyhow, I try removing the 's' converting from $this->has_many['standard_designs'] to $this->has_many['standard_design']

It didn't help. I am still getting this error (after putting back the back-ticks )

I would really appreciate if you could help me resolve this error.

Doing this :

$this->has_many['standard_design'] = array(
            'foreign_model' => 'standard_design_m',
            'foreign_key' => 'dress_mode_id',
            'local_key' => 'id'
        );

Added my db prefix twice.

Same result for: $this->has_many['standard_design'] = 'standard_design_m'; (except now I also don't have my 'dress_mode_id')

[avenirer]

I am referring to the fact that you mentioned in there the foreign_table...

[dani0332]

Yes, I took that from your docs, that says right way , but like I said removing the 'foreign_table' didn't help me getting rid of that error. Please HELP !!

[xcymax]

I had same problem when using postgresql. Modified to use " instead of backticks is the correct solution. Eg. $select[] = '"'.$foreign_table.'"."'.$foreign_key.'"';