Jump to content


Join Tables


  • Please log in to reply
12 replies to this topic

#1 R2D2

R2D2

    Newbie

  • Members
  • Pip
  • 7 posts

Posted 30 March 2012 - 06: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!

#2 R2D2

R2D2

    Newbie

  • Members
  • Pip
  • 7 posts

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 :(

#3 web-johnny

web-johnny

    grocery CRUD Author

  • Administrators
  • 927 posts
  • LocationLondon

Posted 31 March 2012 - 10:11 AM

Hello 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 Attached File  users_join.php   1.49K   764 downloads

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
Posted Image

#4 R2D2

R2D2

    Newbie

  • Members
  • Pip
  • 7 posts

Posted 31 March 2012 - 10:58 AM

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

#5 j-gun

j-gun

    Member

  • Members
  • PipPip
  • 17 posts

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

#6 j-gun

j-gun

    Member

  • Members
  • PipPip
  • 17 posts

Posted 24 September 2012 - 04:39 AM

Hello 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 Attached File  users_join.php   1.49K   764 downloads

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


@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

#7 web-johnny

web-johnny

    grocery CRUD Author

  • Administrators
  • 927 posts
  • LocationLondon

Posted 24 September 2012 - 08:08 AM

j-gun the only way to change the 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 :

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

Cheers
Johnny
Posted Image

#8 j-gun

j-gun

    Member

  • Members
  • PipPip
  • 17 posts

Posted 25 September 2012 - 12: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

#9 j-gun

j-gun

    Member

  • Members
  • PipPip
  • 17 posts

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

#10 Masmuh Vítězství

Masmuh Vítězství

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 26 February 2013 - 05:38 AM

How about searching filter, did'nt work .



#11 Ankit Agarwal

Ankit Agarwal

    Newbie

  • Members
  • Pip
  • 6 posts

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



#12 Lucero Chavez

Lucero Chavez

    Newbie

  • Members
  • Pip
  • 2 posts
  • LocationPeru

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! 



#13 Lucero Chavez

Lucero Chavez

    Newbie

  • Members
  • Pip
  • 2 posts
  • LocationPeru

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!






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users