⚠ 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

Pre-filtering data



jgalak

jgalak
  • profile picture
  • Member

Posted 07 January 2014 - 23:46 PM

Here's the situation.  I am writing what is, effectively, a ticketing system.  I have a ticket table and a consultant table, and a a join table that shows what tickets are assigned to what consultant.  I've set it as an n-n relationship, everything works fine.

 

But now I want to set it to show only those items assigned to the currently logged in consultant.  I have the consultant's id, which points into the consultant table.  What's the best way to filter only the assigned tickets?

 

I've seen suggestions using temporary tables and custom models, but both seem to be massive overkill.  Is there a simpler way to do it?  I poked around at the "where" command, but that doesn't seem to work over a join. 

 

Thanks,

Juliean.


jgalak

jgalak
  • profile picture
  • Member

Posted 08 January 2014 - 00:51 AM

Well, I finally figured it out using a custom model, but it really seems like there should be an easier way...  For anyone interested, here's my custon model code:

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

//Modification of grocery_CRUD_model to allow adding join and where clauses

class gcrud_query_model extends grocery_CRUD_model {


    private $join_tbl = null;
    private $join_str = null;

    private $where_array = null;

    function __construct() {
        parent::__construct();
    }

    function get_list() {

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

        //set_relation special queries
        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,'{'))
                {
                    $related_field_title = str_replace(" ","&nbsp;",$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'";
            }
        }


        //set_relation_n_n special queries. We prefer sub queries from a simple join for the relation_n_n as it is faster and more stable on big tables.
        if(!empty($this->relation_n_n))
        {
            $select = $this->relation_n_n_queries($select);
        }

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

        //if there is data for a join, add a join clause
        if ((!is_null($this->join_tbl)) && (!is_null($this->join_str)))
        {
            $this->db->distinct();
            $this->db->join($this->join_tbl,$this->join_str,'left outer');
        }

        //if there is data for a where, add a where clause
        if (!is_null($this->where_array))
        {
            $this->db->where($this->where_array);
        }



        $results = $this->db->get()->result();
        //var_dump($this->db->last_query());  //For debugging
        echo "<br>";

        return $results;    }

    public function set_join_str($join_tbl, $join_str) {

        $this->join_tbl = $join_tbl;
        $this->join_str = $join_str;
    }

    public function set_where_str($array) {

        $this->where_array = $array;
    }
}

Using this in the controller might look like this:

 $crud->set_model('gcrud_query_model');
$crud->basic_model->set_join_str("tblB", "tblA.id=tblB.aId");
$where_array = array("tblB.id" => 25,
                                 "complete" => 0);
$crud->basic_model->set_where_str($where_array);

In this example, tblA is the table the CRUD is running against, and I want only those assigned to consultant #25.


shrike2011

shrike2011
  • profile picture
  • Member

Posted 21 January 2014 - 04:51 AM

Thank you for taking the time to post your solution, jgalak. Your initial scenario is very much like mine. This week, I'll be implementing a solution based on your class.


carperolm

carperolm
  • profile picture
  • Member

Posted 03 March 2016 - 12:03 PM

Thank you jgalak! you save mi time!


Jamal Javed

Jamal Javed
  • profile picture
  • Member

Posted 25 April 2016 - 17:43 PM

jgalak you are the man.