[bfmike]

Looking for what methods I should be extending in my grocery crud model so that I can search on my Joined query.

At the moment I can only search fields from the main table.

Here is a copy of my model

<?php

class users_crud_model extends grocery_CRUD_Model {



// --------------------------------------------------------------------

/**

  * Initialisation function (__construct)

  *

  * Initialise, get parent properties and retrieve any messages

  *

  * @access public

  * @return void

  */

public function __construct(){

  parent::__construct();

}



function get_list() {

 

  if($this->table_name === null) {

   return false;

  }

 

  $select = "{$this->table_name}.*";

 

  // ADD YOUR SELECT FROM JOIN HERE

  $_select = array(

   'user_profiles.firstname',

   'user_profiles.lastname',

   'user_profiles.margin',

   'user_profiles.created_by',

   'user_profiles.rep_code',

   );

  $select .= ','. implode(',', $_select);

  if( ! empty($this->relation))

   foreach ($this->relation as $relation) {

    list($field_name , $related_table , $related_field_title) = $relation;

   

    $unique_join_name = $this->_unique_join_name($field_name);

    $unique_field_name = $this->_unique_field_name($field_name);

   

    if (strstr($related_field_title,'{')) {

	 $select .= ", CONCAT('".str_replace(array('{','}'),array("',COALESCE({$unique_join_name}.",", ''),'"),str_replace("'","\\'",$related_field_title))."') as $unique_field_name";

    }

    else {

	 $select .= ", $unique_join_name.$related_field_title as $unique_field_name";

    }

   

    if ($this->field_exists($related_field_title)) {

	 $select .= ", {$this->table_name}.$related_field_title as '{$this->table_name}.$related_field_title'";

    }

   }

 

  $this->db->select($select, false);

 

  // ADD YOUR JOIN HERE for example:

  $this->db->join('user_profiles','user_profiles.user_id = users.id');

 

  if ($this->profile_data['role'] != 'admin') {

   $this->db->where('activated', 1);

  }

  else {

   $this->db->order_by('activated', 'asc');

  }

 

  $results = $this->db->get($this->table_name)->result();

 

  return $results;

}









}

[bfmike]

I thought I would provide a little more information regarding this as it may come across as a bit vague.

Essentially I have created a custom model so that I can join multiple tables from my database in to one grocery crud datatable. The output is brilliant and works well but my problem lies with being able to filter/search.

If I attempt to filter/search on a column that is not in the main selection table I am returned a 500 error with a database error detailing the fact that my filtered/searched column is not in the table for this query.

My question is, what method do I need to extend/relplace in my grocery crud model?

I will have a look at this again myself this afternoon and If I find what I am looking for I will post on here for everyone.

Cheers

Mike

[doxrealm]

For doing this you also need to add the function "get_total_results" to your custom model: 

class Custom_model extends grocery_CRUD_Model {


function get_list(){

   //your model


}



function get_total_results() {
                           
        $this->db->from($this->table_name);
        //make the joins you need.
        $this->db->join('table_to_join','foreing_key = primary_key');                  
        return $this->db->count_all_results();
}


}

[davidoster]

This is true!

Thank you for this [member=doxrealm] and welcome to the forums.