How do I filter only related records in MJoin?

nicoledramirez@hotmail.com

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") ) )

nicoledramirez@hotmail.com

.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

allan

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.com

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.

allan

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.com

I still get the same error.

allan

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

Thanks,
Allan

nicoledramirez@hotmail.com

.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.

allan

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.com

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; }
    }
}

allan

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.com

              ```   .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.com

{
    "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()"
}

allan

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.com

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.

allan

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