I'm using DataTables with server side processing in CodeIgniter. Searching is enabled. Everything is working fine, but I noticed that when I search for data in a column that contains a foreign key, it doesn't return the data in the column (except I search using the foreign key value).
For instance, I have a table called 'purchases', which has a foreign key field called 'customer_id' where I save the ID of the customer who made the purchase. The customers table exists in the same database and has fields like id, name, location, etc. In my views, what is shown in the customer column is the name of the customer (not the ID). However, when I search the name of a customer that is shown, it doesn't filter the records to show this customer. But when I search using the ID of the customer, it filters and returns the record.
Basically, I want to be able to use the customers.name rather than the customers.id in the search field given that the purchases table which I am generating DataTable results from only has customer_id as a foreign key to the customers table.
This is what I have in my model currently:
var $table = 'purchases';
var $column_order = array(null, 'id', 'customer_id', 'amount', 'date', 'status');
var $column_search = array('id', 'customer_id', 'amount', 'date', 'status');
var $order = array('date' => 'desc');
private function record_query() {
$this->db->from($this->table);
$i = 0;
foreach ($this->column_search as $item) // loop column
{
if($_POST['search']['value']) // if datatable send POST for search
{
if($i===0) // first loop
{
$this->db->group_start(); // open bracket. query Where with OR clause better with bracket. because maybe can combine with other WHERE with AND.
$this->db->like($item, $_POST['search']['value']);
} else {
$this->db->or_like($item, $_POST['search']['value']);
}
if(count($this->column_search) - 1 == $i) //last loop
$this->db->group_end(); //close bracket
}
$i++;
}
if(isset($_POST['order'])) { // here order processing
$this->db->order_by($this->column_order[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
} else if(isset($this->order)) {
$order = $this->order;
$this->db->order_by(key($order), $order[key($order)]);
}
}
I suspect it's because the $column_search array only accounts for customer_id, but I don't know how to include other fields from the customers table in the array. I don't want to save the customer's name in the purchases table because that will create a lot problems in the application. Can someone please point me to the right direction?