[Travar Lazica]

Let's say you want to execute a complex custom query that you want to pass as a query string to Grocery CRUD.

 

 Step 1. Create a custom model

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

class Custom_query_model extends grocery_CRUD_model {

	private  $query_str = ''; 
	function __construct() {
		parent::__construct();
	}

	function get_list() {
		$query=$this->db->query($this->query_str);

		$results_array=$query->result();
		return $results_array;		
	}

	public function set_query_str($query_str) {
		$this->query_str = $query_str;
	}
}

Step 2. Code the controller

Add the following code to your controller and existing mechanic for outputting the Grocery CRUD interface.

$crud = new grocery_CRUD();
$crud->set_model('custom_query_model');
$crud->set_table('employees'); //Change to your table name
$crud->basic_model->set_query_str('SELECT * FROM employees'); //Query text here
$output = $crud->render();

When you assign a custom model to the grocery crud object ($crud), you can access it with $crud->basic_model, regardless of the custom model's name.

 

Hope this helped.

 

!Edit!

 

Another way you can execute temporary tables, that requires no tampering with custom models etc. is to create a temporary table in the database filled with data from the custom query and then just let Grocery CRUD do it's rendering the temp table.

 

Filling temporary tables in Microsoft SQL Server:

SELECT t.*
INTO #<name for the temporary table>
FROM (<query>) as t

* note the the preceding '#'  to the temporary table name, it is a prefix to the names of all temporary tables.

 

Filling temporary tables in MySQL:

CREATE TEMPORARY TABLE <name for the temporary table> AS (<query>)

For more info on temporary tables in CodeIgniter: http://ellislab.com/forums/viewthread/216656/

[davidoster]

Hello and welcome to the forums [member=travar lazica].

Thank you for this contribution. It's nice to see different ways of doing things and all of us learn new things and different ways of implementing the same thing but choosing the most appropriate way of implementation.

 

Just a quote, we need to be extra carefull with the temporary tables in MySQL/MSSQL because they are not persistent.

"

Temporary Tables

You can use the TEMPORARY keyword when creating a table. A TEMPORARY table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) To create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.

Note

CREATE TABLE does not automatically commit the current active transaction if you use the TEMPORARY keyword.

" from http://dev.mysql.com/doc/refman/5.1/en/create-table.html

[maxxe]

Thanks for the int, but i receive 

 

"Fatal error: Class 'grocery_CRUD_Model' not found in C:\easyphp\codeigniter_2_1_4\application .... on line 3"

 

[webdev23]

My install throws an error on this line:

$crud->basic_model->set_query_str('SELECT * FROM employees'); //Query text here

 

specifically regarding:

set_query_str

[Anu Krishna]

Hi,

 

This way is working for me, but pagination doesn't work if we use our custom queries, as it is listing all the records and pagination total count is wrong always.

Does anyone had gone thru this kind of issue so far? Any help is really appreciated.

 

Thanks,

Anu

[smart-aleck]

Hi,

 

This way is working for me, but pagination doesn't work if we use our custom queries, as it is listing all the records and pagination total count is wrong always.

Does anyone had gone thru this kind of issue so far? Any help is really appreciated.

 

Thanks,

Anu

 

I have the exact same issue, even when I add a LIMIT 100 statement at the end of my custom query string it doesn't process it and I get a full list of 2700 rows.  The even crazier thing is that when I execute the query in MySQL workbench, there are only 1120 rows total, it seems to be ignoring parts of or changing the query somehow.

[smart-aleck]

I did some more looking and it appears that my query IS executing properly with a LIMIT, but the pagination is just giving some crazy numbers.  It would be awesome if someone out there could help tackle this issue.  I'm going to scan through the grocery_CRUD_model code and see if I can spot what might be occurring.  My query is pretty wild, here it is for reference:

SELECT 	p.orderorderid,
	p.orderprodid, 
	p.ordprodsku, 
	p.ordprodname, 
	p.ordprodqty, 
	p.ordprodoptions, 
	p.ordprodvariationid, 
	p.order_address_id,
	addr.first_name,
	addr.last_name,
	addr.company,
	addr.address_1,
	addr.address_2,
	addr.city,
	addr.state,
	addr.zip,
	addr.phone,
	optlist.optionlist

FROM 	sole_order_products AS p

LEFT JOIN
       (SELECT sub.ordprodid, GROUP_CONCAT(opt) AS 'optionlist'
	FROM   (SELECT	ocf.ordprodid,
		CONCAT_WS(': ', fieldname, textcontents) AS opt
		FROM	sole_order_configurable_fields ocf
		WHERE	ocf.orderid > 692
	       )AS	sub
		GROUP BY sub.ordprodid
	) AS optlist

ON 	p.orderprodid = optlist.ordprodid

LEFT JOIN sole_order_addresses AS addr

