⚠ 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

Subquery addon



redax
  • profile picture
  • Member

Posted 25 June 2015 - 11:26 AM

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

 

​       

 

 

 

 

 

 

 

 


betaPictoris
  • profile picture
  • Member

Posted 10 October 2018 - 15:36 PM

Hi,

 

You deserve a warm thank you for this, so read carefully: THANK YOU !

This is awesome, saved me hours of attempts

 

As I true newbie, it took me a while to guess that copying the library was not sufficient - I had to load it in my controller, of course...

 

And then it worked like a charm !

 

Only when I tried to add a second subquery column, it crashed - it's only because the add_subquery method lacks a final "return $this;". #fixed

 

Thanks again and regards

bp


couponstechie
  • profile picture
  • Member

Posted 15 October 2018 - 10:29 AM

This is a helpful post overview. I am so fulfilled to get this post article and better than average salty crew coupon information. I was envisioning getting such a post which is to a great degree helpful to us.