Answers

• rf1234 Posts: 3,190Questions: 92Answers: 438

  December 2025

  Hmmm. Editor doesn't "make" left joins itself. You have to code them. Looking at your code you should be able to see what left joins you got.

  https://editor.datatables.net/manual/net/joins#Left-Join
• allan Posts: 65,469Questions: 1Answers: 10,874 Site admin

  December 2025

  Just to add to that:

  But I can't see any private field like name of joined table.

  The first parameter of the .LeftJoin() method tells Editor what the joined table name should be.

  But you mention a private property - are you looking to try and access that information externally? Why?

  Allan
• klymov.inekon Posts: 39Questions: 11Answers: 0

  January 7

  It's just that I have a system where I don't know exactly what left joins there will be. And when adding a left join, I pass a string with the table name value. To check, I then need to keep track of the tables that have already been joined, for example, using an array to check if the table has already been added. If it has already been added, then I need to change the join conditions.
  This can be described as working with SQL tables. The user specifies that I have a table with a certain name and certain columns. They want the values for this table to be references to values from another table. For example, in the original table there is a column called customerId, in another table there is a column called customerId, customerName. And I want to see customerName as the result. Such “replacements” can be for several tables and several columns in one table. In order not to join the table twice with different conditions and to avoid conflicts with the join names, I would like to combine them into one join and write the multiple conditions.
  So here's the question: is it possible in DataTables to change these parameters during program execution using the API, or do I need to take all join logic manually first and set a left join function once?
• allan Posts: 65,469Questions: 1Answers: 10,874 Site admin

  January 7

  Do you have the raw SQL for what you want? If so, can you show me that?

  For example, in the original table there is a column called customerId, in another table there is a column called customerId, customerName. And I want to see customerName as the result.

  That sounds like a regular left join. In this example I've got it reading just name from the joined table, but you could readily have it read multiple fields if you want from the joined table.

  Allan