ON	p.order_address_id = addr.id	

WHERE 	p.orderorderid
	IN (
	SELECT orders.orderid
	FROM sole_orders orders
	WHERE orders.orderid > 692
	AND orders.ordstatus = 11
	AND orders.ordpaymentstatus = 'captured'
	AND orders.ordrefundedamount = 0
	)

AND 	p.orderorderid > 692

[smart-aleck]

OK der der der ... I pretty much answered my own question.  I ran into this exact issue last year when creating my own custom model.  All it took was to look through my last few posts to discover the answer.  In order to display the correct number of records, you need to add the following function to your new model:

	function get_total_results()
	{
		return $this->db->query($this->query_str)->num_rows();	
	}

This does NOT however fix the pagination, I will be looking at that next and will post what I find back here.

[smart-aleck]

I started working on the order_by() function and came up with the following:

	function order_by($order_by , $direction) {
		$this->set_query_str($this->query_str . " ORDER BY " . $order_by . " " . $direction);
	}		

I echoed the query and it looks correct, but now I'm getting the following error after I attempt to sort:

 

Fatal error: Call to a member function result() on a non-object in /public_html/control/application/models/custom_query_model.php on line 18

 

This is the code it is referencing:

	function get_list() {
		$query=$this->db->query($this->query_str);
		
		$results_array=$query->result();
		return $results_array;	
	}

I added some code to check if $query was an object or not, and if not just return $query but this cascades down the line and starts breaking other functions.  It seems like when the sort function is called, the variable passed along is simply an array but I cannot figure out where this happens.  I'm also looking for how pagination functions are setup and where exactly this code is.

 

Can anyone else chime in here?

[Emerson NVlsqz]

-

[marlaaragao]

If anyone is struggling with pagination in custom query, I managed to find a simple solution. Its here: /topic/3177-custom-query-pagination-problem-solution/

 

Thank you!

[michaelh613]

The problem I am having is that the filter isn't working once I use this solution.    Have you tried anything to get filtering working.

[likhon3k]

Let's say you want to execute a complex custom query that you want to pass as a query string to Grocery CRUD.

 

 Step 1. Create a custom model

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

class Custom_query_model extends grocery_CRUD_model {

	private  $query_str = ''; 
	function __construct() {
		parent::__construct();
	}

	function get_list() {
		$query=$this->db->query($this->query_str);

		$results_array=$query->result();
		return $results_array;		
	}

	public function set_query_str($query_str) {
		$this->query_str = $query_str;
	}
}

Step 2. Code the controller

Add the following code to your controller and existing mechanic for outputting the Grocery CRUD interface.

$crud = new grocery_CRUD();
$crud->set_model('custom_query_model');
$crud->set_table('employees'); //Change to your table name
$crud->basic_model->set_query_str('SELECT * FROM employees'); //Query text here
$output = $crud->render();

When you assign a custom model to the grocery crud object ($crud), you can access it with $crud->basic_model, regardless of the custom model's name.

 

Hope this helped.

 

!Edit!

 

Another way you can execute temporary tables, that requires no tampering with custom models etc. is to create a temporary table in the database filled with data from the custom query and then just let Grocery CRUD do it's rendering the temp table.

 

Filling temporary tables in Microsoft SQL Server:

SELECT t.*
INTO #<name for the temporary table>
FROM (<query>) as t

* note the the preceding '#'  to the temporary table name, it is a prefix to the names of all temporary tables.

 

Filling temporary tables in MySQL:

CREATE TEMPORARY TABLE <name for the temporary table> AS (<query>)

For more info on temporary tables in CodeIgniter: http://ellislab.com/forums/viewthread/216656/

 

Thank You very much for Your solution. I was searching this solution form long time but today I found it.

[Hemant Randive]

Hello,

When i write the code to render the data using crud function i want to pass some variable coming from some form i.e. a some data from form and using custom query model i want to pass that variable to that query. 

 

