Solving a big Delay when use a lookup for more 70,000 rows !

[waleed51]

I have a (Visits) grid which records visits transactions to my company ,
and it have a relation with (Visitor) -Visit have one or more visitors- ,

I use a Visitors Row as Lookup in Visit dialog, to can select visitor when I add a new Visitor transaction, ( like in Picture).

the problem is Visitor table have more 70,000 rows, when I use it in lookup it make a big delay for searching on it to retrieve data.

Is there is any way to not load all lookup, only load based on search in lookup, or any alternative option compatible with my business?

[kilroyFR]

There is an option to load lookup asynchronously

[waleed51]

Please more explanation @kilroyFR

[VictorTomaili]

I guess you want to something like in this demo filters. Some of our editors included in our premium. But you are free to write your own.
https://serenity.is/demo/AdvancedSamples/DataExplorer

[waleed51]

sorry , but i don't know how to do my own??
please more help ! it's so critical and important to me!

[VictorTomaili]

ServiceLookupEditor is good option what i miss.

[waleed51]

how to use it ???

[minhhungit]

@waleed51 Use [ServiceLookupEditor], like this

[ServiceLookupEditor(service: "Northwind/Customer/List", idField: "CustomerID", textField: "CompanyName", PageSize = 5)]
public String CustomerID { get; set; }

[waleed51]

first thank you for your help , but it isn't work
i user it in Columns.cs - Form.cs - Row.cs and not work at any one , and give error (ServiceLookupEditor could not be find)

please, i need how to use it ?
@minhhungit

[minhhungit]

which serenity version you are using, if you use old version, you might need to implement the editor by yourself,check code here

https://github.com/serenity-is/Serenity/blob/b8fd791681e91014e5ddc7ee24f3efbeb91f0ca0/src/Serenity.Scripts/CoreLib/UI/Editors/ServiceLookupEditor.ts

[waleed51]

i use an old version and try to implement the editor like code in link, but it give a lot of errors .
please help !!
thank you for ervery thing, and i'm so sorry @minhhungit

[minhhungit]

@waleed51 use this code, I cloned code from the link https://github.com/serenity-is/Serenity/blob/b8fd791681e91014e5ddc7ee24f3efbeb91f0ca0/src/Serenity.Scripts/CoreLib/UI/Editors/ServiceLookupEditor.ts

namespace YOUR_APP.Web.Common.Editors {

    export interface ServiceLookupEditorOptions extends Serenity.Select2EditorOptions {
        service?: string;
        idField: string;
        textField: string;
        pageSize?: number;
        minimumResultsForSearch?: any;
        sort: string[];
        columnSelection?: Serenity.ColumnSelection;
        includeColumns?: string[];
        excludeColumns?: string[];
        includeDeleted?: boolean;
        containsField?: string;
        equalityFilter?: any;
        criteria?: any[];
    }

    @Serenity.Decorators.registerEditor("CustomServiceLookupEditorBase")
    export class CustomServiceLookupEditorBase<TOptions extends ServiceLookupEditorOptions, TItem> extends Serenity.Select2Editor<TOptions, TItem> {

        constructor(input: JQuery, opt?: TOptions) {
            super(input, opt);
        }

        protected getDialogTypeKey() {
            var dialogTypeKey = super.getDialogTypeKey();
            if (dialogTypeKey)
                return dialogTypeKey;

            var service = this.getService();
            if (Q.startsWith(service, "~/Services/"))
                service = service.substr("~/Services/".length);

            if (service.split('/').length == 3)
                service = service.substr(0, service.lastIndexOf('/'));

            return service.replace("/", ".");
        }

        protected getService(): string {
            return this.options.service;
        }

        protected getServiceUrl() {
            var url = this.getService();
            if (url == null)
                throw new Error("ServiceLookupEditor requires 'service' option to be configured!");

            if (!Q.startsWith(url, "~") && !Q.startsWith(url, "/") && url.indexOf('://') < 0)
                url = "~/Services/" + url;

            if (Q.startsWith(url, "~"))
                url = Q.resolveUrl(url);

            return url;
        }

        protected getIncludeColumns() {
            var include = this.options.includeColumns || [];
            var idField = this.getIdField();

            if (idField && include.indexOf(idField) < 0)
                include.push(idField);

            var textField = this.getTextField();
            if (textField && include.indexOf(textField) < 0)
                include.push(textField);

            return include;
        }

