How do I filter only related records in MJoin?

How do I filter only related records in MJoin?

nicoledramirez@hotmail.comnicoledramirez@hotmail.com Posts: 60Questions: 14Answers: 2

I have the following code in my controller. When run, it pulls every record in the comment table instead of only those in the comment_access table with the correct cdi_master_id.

.MJoin(new MJoin("cdi_comment") .Link("cdi_master.id", "comment_access.cdi_master_id") .Link("cdi_comment.id", "comment_access.cdi_comment_id") .Model<cdi_clinician_commentModel>() .Order("cdi_comment.comment") .Field(new Field("id") .Options("cdi_comment", "id", "comment") ) )

Answers

  • nicoledramirez@hotmail.comnicoledramirez@hotmail.com Posts: 60Questions: 14Answers: 2
    edited May 2017
    .MJoin(
      new MJoin("cdi_comment") 
        .Link("cdi_master.id", "comment_access.cdi_master_id") 
        .Link("cdi_comment.id", "comment_access.cdi_comment_id") 
        .Model<cdi_clinician_commentModel>() .Order("cdi_comment.comment") 
        .Field(new Field("id") 
          .Options("cdi_comment", "id", "comment") 
        ) 
    )
    

    Edited by Allan for syntax highlighting

  • allanallan Posts: 61,444Questions: 1Answers: 10,053 Site admin

    Hi,

    Use the Options class to get the options, which has the option of providing a WHERE condition so you can limit the results.

    Allan

  • nicoledramirez@hotmail.comnicoledramirez@hotmail.com Posts: 60Questions: 14Answers: 2

    Hi Allan,

    I tried that but I am not sure how to reference it correctly.
    When I try something like:
    .Field(new Field("id")
    .Options("cdi_comment", "id", "comment", q => q.Where("cdi_comment.cdi_master_id", "cdi_master.id", "="))

    I get an error: "Conversion failed when converting the nvarchar value 'cdi_master.id' to data type int." But it is already set as int datatype in the database.

  • allanallan Posts: 61,444Questions: 1Answers: 10,053 Site admin

    You need to use the Options class as shown in the link, rather than just the Field.Options() method:

    new Field("id")
        .Options(new Options()
            .Table("cdi_comment")
            .Value("id")
            .Label("...")
            .Where("...")
        );
    

    Allan

  • nicoledramirez@hotmail.comnicoledramirez@hotmail.com Posts: 60Questions: 14Answers: 2

    I still get the same error. :'(

  • allanallan Posts: 61,444Questions: 1Answers: 10,053 Site admin

    Can you show me the code that you are using now please?

    Thanks,
    Allan

  • nicoledramirez@hotmail.comnicoledramirez@hotmail.com Posts: 60Questions: 14Answers: 2
    edited May 2017
    .MJoin(new MJoin("cdi_comment")
                            .Link("cdi_master.id", "comment_access.cdi_master_id")
                            .Link("cdi_comment.id", "comment_access.cdi_comment_id")
                            .Model<cdi_clinician_commentModel>()
                           // .Order("cdi_comment.comment")
                            .Field(new Field("id")
                                .Options("cdi_comment", "id", "comment", q => q.Where("cdi_comment.cdi_master_id", "cdi_master.id", "="))
                            )
                        )
    

    Thing is, if I do something like this:

    .MJoin(new MJoin("cdi_comment")
                            .Link("cdi_master.id", "comment_access.cdi_master_id")
                            .Link("cdi_comment.id", "comment_access.cdi_comment_id")
                            .Model<cdi_clinician_commentModel>()
                           // .Order("cdi_comment.comment")
                            .Field(new Field("id")
                                .Options("cdi_comment", "id", "comment", q => q.Where("cdi_comment.cdi_master_id", "61176", "="))
                            )
                      )
    

    It works. And both the cdi_comment.cdi_master and the cdi_master.id field are already integer fields in the db and model so I am not sure why it wants to convert.

  • allanallan Posts: 61,444Questions: 1Answers: 10,053 Site admin

    Ah, you need to add false to your Where method:

    q.Where("cdi_comment.cdi_master_id", "cdi_master.id", "=", false)
    

    Otherwise it tries to bind cdi_master.id as the value to be searched for, not a reference. That's to try and prevent SQL injection attacks.

    Allan

  • nicoledramirez@hotmail.comnicoledramirez@hotmail.com Posts: 60Questions: 14Answers: 2
    edited May 2017

    Close but not quite there yet. Now I get an error that cdi_master.id cannot be bound.

    I have it in the Model:

    public class cdi_clinician_comment
        {
            public string id { get; set; }
            
        }
        public class cdi_master
        {
            public int id { get; set; }
        }
    }
    
  • allanallan Posts: 61,444Questions: 1Answers: 10,053 Site admin

    Could you show me the exact error message please, and also the Options code that is now being used. Just so I can double check things on this side.

    Allan

  • nicoledramirez@hotmail.comnicoledramirez@hotmail.com Posts: 60Questions: 14Answers: 2
                  ```   .MJoin(new MJoin("cdi_comment")
                        .Link("cdi_master.id", "comment_access.cdi_master_id")
                        .Link("cdi_comment.id", "comment_access.cdi_comment_id")
                        .Model<CommentAccessModel>()
                        .Order("cdi_comment.comment")
                        .Field(new Field("id")
                            .Options("cdi_comment", "id", "comment", q => q.Where("cdi_comment.cdi_master_id", "cdi_master.id", "=", false))
                        )
                    ) ```
    

    The error message is cdi_master.id cannot be bound.

  • nicoledramirez@hotmail.comnicoledramirez@hotmail.com Posts: 60Questions: 14Answers: 2
    edited May 2017
    {
        "Message": "An error has occurred.",
        "ExceptionMessage": "The multi-part identifier \"cdi_master.id\" could not be bound.",
        "ExceptionType": "System.Data.SqlClient.SqlException",
        "StackTrace": "   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)\r\n   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)\r\n   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)\r\n   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()\r\n   at System.Data.SqlClient.SqlDataReader.get_MetaData()\r\n   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds, Boolean describeParameterEncryptionRequest)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)\r\n   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)\r\n   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)\r\n   at DataTables.DatabaseUtil.Sqlserver.Query._Exec()\r\n   at DataTables.Options.Exec(Database db)\r\n   at DataTables.MJoin.Data(Editor editor, DtResponse response)\r\n   at DataTables.Editor._Get(Object id, DtRequest http)\r\n   at DataTables.Editor._Process(DtRequest data)\r\n   at DataTables.Editor.Process(DtRequest data)\r\n   at DataTables.Editor.Process(NameValueCollection data)\r\n   at WebApiExamples.Controllers.cdi_clinician_master2Controller.cdi_clinician_master2()\r\n   at lambda_method(Closure , Object , Object[] )\r\n   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)\r\n   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Controllers.ApiControllerActionInvoker.<InvokeActionAsyncCore>d__0.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Controllers.ActionFilterResult.<ExecuteAsync>d__2.MoveNext()\r\n--- End of stack trace from previous location where exception was thrown ---\r\n   at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)\r\n   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)\r\n   at System.Web.Http.Dispatcher.HttpControllerDispatcher.<SendAsync>d__1.MoveNext()"
    }
    
  • allanallan Posts: 61,444Questions: 1Answers: 10,053 Site admin

    Thanks - I see the issue now.

    The problem is that the Options class is constructing this SQL statement:

    SELECT id, comment
    FROM cdi_comment
    WHERE cdi_comment.cdi_master_id = cdi_master.id
    

    That won't work since the cdi_master table is not referenced in that statement, thus throwing the error you are seeing.

    I'm not entirely certain what the goal is here? Are you trying to have the select list populated with different values for each row as it is being edited?

    Thanks,
    Allan

  • nicoledramirez@hotmail.comnicoledramirez@hotmail.com Posts: 60Questions: 14Answers: 2

    Basically I am trying to do this: https://editor.datatables.net/examples/advanced/joinArray.html but I am not sure why it isn't working.

    The parent table is pulling from multiple tables and then passing back cdi_master_id to the form to pull all comment records into the form and allow the user to relate or unrelate them to the parent record.

  • allanallan Posts: 61,444Questions: 1Answers: 10,053 Site admin

    The example that you linked to doesn't have any kind of query condition of the options that can be selected for the array field. Its simply a list of the options that are available.

    If you drop the where clause, does it do what you are looking for?

    Thanks,
    Allan

This discussion has been closed.