Something like this

 

 public function export_accounts_data() {

$month = $this->uri->segment(5);

 

but when i user somthing like this sometimes the function dont work.

please help me in this 

[rostamiani]

Thanks, but I could not use this code.

The problem is that my SQL query joins multiple tables but the columns in the list are from the table that I mentioned in set_table command.

How can I use this code when I'm using multiple tables?
 

Thanks

 

Update:

 

Solved, I forgot to set the columns.

[GiancarloN]

edit: search in custom queries: better solution /topic/3620-custom-queries-with-pagination-sorting-and-searching/#entry14192

 

Hello!

I am new here but I have found this forum very helpful in many cases, therefore it is time to share my 2 cents workaround to search issue, even if it is not solid enough to be used in production environment with complex custom queries.

 

in class Custom_query_model add the following

        private $search_info = null;
        /**
	 * NOTE: 
	 * this works only in very simple cases where $query_str accepts appending where, order by to its end 
	 * this does NOT work in changed field types @see My_extension::set_ajax_list_queries()
	 * @see Grocery_crud_model::get_list()
	 */
	function get_list() {
		if(!isset($this->search_info) || $this->search_info == null) {
			return $this->db->query($this->query_str)->result();
		}
		$sql = $this->query_str;
		$conditions = $this->search_info;
		if(isset($conditions->search) && $conditions->search != null && is_array($conditions->search) && count($conditions->search) > 0) {
			foreach($conditions->search as $key => $value) {
				$sql .= '  AND `'.$key.'` LIKE '%'.@mysql_real_escape_string($value).'%' ';
			}
		}
		if(isset($conditions->order_by) && $conditions->order_by != null && is_array($conditions->order_by) && count($conditions->order_by) > 0) {
				$sql .= ' order by `'.$conditions->order_by[0].'` '.$conditions->order_by[1];
		}
		return $this->db->query($sql)->result();
	}
	
	public function set_search_info($search_info) {
		$this->search_info = $search_info;
	}

then extend Grocery_CRUD class (such us it is described -> /topic/90-how-to-create-an-extension-for-grocery-crud-real-example-included/ ) and override  set_ajax_list_queries method:

/**
	 * Override to fix search in true_false and dropdown fields
	 * @see grocery_CRUD_Model_Driver::set_ajax_list_queries()
	 */
	public function set_ajax_list_queries($state_info = null)
	{

		if (!isset($state_info) || $state_info == null || empty($state_info)
			|| !isset($state_info->search) || $state_info->search == null || empty($state_info->search)
			|| !is_array($state_info->search)
			|| !isset($this->change_field_type) || $this->change_field_type == null || empty($this->change_field_type)
			|| !is_array($this->change_field_type)
				) {
			if (is_a($this->basic_model, 'Custom_query_model') && method_exists($this->basic_model, 'set_search_info' )) {
				$this->basic_model->set_search_info($state_info);
			}
			parent::set_ajax_list_queries($state_info);
			return;
		}
		
		$changeFields = $this->change_field_type;
		foreach ($state_info->search as $search_field => $search_text) {
			if (array_key_exists($search_field, $changeFields) 
				&& isset($changeFields[$search_field]->extras) 
				&& is_array($changeFields[$search_field]->extras)
				&& count($changeFields[$search_field]->extras) > 0
					) {
				$extraArr = $changeFields[$search_field]->extras;
				if ($changeFields[$search_field]->type == 'true_false') {
					foreach ($extraArr as $extraKey => $extraVal) {
						if (strval(intval($search_text)) === $search_text) {
							if (intval($search_text) == intval($extraKey)) {
								$state_info->search[$search_field] = $extraKey;
							}
						}
						else if(stripos($extraVal, $search_text) > -1) {
							$state_info->search[$search_field] = $extraKey;
						}
					}
				}
				else if ($changeFields[$search_field]->type == 'dropdown') {
					$conditionAdded = 0;
					foreach ($extraArr as $extraKey => $extraVal) {
						if (strval(intval($search_text)) === $search_text) {
							if (intval($search_text) == intval($extraKey)) {
								$this->where($search_field.' =' , intval($extraKey));
								unset($state_info->search[$search_field]);
								$conditionAdded++;
							}
						}
						else if(stripos($extraVal, $search_text) > -1) {
							unset($state_info->search[$search_field]);
							$this->or_like($search_field , $extraKey);
						}
					}
					if(strval(intval($search_text)) === $search_text && $conditionAdded == 0) {
						// add a condition in order not to match with special value "-1"
						$this->where($search_field.' !=', '-1');
					}
				}
			}
		}
		if (is_a($this->basic_model, 'Custom_query_model') && method_exists($this->basic_model, 'set_search_info' )) {
			$this->basic_model->set_search_info($state_info);
		}
		parent::set_ajax_list_queries($state_info);
	}

Actually important part for this topic is only:

			if (is_a($this->basic_model, 'Custom_query_model') && method_exists($this->basic_model, 'set_search_info' )) {
				$this->basic_model->set_search_info($state_info);
			}

all the rest try to handle search problem in:

- 'dropdown' define such as array keys correspond to DB real values: $crud->field_type('column','dropdown',array("dbVal1" => "Label1","dbVal2" => "Label2"))

- 'true_false' fields (in this case assuming you use it in this way in your controller: $crud->field_type('column','true_false', array('No','Yes'));

 

 

 

This is still in development and I cannot assure it is bug free of course but I think it is a starting point to more expert people here. 

If you found errors or improvements please share them with the community.

 

bye,

G.

 

EDIT: I have workaround another bug (search does not work properly when input == '0') => /topic/3548-bootstrap-theme-search-in-list-with-value-0-on-tinyint1-fields-problem/#entry14232