Foreign Keys in Junction Table - Resolved

Talk about foreign keys

Moderators: ne_moj, zewa, vadimako, ne_moj, zewa, vadimako

dynsight
Expert
Expert
Posts: 99
Joined: Mar 3rd, '11, 03:05

Foreign Keys in Junction Table - Resolved

Postby dynsight » Jun 4th, '11, 21:45

I have a simple junction table with many to many (see attached image)
jtable.png
Basic Layout
jtable.png (43.15 KiB) Viewed 1971 times

gal_cat_frames is essential an index, and a FK to gal_frames and a FK to gal_category. I am using the most recent version of the datagrid (675)

This is the query I use to populate the table

Code: Select all

$sql="SELECT gcf.id,gc.catName, gcf.catID, gf.frameName, gcf.frameID
FROM gal_category gc INNER JOIN gal_cat_frames gcf ON gc.id = gcf.catID INNER JOIN gal_frames gf ON gcf.frameID = gf.id";

and as  a separate option

  $sql="SELECT gcf.id, gcf.catID,gcf.frameID
FROM gal_cat_frames gcf;


Here is my view mode.... Note. I have tried various combinations of fields to be listed in the Foreign key, field key and field name fields... never got the results that I expected. I want to see the category name and frame name in the dropdown... NOT the indexes, which is what is appearing.

Code: Select all

$vm_columns=array(
   
   "catID"=>array("header"=>"Category Name","type"=>"foreign_key"),
   
   "frameID"=>array("header"=>"Frame Name","type"=>"foreign_key"),

   
   );
   
   $foreign_keys = array(
"catID"=>array("table"=>"gal_category", "field_key"=>"catID", "field_name"=>"catName", "view_type"=>"dropdownlist", "radiobuttons_alignment"=>"horizontal|vertical", "condition"=>"", "order_by_field"=>"", "order_type"=>"ASC", "on_js_event"=>""),

"frameID"=>array("table"=>"gal_frames", "field_key"=>"frameID", "field_name"=>"frameName", "view_type"=>"dropdownlist", "radiobuttons_alignment"=>"horizontal|vertical", "condition"=>"", "order_by_field"=>"", "order_type"=>"ASC", "on_js_event"=>""),


);


