Jump to content


grocery crud master-detail 1_n relation implementation

grocery_crud set_relation 1_n_relation

  • Please log in to reply
6 replies to this topic

#1 wizard24h

wizard24h

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 17 June 2017 - 01:41 PM

hello guys I'm a new member and I'd like to share with you my idea which discussed in this topic : 

http://www.grocerycr...er-detail-form/

 

this was a very long time ago and I have no idea if you guys already fix this or not but what I've done is I fix this issue and I would like to share my code with you . 

 

so just to remind you guys this discussion is about implementing master-detail 1_n relation inside tables, for example: 

 

if you have a customers table and each customer has multiple phone numbers

in this case you might want to add the phone number inside the add customer form like this : 

 

2kjGipJ.png

as you can see we can add and remove rows easily by pressing the buttons shown in the figure above ! 

 

so please follow my instructions : 

first create phone_numbers table in your database : 

CREATE TABLE `phone_numbers` (
  `id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `phone_type` varchar(255) NOT NULL,
  `phone_number` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

then inside the customers controller define a new field and call it for example : phone_number like this :

$crud->fields('first_name', 'last_name', 'email', 'gender', 'birth_date', 'phone_number');

note : phone_number field isn't exist inside customers table in the database !!. 

 

after this we add callback for our new field like this :

$crud->callback_add_field('phone', array($this, 'phone_number'));

and then we define our new callback inside our class like this :

    function phone_number($value = '', $primary_key = null){

        $this->db->where('customer_id', $primary_key);
        $numbers = $this->db->get('phone_numbers')->result_array();
        $numbers[1] = array();
        $html = '
            <table id="phone_number"> 
            <tr> <th>type</th><th>Phone number</th> </tr>';
        foreach ($numbers as $number){
            if (!empty($number["phone_number"]) )
            $html.= '<tr><td><input name="phone_types[]" type="text" value="'.$number["phone_type"].'" ></td><td><input name="phone_numbers[]" type="text" value="'.$number["phone_number"].'" ></td><td><button class="remove_number btn btn-danger">remove</button></td></tr>';
        }
        $html .='
            <tr><td><input name="phone_types[]" type="text" ></td><td><input name="phone_numbers[]" type="text" ></td><td><button class="remove_number btn btn-danger">remove</button></td></tr>
            </table>
            <button type="button" id="add_number" class="btn btn-info" style="width: 100px; margin:20px 0px; ">add</button>
            ';
        return $html;
    }

by doing all these steps we'll get a simple table inside our add customer form ! 

please keep in mind : 

** don't use the same input field name inside the $html variable as the field name inside your grocery crud fields definition 

     example : inside $crud->fields function we pass phone_number variable and inside $html variable in the phone_number call back we use phone_numbers[]

      (different name) 

 

 

so now we should see a static table as mentioned before so now we need to bring it to live  :D  

inside your custom Javascript file put this : 

jQuery(document).ready(function(){
// Sales chart
jQuery(' #add_number').on('click', function(e){
e.preventDefault();

var row = '<tr><td><input name="phone_types[]" type="text" ></td><td><input name="phone_numbers[]" type="text" ></td><td><button class="remove_number btn btn-danger">remove</button></td></tr>';
jQuery('#phone_number tr:last').after(row);
register_remove_event();
});

function register_remove_event(){
jQuery('#phone_number button.remove_number').on('click', function (e) {
e.preventDefault();
if (jQuery('#phone_number tr').length > 2){
jQuery(this).parents('tr').remove();

}
});
}
register_remove_event();
});

the code above does nothing more than removing and adding rows in the table ! 

 

now we want to save the inserted phone numbers to the database 

easy !! 

 

use another 2 callbacks inside your class 

            $crud->callback_before_insert(array($this, 'save_phone_number'));
            $crud->callback_before_update(array($this, 'save_phone_number'));

the first one fire for new inserted customers 

the second one fires when we update an existing customer

 

they both invoke the same callback save_phone_number

 

so we need to define it  :(

    function save_phone_number($post_array, $primary_key){
        //first we delete all the old records since we're going to re-insert them back!
        $this->db->delete('phone_numbers', array('customer_id' => $primary_key));
        $i = 0;
        $data = array();
        while (!empty($post_array["phone_numbers"][$i]) ){
            $phone = array(
                'phone_type' => $post_array["phone_types"][$i],
                'phone_number' => $post_array["phone_numbers"][$i],
                'customer_id' => $primary_key
            );
            array_push($data, $phone);
            $i++;
        }
        $this->db->insert_batch('phone_numbers', $data);
    }

the code above defines the save_phone_number call back 

so what that code does is it deletes all previous phone numbers and  insert the new ones !

 

now as we can see we've finished our solution but it looks so stupid 

like miss miss colombia without makeup  :lol:

so we need to implement some styles for our new feature :D

 

so put the following code inside your css file : 

/*everything below this line is custom */
table#phone_number{
border-collapse: collapse;
width: 100%;
}
table#phone_number th, td {
text-align: left;
padding: 8px;
}
table#phone_number tr:nth-child(even){background-color: #f2f2f2}

table#phone_number th {
background-color: #46bfeb;
color: white;
}

table#phone_number th:first-child { -webkit-border-radius:5px 0 0 0; border-left:none}
table#phone_number th:last-child{ -webkit-border-radius:0 5px 0 0;border-right:none }
table#phone_number tfoot td:first-child{ -webkit-border-radius:0 0 0 5px ;}
table#phone_number tfoot td:last-child{ -webkit-border-radius:0 0 5px 0;}

table#phone_number input{
border: solid 1px lightgrey;
padding: 5px;
width: 80%;
min-width: 40px;
-webkit-border-radius: 7px;
-moz-border-radius: 5px;
border-radius: 5px;
}

and here we can see this amazing table inside our customer insertion page :D

2kjGipJ.png

 

ok guys so now we finish all this, 

anyway if I see any support or interests on this topic then I'll release a nice implementation on github which requires less code and time from you  :rolleyes:  



#2 Mauistar

Mauistar

    Newbie

  • Members
  • Pip
  • 4 posts

Posted 10 November 2017 - 02:16 AM

hola amigo, me gustarĂ¡ implementarlo en mi sitio web, pero algunos codigos que escribiste no se donde se ponen, me ayudas? gracias



#3 Amit Shah

Amit Shah

    grocery CRUD Master

  • Advanced Member
  • PipPipPip
  • 1,192 posts
  • LocationMumbai, India

Posted 10 November 2017 - 06:33 AM



hello guys I'm a new member and I'd like to share with you my idea which discussed in this topic : 

http://www.grocerycr...er-detail-form/

 

this was a very long time ago and I have no idea if you guys already fix this or not but what I've done is I fix this issue and I would like to share my code with you . 

 

so just to remind you guys this discussion is about implementing master-detail 1_n relation inside tables, for example: 

 

if you have a customers table and each customer has multiple phone numbers

in this case you might want to add the phone number inside the add customer form like this : 

 

2kjGipJ.png

as you can see we can add and remove rows easily by pressing the buttons shown in the figure above ! 

 

so please follow my instructions : 

first create phone_numbers table in your database : 

CREATE TABLE `phone_numbers` (
  `id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `phone_type` varchar(255) NOT NULL,
  `phone_number` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

then inside the customers controller define a new field and call it for example : phone_number like this :

$crud->fields('first_name', 'last_name', 'email', 'gender', 'birth_date', 'phone_number');

note : phone_number field isn't exist inside customers table in the database !!. 

 

after this we add callback for our new field like this :

$crud->callback_add_field('phone', array($this, 'phone_number'));

and then we define our new callback inside our class like this :

    function phone_number($value = '', $primary_key = null){

        $this->db->where('customer_id', $primary_key);
        $numbers = $this->db->get('phone_numbers')->result_array();
        $numbers[1] = array();
        $html = '
            <table id="phone_number"> 
            <tr> <th>type</th><th>Phone number</th> </tr>';
        foreach ($numbers as $number){
            if (!empty($number["phone_number"]) )
            $html.= '<tr><td><input name="phone_types[]" type="text" value="'.$number["phone_type"].'" ></td><td><input name="phone_numbers[]" type="text" value="'.$number["phone_number"].'" ></td><td><button class="remove_number btn btn-danger">remove</button></td></tr>';
        }
        $html .='
            <tr><td><input name="phone_types[]" type="text" ></td><td><input name="phone_numbers[]" type="text" ></td><td><button class="remove_number btn btn-danger">remove</button></td></tr>
            </table>
            <button type="button" id="add_number" class="btn btn-info" style="width: 100px; margin:20px 0px; ">add</button>
            ';
        return $html;
    }

by doing all these steps we'll get a simple table inside our add customer form ! 

please keep in mind : 

** don't use the same input field name inside the $html variable as the field name inside your grocery crud fields definition 

     example : inside $crud->fields function we pass phone_number variable and inside $html variable in the phone_number call back we use phone_numbers[]

      (different name) 

 

 

so now we should see a static table as mentioned before so now we need to bring it to live  :D  

inside your custom Javascript file put this : 

jQuery(document).ready(function(){
// Sales chart
jQuery(' #add_number').on('click', function(e){
e.preventDefault();

var row = '<tr><td><input name="phone_types[]" type="text" ></td><td><input name="phone_numbers[]" type="text" ></td><td><button class="remove_number btn btn-danger">remove</button></td></tr>';
jQuery('#phone_number tr:last').after(row);
register_remove_event();
});

function register_remove_event(){
jQuery('#phone_number button.remove_number').on('click', function (e) {
e.preventDefault();
if (jQuery('#phone_number tr').length > 2){
jQuery(this).parents('tr').remove();

}
});
}
register_remove_event();
});

the code above does nothing more than removing and adding rows in the table ! 

 

now we want to save the inserted phone numbers to the database 

easy !! 

 

use another 2 callbacks inside your class 

            $crud->callback_before_insert(array($this, 'save_phone_number'));
            $crud->callback_before_update(array($this, 'save_phone_number'));

the first one fire for new inserted customers 

the second one fires when we update an existing customer

 

they both invoke the same callback save_phone_number

 

so we need to define it  :(

    function save_phone_number($post_array, $primary_key){
        //first we delete all the old records since we're going to re-insert them back!
        $this->db->delete('phone_numbers', array('customer_id' => $primary_key));
        $i = 0;
        $data = array();
        while (!empty($post_array["phone_numbers"][$i]) ){
            $phone = array(
                'phone_type' => $post_array["phone_types"][$i],
                'phone_number' => $post_array["phone_numbers"][$i],
                'customer_id' => $primary_key
            );
            array_push($data, $phone);
            $i++;
        }
        $this->db->insert_batch('phone_numbers', $data);
    }

the code above defines the save_phone_number call back 

so what that code does is it deletes all previous phone numbers and  insert the new ones !

 

now as we can see we've finished our solution but it looks so stupid 

like miss miss colombia without makeup  :lol:

so we need to implement some styles for our new feature :D

 

so put the following code inside your css file : 

/*everything below this line is custom */
table#phone_number{
border-collapse: collapse;
width: 100%;
}
table#phone_number th, td {
text-align: left;
padding: 8px;
}
table#phone_number tr:nth-child(even){background-color: #f2f2f2}

