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

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

dpanscikdpanscik Posts: 119Questions: 32Answers: 0
edited March 2023 in Free community support

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

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    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

  • dpanscikdpanscik Posts: 119Questions: 32Answers: 0

    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);
                }
    
  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin
    Answer ✓

    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

  • dpanscikdpanscik Posts: 119Questions: 32Answers: 0

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

Sign In or Register to comment.