Working w/ Link tables

dynasoft

Hi

I'm trying to implement a link table called GlobalSubsPacksRelations below. It stores IDs from GlobalSubsPacks.ID via GlobalSubsPacksRelations.SubPackID. It's a one-to-many relationship between these 2 tables as GlobalSubsPacksRelations effectively stores IDs from various other tables via GlobalSubsPacksRelations.ParamID, one being GlobalPacksVoiceParam.ID. GlobalSubsPacks stores sales bundles the parts of which are stored in GlobalSubsPacksRelations via ParamID. There is one line in GlobalSubsPacksRelations per bundle part.

I am trying to display in a DT all the bundles and also set fields such as GlobalPacksVoiceParam.CustomerType so I can show the individual parts of bundles in an Editor form. I keep getting error 'Invalid column name 'SubPackID'' with code below. Any help would be greatly appreciated.

• Server:

          HttpRequest formData = HttpContext.Current.Request;
  
          using (Database db = new Database(SetGetDbType2, SetGetDbConnection))
          {
              editor = new Editor(db, "GlobalSubsPacks", "GlobalSubsPacks.id")
                  .Model<SubsPacksDBModel.GlobalSubsPacks>("GlobalSubsPacks")
                  .Model<SubsPacksDBModel.GlobalSubsPacksRelations>("GlobalSubsPacksRelations");
  
              editor.MJoin(new MJoin("GlobalPacksVoiceParam")
                  .Model<SubsPacksDBModel.GlobalPacksVoiceParam>()
                  .Name("GlobalPacksVoiceParam.CustomerType")
                  .Link("GlobalPacksVoiceParam.id", "GlobalSubsPacksRelations.ParamID")
                  .Link("GlobalSubsPacks.id", "GlobalSubsPacksRelations.SubPackID")
                  .Field(new Field("id")
                      .Options(new Options()
                          .Table("GlobalPacksVoiceParam")
                          .Value("id")
                          .Label("CustomerType")
                      )
                      .Set(false)
                  )
                  .Set(false)
              );
  
              editor.TryCatch(false);
              editor.Debug(true);
              editor.Process(formData);                                            
  

• JS:

      var editor = new $.fn.dataTable.Editor({
  
          destroy: true,
          ajax: {
              url: '/Packages/CRUDPackages/',
              type: 'POST',
              async: true,
              cache: false
          },
          table: '#tblDataTable',
          fields: [
              {
                  label: '',
                  name: 'GlobalSubsPacks.id'
              }, {
                  label: 'CustomerType',
                  name: 'GlobalPacksVoiceParam.CustomerType',
                  type: 'select',
                  options: [
                      ...
                      @Html.Raw(strTp)
                  ],
                  def: 'default'
              }
          ]
      });
  
      var dataTable = $('#tblDataTable').DataTable( {
  
          dom: 'Bfrtip',
          ajax: {
              url: '/Packages/CRUDPackages/',
              type: 'GET',
              dataType: 'json',
              contentType: 'application/json; charset=utf-8',
              async: true,
              cache: false
          },
          columns: [
              { data: 'GlobalSubsPacks.id' ,
                  className: 'text-left'
              },
              { data: 'GlobalPacksVoiceParam.CustomerType' ,
                  className: 'text-left'
              }
          ],
          select: true,
          buttons: [
              { extend: 'create', editor: editor },
              { extend: 'edit', editor: editor },
              { extend: 'remove', editor: editor }
          ]
      });
  

• Model:

  public class SubsPacksViewModel
  {
  public class GlobalSubsPacks
  {
  public long ID { get; set; }
  public string Name { get; set; }
  }

  public class GlobalSubsPacksRelations
  {
      public long ID { get; set; }
      public long SubPackID { get; set; } //stores GlobalSubsPacks.ID
      public long ParamID { get; set; }   //stores GlobalPacksVoiceParam.ID
      public int Exported { get; set; }
  }
  
  public class GlobalPacksVoiceParam
  {
      public long ID { get; set; }
      public long SubPackID { get; set; }    //stores GlobalSubsPacks.ID
      public string CustomerType { get; set; }
  }
  

  }

dynasoft

There was one missing line in the server code:

editor.LeftJoin("GlobalSubsPacksRelations", "GlobalSubsPacks.id", "=", "GlobalSubsPacksRelations.SubPackID");

allan

Do you mean that with that line it is now fixed, or that it is still broken, but that line was meant to be included in the code above?

If still broken, can you use the debugger (https://debug.datatables.net) on your page to give me a trace please - click the Upload button and then let me know what the debug code is.

Thanks,
Allan

dynasoft

Hi Allan,
The line doesn't fix it, it was just missing from the initial code.
If I use this server code in lieu of the MJoins I get as many records of the sales bundles as there are lines in the linked table GlobalPacksVoiceParam. I just need the unique records from the main table GlobalSubsPacks. What is the correct way to show this? Is MJoins the way to go?
Will load the code in debugger.

dynasoft

"If I use this server code in lieu of the MJoins..."

This server code:

                editor.Field(new Field("GlobalPacksVoiceParam.CustomerType")
                    .SetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? null : val)
                );
                editor.Field(new Field("GlobalPacksVoiceParam.NumberOfMinutesOrCalls")
                    .GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? 0 : val)
                    .SetFormatter((val, data) => CommonUtilities.IsNumeric(val) == false ? 0 : val)
                    .Validator(Validation.Numeric())
                );
                editor.Field(new Field("GlobalPacksVoiceParam.HaveMinutesOrCalls")
                    .GetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? null : val)
                    .SetFormatter((val, data) => CommonUtilities.IsNullOrEmpty(val) == true ? null : val)
                );

dynasoft

Debug code is owugid. Thanks

dynasoft

Hi, would you have news on this error? Thanks.

allan

Sorry - I lost track of this.

Unfortunately the debug output doesn't show the SQL being executed. Possibly due to the .TryCatch(false). Could you remove that and run the debugger again please?

just need the unique records from the main table GlobalSubsPacks. What is the correct way to show this?

Using GlobalSubsPacks as the host table (i.e. the one in the new Editor(...) line) is the correct way to do this.

Is MJoins the way to go?

MJoin would be used to show multiple child rows / data points, for each GlobalSubsPacks entry. Is that what you need? A bit like the Permissions column in this example.

Allan

dynasoft

Thanks. I think MJoin is probably not what I need. Will post a new code shortly.