table#phone_number th {
background-color: #46bfeb;
color: white;
}

table#phone_number th:first-child { -webkit-border-radius:5px 0 0 0; border-left:none}
table#phone_number th:last-child{ -webkit-border-radius:0 5px 0 0;border-right:none }
table#phone_number tfoot td:first-child{ -webkit-border-radius:0 0 0 5px ;}
table#phone_number tfoot td:last-child{ -webkit-border-radius:0 0 5px 0;}

table#phone_number input{
border: solid 1px lightgrey;
padding: 5px;
width: 80%;
min-width: 40px;
-webkit-border-radius: 7px;
-moz-border-radius: 5px;
border-radius: 5px;
}

and here we can see this amazing table inside our customer insertion page :D

2kjGipJ.png

 

ok guys so now we finish all this, 

anyway if I see any support or interests on this topic then I'll release a nice implementation on github which requires less code and time from you  :rolleyes:  

Kool brother - nice stuff.. 

Its worth a submission and will help quite a lot of other friends of ours.

 

Happy GCing :)



#4 rdroguett

rdroguett

    Member

  • Members
  • PipPip
  • 18 posts

Posted 11 September 2018 - 05:47 PM

Nice code... buts in my model i have a trouble... but i never recive the parameters into de callback...

 

 

Tables

 

