[R2D2]

Hi everyone,

I am not familiar with grocery crud, I have just uploaded everything i needed and everything works fine so far,
i get my tables from the database, everythings working fine

but now I want to display some tables with joins ...
how does that work?

i have a table named "players", now i want some additive information from the table "persons"
I couldn't find anything helpful so far, maybe i have overlooked a page on grocerycrud.com?
google isn't helping much

Thanks in advance!

[R2D2]

Hi again,

please give me a hint where to look or anything else, i am quite desperate, there are many functions, but none of it seems to fit the table join,
maybe i am just misunderstanding some functions, but maybe a small thought.provoking impulse does help

[web-johnny]

Hello [member='R2D2'] and welcome to the forum,

Actually perhaps it seems obvious to grocery CRUD to have joins and customs queries to the table, but it still NOT an available feature at this moment. That's why you didn't find it at google.

The only thing you can do for now and not change the core of grocery CRUD is to use the set_model function ( http://www.grocerycr...tions/set_model ) . So in your case you just have to create a model that it will look something like this (the name Users_join is just an example):

<?php

class Users_join extends grocery_CRUD_Model

{

//The function get_list is just a copy-paste from grocery_CRUD_Model

	function get_list()

	{

	 if($this->table_name === null)

	  return false;

	

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

	

  // ADD YOUR SELECT FROM JOIN HERE, for example: <------------------------------------------------------

  // $select .= ", user_log.created_date, user_log.update_date";



	 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,'{'))

		$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'";

	  }

	

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

	

  // ADD YOUR JOIN HERE for example: <------------------------------------------------------

  // $this->db->join('user_log','user_log.user_id = users.id');



	 $results = $this->db->get($this->table_name)->result();

	

	 return $results;

	}

}

You can download the file from [attachment=89:users_join.php]

So in any case you have to create a separate file for each let's say different case.

[b]Important note: [/b]Before using the set_model method make sure that you totally understand how the set_relation and set_relation_n_n works. I am telling this for the reason that the set_model is suggested to use it from more let's say familiar users with grocery CRUD

[R2D2]

I will take a look at it!
thanks a lot johnny!

[j-gun]

Hi Johnny

Your custom model is work well when get_list function is called, but what about the edit function? Fields from get_lists is not equal if i edit that record.

Thanks a lot

[j-gun]

[quote name='web-johnny' timestamp='1333188714' post='1018']
Hello [member='R2D2'] and welcome to the forum,

Actually perhaps it seems obvious to grocery CRUD to have joins and customs queries to the table, but it still NOT an available feature at this moment. That's why you didn't find it at google.

The only thing you can do for now and not change the core of grocery CRUD is to use the set_model function ( http://www.grocerycr...tions/set_model ) . So in your case you just have to create a model that it will look something like this (the name Users_join is just an example):

<?php

class Users_join extends grocery_CRUD_Model

{

//The function get_list is just a copy-paste from grocery_CRUD_Model

function get_list()

{

if($this->table_name === null)

return false;



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



// ADD YOUR SELECT FROM JOIN HERE, for example: <------------------------------------------------------

// $select .= ", user_log.created_date, user_log.update_date";



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,'{'))

$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'";

}



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



// ADD YOUR JOIN HERE for example: <------------------------------------------------------

// $this->db->join('user_log','user_log.user_id = users.id');



$results = $this->db->get($this->table_name)->result();



return $results;

}

}

You can download the file from [attachment=89:users_join.php]

So in any case you have to create a separate file for each let's say different case.

[b]Important note: [/b]Before using the set_model method make sure that you totally understand how the set_relation and set_relation_n_n works. I am telling this for the reason that the set_model is suggested to use it from more let's say familiar users with grocery CRUD
[/quote]

@Johnny
I've succeed using my custom model only for get_list() and display selected record for update purpose. Because of using custom model, the grocery CRUD didn't work well for action INSERT, UPDATE & DELETE.

Why it happens?

Here is my custom model:

class Users_join extends grocery_CRUD_Model

{

    function get_list()

    {

	 if($this->table_name === null)

	  return false;

	

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

	

  // ADD YOUR SELECT FROM JOIN HERE <------------------------------------------------------

  // for example $select .= ", user_log.created_date, user_log.update_date";

  $select .= ", users.*";

 

	 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,'{'))

	    $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'";

	  }

	 

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

	

  // ADD YOUR JOIN HERE for example: <------------------------------------------------------

  // $this->db->join('user_log','user_log.user_id = users.id');

  $this->db->join('users','users.id = '. $this->table_name . '.user_id');

 

	 $results = $this->db->get($this->table_name)->result();

	

	 return $results;

    }

And here is my grocery CRUD function:

function teachers_display(){

  $crud = new grocery_CRUD();

	    $crud->set_model('users_join');

  $crud->set_theme('flexigrid');

	    $crud->set_table('teachers','users');

	    $crud->set_subject('teacher');

  $crud->columns('user_id','name','address','phone','city','username');

  $crud->required_fields('name');

  $crud->change_field_type('user_id','invisible');

  $crud->fields('user_id','name','address','phone','city','username');

 

	    $output = $crud->render();

	    $this->_crud_output($output);

}

