Editor Samples - joinArray.html has bugs with ServerSide: true.

washuit-iamm

Mostly, works. Edit joinArray.html and set to serverSide: true. Then click the "permission" column to trigger a sort.

Why does sorting on Permissions break?

var response = new Editor(db, "users", "users.id")
    .Debug(true)
    .Model<JoinModelUsers>("users")
    .Model<JoinModelSites>("sites")
    .Field(new Field("users.site")
        .Options(new Options()
            .Table("sites")
            .Value("id")
            .Label("name")
        )
    )
    .LeftJoin("sites", "sites.id", "=", "users.site")
    .MJoin(new MJoin("permission")
        .Link("users.id", "user_permission.user_id")
        .Link("permission.id", "user_permission.permission_id")
        .Model<JoinAccessModel>()
        .Order("permission.name")
        .Field(new Field("id")
            .Options(new Options()
                .Table("permission")
                .Value("id")
                .Label("name")
            )
        )
    )
    .Process(Request)
    .Data();

namespace EditorNetCoreDemo.Models
{
    public class JoinModelUsers
    {
        public string first_name { get; set; }

        public string last_name { get; set; }

        public string phone { get; set; }

        public int site { get; set; }

        public int manager { get; set; }
    }

    public class JoinModelSites
    {
        public string name { get; set; }
    }

    public class JoinAccessModel
    {
        public int id { get; set; }

        public string name { get; set; }
    }
}

JS

$('#example').DataTable({
    ajax: {
        url: '/api/joinArray',
        type: 'POST'
    },
    processing: true,
    serverSide: true,
    columns: [
        { data: 'users.first_name' },
        { data: 'users.last_name' },
        { data: 'sites.name' },
        { data: 'permission', render: '[, ].name' }
        ]
//  ...

allan

That is actually expected. The server-side processing libraries don't support order or search on an Mjoin column at this time.

The issue is that the main search and order are performed in a single query, by the Mjoin'ed data is fetched in a second query.

Until I find a way to combine the queries (which I'm not sure I ever will across all of the supported databases), it will remain unsupported. I've done it for postgres before (not in these libraries), so it is possible, but it is a lot of work and very schema dependant.

Allan

washuit-iamm

@allan

I am interested in hearing how you did that in Postgres, or any alternative solutions I could use. We definitely need to be able to search on MJoins.

Taking the Editor.NET samples for example, searching all users by who has "Accounts" permissions. Today you simply cannot sort or filter on those in any way serverSide.

allan

It wasn't with the Editor libraries - they simply wouldn't work without a major rearchitecture.

What I did was to use CTEs with a common structure for each table, JSON aggregation and grouping. I'll see if I can dig some code out next week, but I doubt it will be useful for anyone - it was for quite a specific use case. It did allow N-level nesting though, I was quite proud of it (used for CloudTables).

You are right - with server-side processing, at this time there is no option to do search / sort for Mjoins. Your best best would be to construct a VIEW that does the grouping and then read from that.

Allan