CREATE TABLE `e_order` (
  `ide_order` int(11) NOT NULL,
  `run` varchar(11) NOT NULL,
  `date` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `e_product` (
  `ide_product` int(11) NOT NULL,
  `code` varchar(45) NOT NULL,
  `name` varchar(45) NOT NULL,
  `price` int(11) NOT NULL,
  `ide_order` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

 

 

    public function genera_orden(){
        $this->output->enable_profiler(TRUE);
        $this->load->model('token_model');
        $this->load->model('invoice_model');
        
        $customer   = new SoapClient("http://www.appoctava.cl/ws/WebService.php?wsdl", $this->data['conexion']);
        foreach($this->token_model->validar_token() as $row)
        {
            $array  = array("TOKEN" => $row->token); 
            try{ 
                $result         = $customer->__SoapCall("validartokenext", $array); 
                $result_code    = $result[0]->DescripcionResultado; 
                if($result_code == 'TVAL'){
                    $crud = new grocery_CRUD();
                    $crud->set_theme('datatables');
                    $crud->set_table('e_order');
                    $crud->set_subject('Orden de Compra');
                    $crud->fields('run', 'date', 'product');
                    $crud->callback_add_field('product', array($this, 'list_product'));


                    $crud->display_as('ide_user','Usuario');
                    $crud->display_as('run','Receptor');
                    $crud->display_as('product','Producto(s)');
                    
                    $crud->callback_before_insert(array($this, 'save_product'));
                    $crud->callback_before_update(array($this, 'save_product'));


                    $output = $crud->render();
                    $this->_appservice_output($output);
                }else{
                    echo "ERROR TOKEN";
                    $this->obtener_token();
                }
            }catch(SoapFault $e){ 
                echo "Ups!! hubo un problema y no pudimos recuperar los datos.<br/>$e<hr/>"; 
            } 
        }
    }

function save_product($post_array, $primary_key){        
$this->db->delete('e_product', array('ide_order' => $primary_key));
        $i = 0;
        $data = array();
        while(!empty($post_array["product_code"][$i]) ){
            $product = array(
                'code'      => $post_array["product_code"][$i],
                'name'      => $post_array["product_name"][$i],
                'price'     => $post_array["product_price"][$i],
                'ide_order' => $primary_key
            );
            array_push($data, $product);
            $i++;
        }
        $this->db->insert_batch('e_product', $data);
    }
function list_product($value = '', $primary_key = null){        
$this->db->where('ide_order', $primary_key);
        $products = $this->db->get('e_product')->result_array();
        $products[1] = array();
        $html = '
            <table id="product_list"> 
                <tr>
                    <th>CODIGO</th><th>NOMBRE ARTICULO</th><th>PRECIO ($CLP)</th>
                </tr>';
                foreach ($products as $product){
                if(!empty($product["code"]))
                $html.= '<tr>'. 
                        '<td><input name="product_code[]" type="text" value="'.$product["code"].'"></td>'. 
                        '<td><input name="product_name[]" type="text" value="'.$product["name"].'"></td>'. 
                        '<td><input name="product_price[]" type="text" value="'.$product["price"].'"></td>'. 
                        '<td><button class="remove_number btn btn-danger">Eliminar</button></td></tr>';
                }
                $html .='<tr>'.
                        '<td><input name="product_code[]" type="text"></td>'.
                        '<td><input name="product_name[]" type="text"></td>'.
                        '<td><input name="product_price[]" type="text"></td>'.
                        '<td><button id="remove_number" class="remove_number btn btn-danger">Eliminar</button></td></tr></table>'.
                        '<button type="button" id="add_number" class="cancel-button ui-input-button ui-button ui-widget ui-state-default ui-corner-all ui-state-hover" style="width: 100px; margin:20px 0px;">Agregar</button>';
                return $html;
    }




#5 rdroguett

rdroguett

    Member

  • Members
  • PipPip
  • 18 posts

Posted 12 September 2018 - 09:21 PM

the problem is the position of the js in the proyect but i dont know hoy can i fixed



#6 rdroguett

rdroguett

    Member

  • Members
  • PipPip
  • 18 posts

Posted 21 September 2018 - 04:25 PM

This metho make problems with the edit options... please check



#7 wizard24h

wizard24h

    Newbie

  • Members
  • Pip
  • 2 posts

Posted 27 November 2019 - 09:06 PM

This metho make problems with the edit options... please check

 

I saw your reply 

please inform me if you still interested in the solution after 1 year 

best regards. 







Also tagged with one or more of these keywords: grocery_crud, set_relation, 1_n_relation

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users