⚠ 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

Join Tables



R2D2

R2D2
  • profile picture
  • Member

Posted 30 March 2012 - 18:49 PM

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

R2D2
  • profile picture
  • Member

Posted 31 March 2012 - 08:47 AM

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

web-johnny
  • profile picture
  • Administrator
  • 1,166 posts

Posted 31 March 2012 - 10:11 AM

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

R2D2
  • profile picture
  • Member

Posted 31 March 2012 - 10:58 AM

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

j-gun

j-gun
  • profile picture
  • Member

Posted 24 September 2012 - 02:51 AM

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

j-gun
  • profile picture
  • Member

Posted 24 September 2012 - 04:39 AM

[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

web-johnny
  • profile picture
  • Administrator
  • 1,166 posts

Posted 24 September 2012 - 08:08 AM

[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

j-gun
  • profile picture
  • Member

Posted 25 September 2012 - 00:26 AM

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

j-gun
  • profile picture
  • Member

Posted 01 October 2012 - 05:28 AM

@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í

Masmuh Vítězství
  • profile picture
  • Member

Posted 26 February 2013 - 05:38 AM

How about searching filter, did'nt work .


Ankit Agarwal

Ankit Agarwal
  • profile picture
  • Member

Posted 26 October 2013 - 08:36 AM

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

Lucero Chavez
  • profile picture
  • Member

Posted 14 January 2014 - 06:29 AM

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

Lucero Chavez
  • profile picture
  • Member

Posted 14 January 2014 - 07:25 AM

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

chest94
  • profile picture
  • Member

Posted 27 November 2014 - 15:35 PM

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 attachicon.gifusers_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.

David R.
  • profile picture
  • Member

Posted 01 December 2015 - 01:10 AM

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

woof
  • profile picture
  • Member

Posted 02 December 2015 - 21:31 PM

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

woof
  • profile picture
  • Member

Posted 04 December 2015 - 06:28 AM

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

woof
  • profile picture
  • Member

Posted 16 December 2015 - 05:47 AM

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


betaPictoris

betaPictoris
  • profile picture
  • Member

Posted 18 October 2018 - 08:04 AM

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