Replies

• allan Posts: 61,972Questions: 1Answers: 10,160 Site admin

  July 2023

  Hi,

  Can you show me your full PHP for this script, and also the client-side Javascript for DataTables and Editor please? Even better would be if you can give me a link to the page so I can see what is going on.

  Thanks,
  Allan
• fpinho Posts: 4Questions: 0Answers: 0

  July 2023 edited July 2023

  I forgot to say that is C#.
  I want to made this on server-side if it's possible.

  var response = new Editor(db, "OrganicUnit", "OrganicUnitID")
                  .Model<OrganicUnitDTO>("OrganicUnit")
                  .Model<JoinModelUser>("User")
                  .Field(new Field("OrganicUnit.UserID")
                      .Options(new Options()
                      .Table("User")
                      .Value("UserID")
                      .Label("FirstName")
                      .Where((q) => { q.Where("ProfileID", (long)ProfileType.COORDINATOR); }))
                      .Validator((value, data, host) =>
                      {
                          if (data.ContainsKey("OrganicUnit") && data["OrganicUnit"] is Dictionary<string, object> ActivityData)
                          {
                              if (ActivityData.ContainsKey("UserID"))
                              {
                                  if (value is string strValue && string.IsNullOrEmpty(strValue))
                                  {
                                      ActivityData["UserID"] = null;
                                  }
                              }
                          }
                          return null;
                      }))
                  .Field(new Field("OrganicUnit.DirectorUserID")
                      .Options(new Options()
                      .Table("User")
                      .Value("UserID")
                      .Label("LastName")
                      .Where((q) => { q.Where("ProfileID", (long)ProfileType.DIRECTOR); }))
                      .Validator((value, data, host) =>
                      {
                          if (data.ContainsKey("OrganicUnit") && data["OrganicUnit"] is Dictionary<string, object> ActivityData)
                          {
                              if (ActivityData.ContainsKey("DirectorUserID"))
                              {
                                  if (value is string strValue && string.IsNullOrEmpty(strValue))
                                  {
                                      ActivityData["DirectorUserID"] = null;
                                  }
                              }
                          }
                          return null;
                      }))
                  .LeftJoin("YearData", "YearData.YearDataID", "=", "OrganicUnit.YearDataID")
                  .Field(new Field("OrganicUnit.OrganicUnitID", "Options").Xss(false))
                  .LeftJoin("User", "User.UserID", "=", "OrganicUnit.UserID")
                  .Where("OrganicUnit.YearDataID", YearDataID, "=")
                  .Process(request)
                  .Data();
  
• allan Posts: 61,972Questions: 1Answers: 10,160 Site admin

  July 2023 edited July 2023

  Sorry - I should have seen that it was C#!

  If I'm understanding this correctly, you've got two foreign key references to the User table from OrganicUnit (UserID and DirectorUserID).

  That's absolutely fine, but it means you need two left joins for it as well! One to get the data for each.

  .LeftJoin("User DirectorUser", "DirectorUser.UserID", "=", "OrganicUnit.DirectorUserID")
  

  (Note that I've aliased the User table to DirectorUser to allow a second reference to the User table).

  You'll also need to add a model for the director user as well:

  .Model<JoinModelUser>("DirectorUser")
  

  Should do it - assuming you want the same information from the table for the director.

  Then on the client-side, refer to DirectorUser.LastName (etc) to display information about the linked director user.

  Regards,
  Allan
• fpinho Posts: 4Questions: 0Answers: 0

  July 2023

  Thanks, works well.
  Regards.
• allan Posts: 61,972Questions: 1Answers: 10,160 Site admin

  July 2023

  Awesome - nice one!

  Allan