and here is the edit view (again, I have tried various options for the field key and field name, and cannot get this to work:

Code: Select all

  // edit page
     $em_columns=array(
     "catID"=>array("header"=>"Category Name","type"=>"foreign_key"),
   
   "frameID"=>array("header"=>"Frame Name","type"=>"foreign_key"),
     );
    
    $foreign_keys = array(
"catID"=>array("table"=>"gal_category", "field_key"=>"catName", "field_name"=>"catName", "view_type"=>"dropdownlist", "radiobuttons_alignment"=>"horizontal|vertical", "condition"=>"", "order_by_field"=>"", "order_type"=>"ASC", "on_js_event"=>""),

"frameID"=>array("table"=>"gal_frames", "field_key"=>"frameName", "field_name"=>"frameName", "view_type"=>"dropdownlist", "radiobuttons_alignment"=>"horizontal|vertical", "condition"=>"", "order_by_field"=>"", "order_type"=>"ASC", "on_js_event"=>""),


);
      $dgrid->SetAutoColumnsInEditMode(false);
     $dgrid->SetColumnsInEditMode($em_columns);


I would like to see, when adding or editing a record inline, the complete list of frames from gal_frames, as well as the complete list of categories from gal_category. This way the EU can quickly assemble a list of category/frames by selecting values from two dropdowns.

Guidance and help appreciated.
Last edited by dynsight on Jun 5th, '11, 21:37, edited 1 time in total.

dynsight
Expert
Expert
Posts: 99
Joined: Mar 3rd, '11, 03:05

Re: Struggling with Foreign Keys in Junction Table (many to many

Postby dynsight » Jun 4th, '11, 23:10

The following code for the foreign keys array does not work either... I believe this is the proper format as listed in
viewtopic.php?f=33&t=1541

Code: Select all

  // edit page
     $em_columns=array(
     "catID"=>array("header"=>"Category Name","type"=>"foreign_key","req_type"=>"ri"),
   
   "frameID"=>array("header"=>"Frame Name","type"=>"foreign_key","req_type"=>"ri"),
     );
    
    $foreign_keys = array(
"catID"=>array("table"=>"gal_category", "field_key"=>"id", "field_name"=>"catName", "view_type"=>"dropdownlist", "radiobuttons_alignment"=>"horizontal|vertical", "condition"=>"", "order_by_field"=>"", "order_type"=>"ASC", "on_js_event"=>""),

"frameID"=>array("table"=>"gal_frames", "field_key"=>"id", "field_name"=>"frameName", "view_type"=>"dropdownlist", "radiobuttons_alignment"=>"horizontal|vertical", "condition"=>"", "order_by_field"=>"", "order_type"=>"ASC", "on_js_event"=>""),


);
      $dgrid->SetAutoColumnsInEditMode(false);
     $dgrid->SetColumnsInEditMode($em_columns);

administrator
Site Admin
Site Admin
Posts: 5992
Joined: Jan 7th, '09, 23:18
Contact:

Re: Struggling with Foreign Keys in Junction Table (many to many

Postby administrator » Jun 5th, '11, 08:40

Foreign keys feature in DataGrid doesn't support model many-to-many, only one-to-many.

dynsight
Expert
Expert
Posts: 99
Joined: Mar 3rd, '11, 03:05

Re: Struggling with Foreign Keys in Junction Table (many to many

Postby dynsight » Jun 5th, '11, 12:46

So How will I be able to populate rows in gal_cat_frames so that I can use a dropdown for each item? Maybe I described the many to many aspect incorrectly...

Basically, I want the user to just see the values from the dropdown lists (gal_category, gal_frames) and not the indexes. I am a bit surprised that this cannot be done...should I switch the KoolPHP grid? I would lose a lot of development time, and not use a tool I already purchased.

Is there a method of creating a custom ADD and Edit form... That is, when I click edit, I go to a PHP form of my own design, which would partially solve my issue, but make it a bit more inconvenient for my customers.

Thanks

dynsight
Expert
Expert
Posts: 99
Joined: Mar 3rd, '11, 03:05

Re: Struggling with Foreign Keys in Junction Table (many to many

Postby dynsight » Jun 5th, '11, 13:53

I am a bit surprised that this cannot be done the way that I want to... based upon the demos and examples... this may be me poorly explaining it, and I do apologize...

Here is my edit mode code...

Code: Select all

  $em_table_properties=array("width"=>"400px");
     $dgrid->SetEditModeTableProperties($em_table_properties);
    
    
     $table_name = "gal_cat_frames";
      $primary_key = "id";
      $condition = "";
      $dgrid->SetTableEdit($table_name, $primary_key, $condition);
    
     // edit page
     $em_columns=array(
     "catID"=>array("header"=>"Category Name","type"=>"foreign_key","req_type"=>"ri"),
   
   "frameID"=>array("header"=>"Frame Name","type"=>"foreign_key","req_type"=>"ri"),
     );
    
    $foreign_keys = array(
"catID"=>array("table"=>"gal_category", "field_key"=>"id", "field_name"=>"catName", "view_type"=>"dropdownlist", "radiobuttons_alignment"=>"horizontal|vertical", "condition"=>"", "order_by_field"=>"", "order_type"=>"ASC", "on_js_event"=>""),

"frameID"=>array("table"=>"gal_frames", "field_key"=>"id", "field_name"=>"frameName", "view_type"=>"dropdownlist", "radiobuttons_alignment"=>"horizontal|vertical", "condition"=>"", "order_by_field"=>"", "order_type"=>"ASC", "on_js_event"=>""),


);
      $dgrid->SetAutoColumnsInEditMode(false);
     $dgrid->SetColumnsInEditMode($em_columns);


Here is my view mode results:
viewmode.png
viewmode.png (88.16 KiB) Viewed 1955 times


And here is what I get in edit mode:
editmode.png
editmode.png (95.84 KiB) Viewed 1962 times


Instead of the numbers appearing in dropdown mode, I would like to see ALL of the values of the gal_category and gal_frames in a dropdown...one in each column. Can this be done?

dynsight
Expert
Expert
Posts: 99
Joined: Mar 3rd, '11, 03:05

Re: Foreign Keys in Junction Table - Resolved

Postby dynsight » Jun 5th, '11, 21:43

I left out the line:

$dgrid->SetForeignKeysEdit($foreign_keys);

Which, BTW is not in the HOWTO: listed here:
viewtopic.php?f=33&t=1541

Regarding this help screen, adding this line would help, as well as correcting the table name... your example gives the table name as "Country," BUT your table property in the $foreign_keys array is "Countries"

To resolve this, I patiently read through this information...

http://www.apphp.com/php-datagrid/index ... ng_started
Which was quite useful.

Here is the code that I used:


Code: Select all

 // set settings for edit/details mode
      $em_table_properties=array("width"=>"400px");
     $dgrid->SetEditModeTableProperties($em_table_properties);
    
    
     $table_name = "gal_cat_frames";
      $primary_key = "id";
      $condition = "";
      $dgrid->SetTableEdit($table_name, $primary_key, $condition);
    
     // edit page columns
     $em_columns=array(
    "id"=>array("type"=>"hidden"),
     "catID"=>array("header"=>"Category Name","type"=>"foreign_key",),
   
   "frameID"=>array("header"=>"Frame Name","type"=>"foreign_key",),
     );
    
    $foreign_keys = array(
"catID"=>array("table"=>"gal_category", "field_key"=>"id", "field_name"=>"catName", "view_type"=>"dropdownlist",  "condition"=>"", "order_by_field"=>"catName", "order_type"=>"ASC", "on_js_event"=>""),

"frameID"=>array("table"=>"gal_frames", "field_key"=>"id", "field_name"=>"frameName", "view_type"=>"dropdownlist", "condition"=>"", "order_by_field"=>"frameName", "order_type"=>"ASC", "on_js_event"=>""),
);
     // $dgrid->SetAutoColumnsInEditMode(false);
   
    $dgrid->SetForeignKeysEdit($foreign_keys);
     $dgrid->SetColumnsInEditMode($em_columns);


Return to “Field type: "foreign key"”