        protected getSort() {
            return this.options.sort || (this.getTextField() ? [this.getTextField()] : null);
        }

        protected getCascadeCriteria(): any[] {

            var val = this.get_cascadeValue();

            if (val == null || val === '') {

                if (!Q.isEmptyOrNull(this.get_cascadeField())) {
                    return ['1', '=', '0'];
                }

                return null;
            }

            var fld = this.get_cascadeField();

            return [[fld], '=', val];
        }

        protected getFilterCriteria(): any[] {
            var val = this.get_filterValue();

            if (val == null || val === '') {
                return null;
            }

            var fld = this.get_filterField();
            return [[fld], '=', val];
        }

        protected getIdListCriteria(idList: any[]): any[] {
            if (idList == null)
                return null;

            if (idList.length == 0)
                return ['0', '=', '1'];

            var idField = this.getIdField();
            if (idField == null)
                throw new Error("ServiceLookupEditor requires 'idField' option to be configured!");

            return [[idField], 'in', [idList]];
        }

        protected getCriteria(query: Serenity.Select2SearchQuery): any[] {
            return Serenity.Criteria.and(
                Serenity.Criteria.and(this.getIdListCriteria(query.idList), this.options.criteria),
                Serenity.Criteria.and(this.getCascadeCriteria(), this.getFilterCriteria()));
        }

        protected getListRequest(query: Serenity.Select2SearchQuery): Serenity.ListRequest {

            var request: Serenity.ListRequest = {};

            if (query.searchTerm)
                request.ContainsText = query.searchTerm;

            request.Sort = this.getSort();
            request.ColumnSelection = this.options.columnSelection || Serenity.ColumnSelection.KeyOnly;
            request.IncludeColumns = this.getIncludeColumns();
            request.ExcludeColumns = this.options.excludeColumns;
            request.ContainsField = this.options.containsField;
            request.EqualityFilter = this.options.equalityFilter;
            request.Criteria = this.getCriteria(query);
            request.Skip = query.skip || 0;
            request.Take = query.take ? (query.checkMore ? query.take + 1 : query.take) : 0;
            request.IncludeDeleted = this.options.includeDeleted;
            request.ExcludeTotalCount = true;

            return request;
        }

        protected getServiceCallOptions(query: Serenity.Select2SearchQuery, results: (result: Serenity.Select2SearchResult<TItem>) => void): Serenity.ServiceOptions<Serenity.ListResponse<TItem>> {
            return {
                blockUI: false,
                url: this.getServiceUrl(),
                request: this.getListRequest(query),
                onSuccess: response => {
                    var items = response.Entities || [];

                    if (items && query.take && query.checkMore && response.Entities.length > items.length)
                        items = items.slice(0, query.take);

                    results({
                        items: items.slice(0, query.take),
                        more: query.checkMore && query.take && items.length > query.take
                    });
                }
            };
        }

        protected hasAsyncSource() {
            return true;
        }

        protected asyncSearch(query: Serenity.Select2SearchQuery, results: (result: Serenity.Select2SearchResult<TItem>) => void): Serenity.Select2SearchPromise {
            var opt = this.getServiceCallOptions(query, results);
            return Q.serviceCall(opt);
        }
    }

    @Serenity.Decorators.registerEditor('TestServiceLookupEditor')
    export class TestServiceLookupEditor extends CustomServiceLookupEditorBase<ServiceLookupEditorOptions, any> {
        constructor(hidden: JQuery, opt?: ServiceLookupEditorOptions) {
            super(hidden, opt);
        }
    }
}

Build, transform t4 template and using it in your Row.cs like this:

[Common.Editors.TestServiceLookupEditor(Service = "Default/Supplier/List", IdField = "Id", TextField = "CompanyName", PageSize = 5)]
public Guid? SupplierId
{
    get { return Fields.SupplierId[this]; }
    set { Fields.SupplierId[this] = value; }
}

Good luck

[waleed51]

i think there is an error in

but it's run and the lookup is empty
@minhhungit

[minhhungit]

getIdField and getTextField is from Select2Editor, are you sure your class is inherited from Serenity.Select2Editor<TOptions, TItem>

[waleed51]

yes sure @minhhungit

[minhhungit]

It is hard for me to guess the problem without checking/debugging code.

Let's try to check if Serenity.Select2Editor in your project has those methods

[waleed51]

ok i will check and tell you @minhhungit