Hi everyone,
I wanted to have subqueries in my fieldset, and seems like codeigniter and groceryCrud does not support it, so I tried to find a way, and found a promising project for codeigniter at:
https://github.com/NTICompass/CodeIgniter-Subqueries
Although the author says it works only for CodeIgniter (1.7.x - 2.0.2), it is working under Codeigniter 3.0 as well without the modification of get_compiled_select function.
So I added the Subquery.php to application/libraries/
and after all I modified libraries/Grocery_CRUD.php:
--- Grocery_CRUD.php.orig 2015-03-14 07:51:51.000000000 +0100
+++ Grocery_CRUD.php 2015-06-25 12:36:57.695807061 +0200
@@ -1256,6 +1257,10 @@
protected function get_list()
{
+ if(!empty($this->subquery))
+ foreach($this->subquery as $subquery)
+ $this->basic_model->subquery($subquery[0], $subquery[1], $subquery[2], $subquery[3] );
+
if(!empty($this->order_by))
$this->basic_model->order_by($this->order_by[0],$this->order_by[1]);
@@ -3465,6 +3470,7 @@
protected $_unique_fields = array();
protected $validation_rules = array();
protected $relation = array();
+ protected $subquery = array();
protected $relation_n_n = array();
protected $upload_fields = array();
protected $actions = array();
@@ -3926,6 +3932,18 @@
/**
*
+ * add subquery column
+ * SELECT count(1) as __count__ FROM products WHERE products.supplier=suppliers.sup_id
+ * add_subquery("__count__","products","count(1)","products.supplier=suppliers.sup_id");
+ */
+ public function add_subquery( $field_name,$table_name, $query_str,$where_clause )
+ {
+ $this->subquery[$field_name] = array($field_name, $table_name, $query_str, $where_clause);
+ }
+
+
+ /**
+ *
* The fields that user can see . It is only for the edit form
*/
public function edit_fields()
And models/Grocery_crud_model.php:
--- grocery_crud_model.php.orig 2015-03-14 07:51:51.000000000 +0100
+++ Grocery_crud_model.php 2015-06-25 12:29:47.795346409 +0200
@@ -207,6 +207,13 @@
return $result;
}
+ function subquery($field_name,$table_name, $query_str,$where_clause) {
+ $sub = $this->subquery->start_subquery('select');
+ $sub->select($query_str)->from($table_name);
+ $sub->where($where_clause);
+ $this->subquery->end_subquery($field_name);
+ }
+
function join_relation($field_name , $related_table , $related_field_title)
{
$related_primary_key = $this->get_primary_key($related_table);
this way I can have subquery in groceryCRUD as an example:
$this->grocery_crud->set_table('suppliers');
$this->grocery_crud->set_subject('Suppliers');
$this->grocery_crud->add_subquery("__count__","products","count(1)","products.supplier=suppliers.sup_id");
I just shared this small piece of code in hope that I can save some time for others who would like to have subqueries as well
Best Regards,
Redax
