Editor - Filter and Sort SQL data on server before sending to editor

dpanscik

I am wondering if there is an easy way to use the stock editor controller and sort and filter SQL data before sending to the form.

I am hoping the approach is something similar to my attempt below where i attempted to use the .where with the lambada expression. .Where(x => x.dayOC > DateTime.now)

            using (var db = new Database(settings.DbType, settings.DbConnection))
            {

                var response = new Editor(db, "Flex_OnCall_3Model", "TableID")
                    .Model<Flex_OnCall_3Model>()
                    .Where(x => x.dayOC > DateTime.now)
                    .Field(new Field("dayOC")
                            .Validator(Validation.DateFormat(Format.DATE_ISO_2822))
                            .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                            .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822))
                    )
                    .Field(new Field("modifiedDate")
                            .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                            .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822))
                    )
                    .Debug(true)
                    .Process(request)
                    .Data();
    
                return Json(response);
            }

allan

You can use Where(), but it needs to be an SQL expression - see the docs here.

There isn't an option to apply a sort though, since that would be done client-side anyway (unless you are using server-side processing, in which case it is applied automatically).

Allan

dpanscik

I am getting red squigglies when I try to use editor.where statement. The manual has this an an example but I may be not understanding where and how to use the implementation.

Here is a screen shot.

Here is the actual code

            using (var db = new Database(settings.DbType, settings.DbConnection))
            {

                var response = new Editor(db, "Flex_OnCall_3Model", "TableID")
                    .Model<Flex_OnCall_3Model>()
                    .editor.Where("user_id", 12)
                    .Field(new Field("dayOC")
                            .Validator(Validation.DateFormat(Format.DATE_ISO_2822))
                            .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                            .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822))
                    )
                    .Field(new Field("modifiedDate")
                            .GetFormatter(Format.DateSqlToFormat(Format.DATE_ISO_2822))
                            .SetFormatter(Format.DateFormatToSql(Format.DATE_ISO_2822))
                    )
                    .Debug(true)
                    .Process(request)
                    .Data();
    
                return Json(response);
            }

allan

Remove the .editor par. It should just be:

.Model(<Flex_OnCall_3Model>())
.Where(...)
.Field(...)

The editor variable is for if you weren't using the fluent API - e.g.:

var editor = new Editor( ... );
editor.Modal(...);
editor.Where(...);

I used that form in the manual just for brevity.

Allan

dpanscik

That was the magic! I'm now successfully filtering the SQL query.