⚠ In case you've missed it, we have migrated to our new website, with a brand new forum. For more details about the migration you can read our blog post for website migration. This is an archived forum. ⚠

  •     

profile picture

Error on search of a column extracted from a join on custom model



Daniele

Daniele
  • profile picture
  • Member

Posted 04 September 2018 - 11:56 AM

Hi all,

 

I use last version of GC Enterprise, 2.6.1 and i need a suggest to solve an errore occourred when i try to search on a column of a joined table, join is made with a custom model.

My tables are:
Driver table tbl_facts with columns ID_GU_HAS_FACT, ID_GUEST and other attributes
Join table tbl_guests with columns ID_GUEST ( join key column), G_TITLE, FULLNAME and other attributes


Error is the missing column on driver table:

Message: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'tbl_facts.FULLNAME' in 'where clause'

Obviously the error is correct, on tbl_facts there isn't any column named FULLNAME, but it's extracted by custom query in custom model


Since with setRelation method i can't manage 2 external column, i created a custom model as for example published on GC Enterprise documentation

Following the extract of my code, first the controller and then the model
 

Controller code

$model = new FactModel($db);
$crud->setModel($model);
$crud->setTable('tbl_facts');
$crud->columns(['G_TITLE','FULLNAME']);
$output = $crud->render();
$this-> v_output($output);
Custom model code

public function getFieldTypes($tableName) {
    $fieldTypes = parent::getFieldTypes($tableName);

    $gtitleFT = new ModelFieldType();
    $fullnameFT = new ModelFieldType();

    $gtitleFT->dataType = 'varchar';
    $fullnameFT->dataType = 'varchar';

    $fieldTypes['G_TITLE'] = $gtitleFT;
    $fieldTypes['FULLNAME'] = $fullnameFT;

    return $fieldTypes;
}

public function getOne($id) {
    $guests = parent::getOne($id);

    $this->db->select("
        tbl_guests.G_TITLE,
        tbl_guests.FULLNAME,
        tbl_fact.*
        ", false);
    $this->db->join('tbl_guests', 'tbl_facts.ID_GUEST = tbl_guests.ID_GUEST', 'left');
    $this->db->where('ID_GU_HAS_FACT', $id);

    return $guests;
}


 public function getList() {
    $id_fact = $_GET['id_fact'];
    $order_by = $this->orderBy;
    $sorting = $this->sorting;

    $this->db->select("tbl_guests.G_TITLE, tbl_guests.FULLNAME, tbl_facts.* ", false);
    $this->db->join('tbl_guests', 'tbl_facts.ID_GUEST = tbl_guests.ID_GUEST', 'left');
    $this->db->where('(tbl_facts.ID_FACT = '.$id_fact.' )');

    if ($order_by !== null) {
        $this->db->order_by($order_by. " " . $sorting);
    }

    if (!empty($this->_filters)) {
        foreach ($this->_filters as $filter_name => $filter_value) {
            $this->db->like($filter_name, $filter_value);
        }
    }

    if (!empty($this->_filters_or)) {
        foreach ($this->_filters_or as $filter_name => $filter_value) {
            $this->db->or_like($filter_name, $filter_value);
        }
    }

    $this->db->limit($this->limit, ($this->limit * ($this->page - 1)));
    $results = $this->db->get($this->tableName)->result_array();

    return $results;
    }

Is there any other function I need to insert in my custom model that is specifically for search function?

Or there is some error on my code?

 

Thanks to anyone can help me with this error

 

 

Regards

Daniele

 

 

 


spiritbreaker69

spiritbreaker69
  • profile picture
  • Member

Posted 13 February 2019 - 09:20 AM

Well i think setRelation is not working on setmodel when searching.. Ive been struggling with it.. maybe they should have a sample on it..


Kaabi

Kaabi
  • profile picture
  • Member

Posted 15 February 2019 - 23:32 PM

Well i think setRelation is not working on setmodel when searching.. Ive been struggling with it.. maybe they should have a sample on it..

 

I'm facing the same Issue too