Individual column searching with MJoins (re-opened)

guidols

Hello everyone,

Some years ago I posted a question regarding individual column searching with MJoin (see here). Since then I’ve avoided using MJoin due to its challenges, but I’m now forced to implement proper filtering (and sorting) using MJoin because copying data into string fields for filtering is becoming nearly unmaintainable.

My Setup

I have a many-to-many relationship structured as follows:

Products ⟷ ProductStorageLocationProducts ⟷ ProductStorageLocations

My MJoin is configured like this:

.MJoin(new MJoin("ProductStorageLocations")
    .Link("Products.Id", "ProductStorageLocationProducts.Product_Id")
    .Link("ProductStorageLocations.Id", "ProductStorageLocationProducts.ProductStorageLocation_Id")
    .Model<EditorNameDto>()
    .Field(new Field("Id")
        .Options("ProductStorageLocations", "Id", "Name")
    )
)

I want to filter the results based on a column name and a filter value passed from the browser. For instance, my idea is to have an action similar to:

[ValidateInput(false)]
public JsonResult Product(string columnName = null, string filterValue = null)
{
    var request = System.Web.HttpContext.Current.Request;

    using (var db = new Database("sqlserver", System.Configuration.ConfigurationManager.ConnectionStrings["HumabsDBContext"].ConnectionString))
    {
        var editor = new Editor(db, "Products", "Products.Id")
            .Model<ProductEditorDto>("Products");

        if (!string.IsNullOrEmpty(columnName) && !string.IsNullOrEmpty(filterValue))
        {
            // Example: filter the N-to-N related entities
            var filterEntities = _db.ProductStorageLocations
                                   .Where(p => p.Name.Contains(filterValue))
                                   .Select(p => p.Id)
                                   .ToList();

            // How do I apply this filter on Products.ProductStorageLocationProducts?
        }

        // ... further processing ...
    }
}

My questions are:

Server-Side Filtering:
How can I apply a filter on the Products.ProductStorageLocationProducts join table using the list of filtered ProductStorageLocation IDs?
I need to restrict the query so that only Products linked to the matching ProductStorageLocations are returned.

Client-Side Parameters:
On the client side, how can I dynamically call the Product action with the two parameters (columnName and filterValue)?
I’m aware that I can configure the ajax request like this:

ajax: {
    url: "/Editor/@entity",
    type: "POST",
    data: {
        // parameters here
    }
}

But I’m unsure how to make these parameters dynamic—so that they are added or modified based on user input.

Any guidance on how to achieve filtering on the N-to-N relationship using MJoin and how to properly pass dynamic parameters from the browser would be greatly appreciated.

Thank you in advance!

allan

When server-side processing is enabled, there is no support currently available for an Mjoined column - sorry. The only way to do it would be to construct an SQL query and use that as a VIEW to feed the table.

Allan

kthorngren

But I’m unsure how to make these parameters dynamic—so that they are added or modified based on user input.

You can use column().search() like this example with server side processing. Or use ajax.data as a function, like this example, if you want to send send search terms separate from the column search terms as described in the SSP protocol.

Kevin