[maulnick]

Im sure it's easy for you guys but it makes me sick all day to think it. So please help me.

Let me see the example http://www.grocerycrud.com/documentation/options_functions/set_relation

My question is how to add others fields in 'offices' table to the joint table, for example I wanna add 'postalCode' and 'country' fields from offices table.

here the example code
--


[color=#000000][b]function[/b][/color] employees_management[color=#66CC66]([/color][color=#66CC66])[/color]
[color=#66CC66]{[/color]
[color=#0000FF]$crud[/color] = [color=#000000][b]new[/b][/color] grocery_CRUD[color=#66CC66]([/color][color=#66CC66])[/color];

[color=#0000FF]$crud[/color]->[color=#006600]set_theme[/color][color=#66CC66]([/color][color=#FF0000]'datatables'[/color][color=#66CC66])[/color];
[color=#0000FF]$crud[/color]->[color=#006600]set_table[/color][color=#66CC66]([/color][color=#FF0000]'employees'[/color][color=#66CC66])[/color];
[color=#0000FF]$crud[/color]->[color=#006600]display_as[/color][color=#66CC66]([/color][color=#FF0000]'officeCode'[/color],[color=#FF0000]'Office City'[/color][color=#66CC66])[/color];
[color=#0000FF]$crud[/color]->[color=#006600]set_subject[/color][color=#66CC66]([/color][color=#FF0000]'Employee'[/color][color=#66CC66])[/color];

[color=#0000FF]$crud[/color]->[color=#006600]set_relation[/color][color=#66CC66]([/color][color=#FF0000]'officeCode'[/color],[color=#FF0000]'offices'[/color],[color=#FF0000]'city'[/color][color=#66CC66])[/color];

[color=#0000FF]$output[/color] = [color=#0000FF]$crud[/color]->[color=#006600]render[/color][color=#66CC66]([/color][color=#66CC66])[/color];

[color=#0000FF]$this[/color]->_example_output[color=#66CC66]([/color][color=#0000FF]$output[/color][color=#66CC66])[/color];
[color=#66CC66]}[/color]


--

here my code

--

[indent=1]function employees_management()[/indent]
[indent=1]{[/indent]
[indent=2]$crud = new grocery_CRUD();[/indent]

[indent=2]$crud->set_theme('datatables');[/indent]

[indent=2]$crud->set_table('employees');[/indent]

[indent=2]$crud->set_relation('officeCode','offices','city');[/indent]

[indent=2]$crud->columns('lastName','firstName','postalCode','country');[/indent]

[indent=2]$crud->set_subject('Employee');[/indent]

[indent=2]$output = $crud->render();[/indent]

[indent=2]$this->_example_output($output);[/indent]
[indent=1]}[/indent]
--

it will give the column 'postalCode' and 'country' blank

Thanks for your kindness for repply my question.

Im sorry for my poor english as well

[noskov.biz]

Hi, maulnick!
If I understood correctly, you want have in your "employees" section few fileds that will be in relationship with corresponding fields in "offices" table, am I right? If so, you have to provide these fields in "employees" table in order to have 1 to n relationship between 1 field from "employees" table to n fields in "offices" table.

When you use grocery CRUD set_relation you have to specify in your function each field that will be in relationship with other field from the related table. So in you code it will be similar to this example:

...

$crud->set_relation('officeCode','offices','postalCode');

$crud->set_relation('postalCode','offices','postalCode');

$crud->set_relation('officeCountry','offices','country');

...

P.S. Please, use button "code" to wrap your code examples. It will be more readable for other members

[maulnick]

First Im sorry for my code that was not wrapped. It was my first posting.

Do you mean I should add 'postalCode attribute' to "employees" table ?

I just wanna show other fields (offices) to the joint table. I have made it. Here is my code

[php]function employees_management() { $crud = new grocery_CRUD(); $crud->set_theme('datatables'); $crud->set_table('employees'); $crud->set_relation('officeCode','offices','{postalCode} and {country}'); $crud->set_subject('Employee'); $output = $crud->render(); $this->_example_output($output); }[/php]

Here is the result. But it doesnt seperated become two columns. How can I make it become two colums that will consist of 'country' column and 'postalCode' column ?

[img]http://i46.tinypic.com/oseixy.jpg[/img]

But thanks for your answer noskov.biz. I still hope you can help me.

[noskov.biz]

You are welcome! It doesn't separate office code and country, because you have combined them in you code:

$crud->set_relation('officeCode','offices','{postalCode} and {country}');

As you see the third parameter in your set_relation function is "{postalCode} and {country}". So you get "75017 and France" in the result.

If you want to have postalCode, country and something else from "offices" table, you have to create in your "employees" table new fields for that. For example: "zip_code" (for "postalCode"), "office_country" (for "country") and so on.

And in you code you will write something like this:

function employees_management()

{

  $crud = new grocery_CRUD();

  $crud->set_subject('Employee');

  $crud->set_theme('datatables');

  $crud->set_table('employees');

  $crud->set_relation('zip_code','offices','postalCode');

  $crud->set_relation('office_country', 'offices', 'country');

  // other 1 to n relations between you tables

  $output = $crud->render();

  $this->_example_output($output);

}

[clustersblue]

[quote]Hi noskov.biz

I have similar question with maulnick.

Mine is like this, I have two tables (charters and sessions). On the charter table it has these fields:

[u]Charter[/u]
charter_id
charter_code
charter_name

While in the sessions:

[u]Sessions[/u]
session_id
charter_id
session_name

Then my code is this

$crud->set_table('session')

		 ->set_subject('Session')

		 ->set_relation('charter_id','charter','charter_code');

Do you mean that if I want to display charter_code and charter_name on separate fields I need to add it like this?

[u]Sessions[/u]
session_id
charter_id
session_name
[color=#ff0000]charter_name[/color]

And the code will be like this:

$crud->set_table('session')

		 ->set_subject('Session')

		 ->set_relation('charter_id','charter','charter_code')

		 ->set_relation('charter_name','charter','charter_name');

Sorry I just couldn't get what you mean and If you don't mind if you can elaborate it further. I'm just started using GC and I'm not that familiar on how it really works.

Thank You[/quote]

I figured it out. What I did was it would be like there will be two charter_id in my Sessions table like what I just did above. Then, when I populated the session table I assigned [i]charter_name[/i] with the same data as [i]charter_id[/i] and that's it I am now able to see the two data in two separate fields in my session GC table. This part is now solved. Thanks noskov.biz and maulnick!

[clustersblue]

Hi,

Since my next question is somewhat related to this topic I decided to have this continued here instead of creating a new item in the forum.

Here is my scenario (I'll be using above example plus addition "Locations" table):

[b][u]Charter[/u][/b]
charter_id
charter_code
charter_name
location_id

While in the sessions:

[b][u]Sessions[/u][/b]
session_id
charter_id
session_name
location_id

And here is another table called locations

[b]Locations[/b]
location_id
location_name
disabled_id

$crud->set_table('sessions');

	 ->set_relation('charter_id','charter','charter_code')

	 ->set_relation_n_n('location_name', 'charter', 'locations', 'location_id', 'charter_id', 'location_name',NULL, array('disabled_id'=>'0'));

Here's what the flexigrid should look like:

[b]Charter ID Session ID Location Name [/b]
1 1 First Floor


What I wanted is to display in the grid are the following: charter id, session id and the location name - depending on the location id set in Charter table. If I run the code above I have a display but under the Location Name all locations were displayed, what I expect is only one base on the location id I set in the charter table. Is this possible in GC?

Thank You!

[noskov.biz]

Hi, clustersblue and sorry for the delayed answer.
Why don't you use just set_relation() function instead of set_relation_n_n? It seems to me that it would be easier:

$crud->set_relation('charter_id', 'charter', 'charter_code');

$crud->set_relation('location_id', 'locations', 'location_name', array('disabled_id' => '0'));

Sorry, if I've misunderstood something.

[clustersblue]

Hi noskov.biz no worries it's fine.

It seems it is me needs to apologize Sorry for the confusion. "Sessions" should not have location_id it is only in the Charter. That's the reason I didn't used set_relation(). Typo error.

I wanted to display the location name in the session flexigrid that is why I set a relation with the charter since it is the only table that has a relationship with the location table. I can do this in pure php using join but I could not figure it out in GC. In the GC documentation it doesn't says if set_relation has similar function with CI join function.

Hope you can help... I do have workaround for this already using callback_column. I was able to get the charter_id from each row and from that id I made a callback function that creates a mysql query using that id, the the return value is the location_name. It indeed displays the location_name but with this approach you cannot create column search on that column. I'm not sure why.

Thanks
Clustersblue

[noskov.biz]

You're welcome!

Mmm, at the previous post I meant that why not to create one more field "location_id" (such as the typo in your example ) at the Sessions and why not to use just set_relation() function? And not to combine fields from Charters with fields from Location, but set the relation "sessions.location_id" to "locations.location_name".

I do not really understand what the relation between Charters and Locations and how do you set it?
If you can, please, post here the db dump of those tables and you controller (or some functions from it).

Cheers!

[clustersblue]

Hi noskov.biz

You are right! My client realized that he needs to have separate location for Session table and not to relate it in Charters. I thought this is already be resolved but not yet Location is no longer a problem now, but he wanted another field to be displayed the same with Location but the problem is, it is only available in the Charter table. This field is Testing Type.

To picture out the system: A user will input a request (a Charter) that have the following fields:[list]
[*]charter_id
[*]charter_code
[*]charter_title
[*]operator_id
[*]category_id
[*][b]testing_type_id [/b]
[*]request_priority_id
[*]status_id
[*]requested_completion_date
[*]comments
[*]specific_notes
[*]configuration_requirements
[*]created_date
[/list]
After creating a charter, it will be reviewed and then with a click of a button the system will create sessions from the charter base on several criteria. So, it is expected that in a charter there are several sessions related to it. The Session table has the following fields: I truncated some of them for to me they are no longer needed it might overwhelm you with so many fields that are no longer relevant. [list]
[*]session_id
[*]charter_id
[*]session_code
[*]location_id
[*]operator_id
[*]session_status_id
[*]session_start_dttm
[*]session_end_dttm
[*]....
[*]connectivity_id
[*]task_id
[*]charter_id_rc_date
[/list]
And here is the fields for the Testing Type table:[list]
[*]testing_type_id
[*]testing_type_name
[*]disabled_id
[/list]
Since my goal here is to display the Sessions in flexigrid with the following requirements (columns to be displayed in the grid) :

Charter ID | Session ID | Session Start Date/Time | Session End Date/Time | Location | ... | Type of Testing | ...

Type of Testing (testing_type_id) is only available in the Charter table the reason I need a relation to it. We don't need to have another Type of Testing field in the Session since it will be redundant.

I hope this is clear now but let me know if it is not. I will be glad to discuss this with you more if you need to.

I really appreciate your help!

Thanks,
Clustersblue

[noskov.biz]

Hi, clustersblue and you're welcome!
It seems to me that adding only one field 'testing_type' to Sessions table would be much easier and less "redudant", then trying to combine it with Charters table.

Let's see. We add new field 'testing_type' to Sessions and make a relation:

$crud->set_relation('testing_type', 'testing', 'testing_type_id');

// or

// $crud->set_relation('testing_type', 'testing', 'testing_type_name');

and that's all.

In the other hand we could not add this field and then we have to make:

$crud->set_relation_n_n('type_of_testing', 'charters', 'testing', 'charter_id', 'testing_type_id', 'testing_type_name', null, array('disabled_id' => '0'));

but in this case we have 'charters.charters_id' is the primary key. And we will have the php-error, that we try to duplicate the primary key, if we will add more then 1 charters. So we need to add another field to Charters for example 'session_charter_id' and make:

$crud->set_relation_n_n('type_of_testing', 'charters', 'testing', 'session_charter_id', 'testing_type_id', 'testing_type_name', null, array('disabled_id' => '0'));

it will work, but in the result we will have much more entries at the Charters table, as it is the relation table.
Or we have to create another table that would be relation table for that purpose.

Well, what do think about that?

[clustersblue]

hmmm. The first idea seems feasible to me. I just need to update my session table with the additional testing_type_id. I'll check if it will not impact my database reliability during changes to its data specifically if there is any update anomaly. I'll let you know.

As to the second, I don't think this is doable as well. I don't want Charters to be affected if there are changes to its sessions. And Charter will consist of 1 or more sessions related to it. I don't know what will happen if a charter will generates more than 1 sessions does it mean I will have more than 1 charter that will correspond to the number of sessions created? If that is the case this is not doable.

We can simply do this using pure mysql join query, do you think this is something to be updated in GC?

Thanks noskov.biz for providing me lots of inputs. It is not only this framework easy to deal with, but also its support.

[noskov.biz]

Hi there! You're welcome and sorry for the delayed answer

[quote][color=#282828][font=helvetica, arial, sans-serif]does it mean I will have more than 1 charter that will correspond to the number of sessions created?[/font][/color][/quote]
The result will be something like that:
[b]Charters table[/b]
charter_id | session_charter_id | testing_type_id | ...
1 1 1
[color=#ff0000]2[/color] [color=#ff0000]2[/color] 2 -> we will give two charters with charter_id #2 and #3
[color=#ff0000]3[/color] [color=#ff0000]2[/color] 1 for the the one session #2 and with testing types # 2 and 1
4 3 3

[b]Sessions table[/b]
session_id | charter_id | ...
1 1
2 2
3 4

[b]Testing table[/b]
testing_type_id | ...
1
2
3

As for mysql or CodeIgniter Active Record. Yes, you can easilly make it and use further in you code. For example in you controller you write:

$query = "SELECT charter_id FROM charters ...";

// or

$ci_query = $this->db->select('charter_id')->from('charters')...

and then you can use $(ci_)query for example:

$crud->set_relation('session_charter_id', 'charters', 'charter_id', array('testing_type_id' => $query));

In addition you can make more complicated code with the set_model() function. But in this case it seems to me that it's not really needed. But you can do this as well.

Best regards!

[clustersblue]

I like your idea. But in my case and for this case I just added additional testing_type_id in the session table for easy relation (this is from your first suggestion ). But, I think I will use your idea on my other column in the session table. Thanks for posting it here.

[gusdewa]

Hi all.,  Suppose I have a relation as follow:

 

If I set employees as the main table, How to display city, state, and country as column in the data grid? 

Thanks a lot for answering my silly question. :)

 

updated;

I have make a dummy column in my employees table, i.e.,city_code, state_code, and country_code. However, they are not displayed in the datagrid. The values are not linked to the corresponding field of offices table.

[davidoster]

Hello and welcome to the forums [member=gusdewa].

You need to use the set_relation function in order to show the extra fields like city_code, state_code, and country_code with the 

{} braces.

Check the manual.