big thanks for your kindly reply

[web-johnny]

[member='j-gun'] the only way to change the [color=#282828][font=helvetica, arial, sans-serif]INSERT, UPDATE & DELETE is by using callbacks. This only way to do it for now is to use callbacks. You can take a look at :[/font][/color]

http://www.grocerycr...callback_update (callback_update)
http://www.grocerycr...callback_insert (callback_insert)
http://www.grocerycr...callback_delete (callback_delete)

Cheers
Johnny

[j-gun]

Hi Johhny,

CMIIW, callback is the function that triggered by add, delete and save button? Unfortunately the add, delete and save button is not working at all. It calls quandary

thanks for your kindly reply

[j-gun]

@johnny

I already solve my join table problem using set_model and override your grocery_crud_model function (insert, update, delete). Thanks Johnny

[Masmuh Vítězství]

How about searching filter, did'nt work .

[Ankit Agarwal]

Please can you me the code example on how to do add/edit/view for multiple table..i am stuck on my project

[Lucero Chavez]

Hi everyone, I made this solution but it seems to have problems in my case because I have tables with a column with the exactly same name. It looks like its overriding the values. 

 

the structure is : User (columns: id, name, etc....) and Test (id, name, etc...) 

 

Why is this happening? Thank you for your response in advance! 

[Lucero Chavez]

Well, at the end I had to change the name of my columns from those tables and it worked well, it wasnt the most efficient way to do it I guess but it works that way... I guess that when columns have the same name from 2 tables in this join, it justs overrides and shows only one of the columns from the first table. Hope it is useful to anybody who had the same problem!

[chest94]

Hello [member='R2D2'] and welcome to the forum,

Actually perhaps it seems obvious to grocery CRUD to have joins and customs queries to the table, but it still NOT an available feature at this moment. That's why you didn't find it at google.

The only thing you can do for now and not change the core of grocery CRUD is to use the set_model function ( http://www.grocerycr...tions/set_model ) . So in your case you just have to create a model that it will look something like this (the name Users_join is just an example):
 

<?php
class Users_join extends grocery_CRUD_Model
{
//The function get_list is just a copy-paste from grocery_CRUD_Model
	function get_list()
	{
	 if($this->table_name === null)
	  return false;
	
	 $select = "{$this->table_name}.*";
	
  // ADD YOUR SELECT FROM JOIN HERE, for example: <------------------------------------------------------
  // $select .= ", user_log.created_date, user_log.update_date";

	 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,'{'))
		$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'";
	  }
	
	 $this->db->select($select, false);
	
  // ADD YOUR JOIN HERE for example: <------------------------------------------------------
  // $this->db->join('user_log','user_log.user_id = users.id');

	 $results = $this->db->get($this->table_name)->result();
	
	 return $results;
	}
}

You can download the file from users_join.php

So in any case you have to create a separate file for each let's say different case.

Important note: Before using the set_model method make sure that you totally understand how the set_relation and set_relation_n_n works. I am telling this for the reason that the set_model is suggested to use it from more let's say familiar users with grocery CRUD

 

 

Hi, this worked just fine for me. Thank you very much.

 

Now I have another problem:

I've got a variable from my controller and I want to add it to a where clause in my view.

 

In more exact words:

I've got a function "employees($id_enterprise)" and I want to set the crud based on that id.

 

I've put a where clause in the model you posted like this

$this -> db -> where('enterprise.id_enterprise = 1');

And it worked fine, but I need that value as a variable.

 

Thank you for your support.

[David R.]

Hi everyone, 

 

Does anybody have a clue ?

Same problem here : need to extend grocery_CRUD_Model to add a specific join, but what if the where clause depends on a variable ?

 

Thanks for your help

[woof]

Just one stupid newbie question:

 

I got it working joining a second tables values, and they appear in list view.

 

But when I open edit dialog these values are not set. Do I need to expand "get_row()" as well and how?

 

Thanx.

[woof]

Hello,

 

okay, I managed on my own, "get_edit_values()" needs some extension. I did it like this:

function get_edit_values($primary_key_value) {
    $primary_key_field = $this->get_primary_key();
    $this->db->where($primary_key_field,$primary_key_value);
    $result = $this->db->get($this->table_name)->row();
    
    if ($result) {
        $data = $this->db->query("SELECT ... WHERE id=" . $result->id . ";");
        foreach ($data->result() as $row) {
            ...
        }
    }
    return $result;
}

Now I just need to extend "db_update()", "db_insert()" and "db_delete()" also...

[woof]

Another question on this. Is it possible to extend query used for filter in "flexigrid" theme?

[betaPictoris]

Thank you Johnny, this is very helpful.

 

Since I'm using it with CI 3, I had to fix it, according to what is explained there.

In brief, the from has to be performed BEFORE the joins, and the call to db->get has to omit the table name, so that no from is performed during the get.

// ADD YOUR JOIN HERE for example: <------------------------------------------------------
$this->db->from($this->table_name); // NEW and required
// $this->db->join('user_log','user_log.user_id = users.id');

// NO MORE $results = $this->db->get($this->table_name)->result();
$results = $this->db->get()->result();// NEW and required