• klymov.inekon Posts: 39Questions: 11Answers: 0

  January 8

  In this part of the code, I create a field depending on the Mapping object, which describes the relationship (with another table or simply an enum of values created by the administrator).

  Field mainField = new Field($"{tables.Table.RemoveSquareBrackets()}.{col.Name}", $"{tables.GetOnlyTableName()}.{col.Name}")  //RemoveSquareBrackets and GetOnlyTableName extensions for strings
      .GetFormatter(EditorFieldExtensions.GetFormatters.FormatStringSpecialSymbols) // we have czech language and it sometimes cause symbols errors
      .SetFormatter(EditorFieldExtensions.SetFormatters.FormatStringSpecialSymbols);
  if (col.Mapping.Type == Domain.Configurations.Type.Table) // configuration if mapping reference onto another table
  {
      _mappingAliasAppendIndex++; // need to create it, because if I have two columns referenced on same table cause join error. this is a problem
      var anotherTable = await _db.tables.Include(s => s.DataSource).FirstOrDefaultAsync(s => s.Id == col.Mapping.tables); //ef core, where we have stored configs.
      if (anotherTable is null)
      {
          Error = $"Related tables with Id {col.Mapping.tables} not found"; //external check if Error is empty 
          return;
      }
    
      string anotherTableAlias = $"{_mappingAliasAppend}{_mappingAliasAppendIndex}";
      DataTables.Options opts = null;
      if (string.IsNullOrEmpty(col.Mapping.CaptionColumn)) // if mapping need to show same column but limit the selection to only values already created in another table via options
      {
          //need to create extra field because options cant change value to label, read it in getFormatter
          editor
              .Field(new Field($"{anotherTableAlias}.{col.Mapping.KeyColumn}", $"Mapped.{anotherTable.GetOnlyTableName()}.{col.Name}").Set(false))
              .LeftJoin(
                  $"""
                      (Select {col.Mapping.KeyColumn}  from [{anotherTable.DataSource.GetDatabaseName().RemoveSquareBrackets()}].{anotherTables.Table.RemoveSquareBrackets()} )
                      {anotherTableAlias}
                  """,
                  $@"{tables.Table.RemoveSquareBrackets()}.{col.Name} = {anotherTableAlias}.{col.Mapping.KeyColumn}"
              );
          opts = new DataTables.Options()
                      .Table($"{anotherTable.DataSource.GetDatabaseName().RemoveSquareBrackets()}.{anotherTable.Table.RemoveSquareBrackets()}")
                      .Value(col.Mapping.KeyColumn)
                      .Label(col.Mapping.KeyColumn)
                      .Render(row => System.Web.HttpUtility.HtmlDecode(row[col.Mapping.KeyColumn].ToString()));
      }
      else // if mapping need to show another column (customerName column, as I've describe before)
      {
          //need to create extra field because options cant change value to label, read it in getFormatter
          editor
              .Field(new Field($"{anotherTableAlias}.{col.Mapping.CaptionColumn}", $"Mapped.{anotherTable.GetOnlyTableName()}.{col.Name}").Set(false))
              .LeftJoin(
                  $"""
                      (Select {col.Mapping.KeyColumn} , {col.Mapping.CaptionColumn} from [{anotherTable.DataSource.GetDatabaseName().RemoveSquareBrackets()}].{anotherTable.Table.RemoveSquareBrackets()} )
                      {anotherTableAlias}
                  """,
                  $@"{tables.Table.RemoveSquareBrackets()}.{col.Name} = {anotherTableAlias}.{col.Mapping.KeyColumn}"
              );
          opts = new DataTables.Options()
                      .Table($"{anotherTable.DataSource.GetDatabaseName().RemoveSquareBrackets()}.{anotherTable.Table.RemoveSquareBrackets()}")
                      .Value(col.Mapping.KeyColumn)
                      .Label(col.Mapping.CaptionColumn)
                      .Render(row => System.Web.HttpUtility.HtmlDecode(row[col.Mapping.CaptionColumn].ToString()));
      }
  
      var colSort = anotherTable.tablesConfig.Columns.Where(s => s.OrderIndex.HasValue).OrderBy(s => s.OrderIndex).FirstOrDefault();
      if (colSort is not null)
      {
          opts.Order($"{anotherTable.DataSource.GetDatabaseName().RemoveSquareBrackets()}.{anotherTable.Table.RemoveSquareBrackets()}.{colSort?.Name}");
      } // order by second column
      mainField.Options(opts);
  }
  else if (col.Mapping.Type == Domain.Configurations.Type.Enum) //created enum if admin want user can set only defined values
  {
      var options = col.Mapping.Items.Select(i => new Dictionary<string, object>() { { "value", i.Key }, { "label", col.Mapping.KeyValue ? i.Value : i.Key } }).ToList();
      editor.Field(
          new Field($"{tables.Table.RemoveSquareBrackets()}.{col.Name}", $"Mapped.{tables.GetOnlyTableName()}.{col.Name}")
          .Set(false)
          .GetFormatter((val, row) =>
          {
              var dict = col.Mapping.Items.FirstOrDefault(s => s.Key == val.ToString());
              return dict is not null
              ? col.Mapping.KeyValue
                  ? dict.Value : dict.Key
              : null;
          })); // can't find better solution pass values as options
      mainField.Options((n, m) => options);
  }
  
  editor.Field(mainField);
  

  This is how you describe a field that references another table. As you can see, I don't know the values in advance, whether there is mapping, and what type they are. There may be several such mappings.
  The problem is that I need to create aliases for values from another table, since I don't know how many references there will be to the same table. Therefore, I would like to know if it is possible to see which tables have already been added.
  As a result, I would ideally like to see a list of already joined tables so that I don't have to create a new join, but change the condition. Or should I make my own decision - find out what the mappings will be and connect all the conditions manually in a string, and later add a left join, already knowing how many tables there will be, and add strings for the conditions to the conditions.