Primeng: Sorting is not working with Virtual scroll (Lazy loading) in PrimeNG data table. Sorting and lazy loading don't work together

[akashdeepg12]

Sorting is not working with Virtual scroll (Lazy loading) in PrimeNG data table.Actually with large data set ,primeNg table become hang, for this resolution I have implemented Virtual scroll(Lazy loading). But there is a problem that Sorting is not working in lazy load.

Also,I tried custom sorting for that but still not working.Actually my custom sort function is not working with Lazy loading.Any solution ?

Is It fixed in recent PrimeNG release ?

[Cito]

What do you mean with "is not working"?

When you're using lazy loading data, you must do the sorting via the lazy load event, e.g. by passing the sortField of the lazy load event as a query parameter that will then be used for the order by clause in an SQL database.

This worked pretty well for me. When changing the sort order, I get the sortField in the lazy load event, and the pagination starts on page 1 again. The only problem I observed is that the scroll position of the component does not jump back to the start.

[akashdeepg12]

thank you @Cito .But there is one more problem that If we have large data set(approx 120 rows) and do custom sorting ,it takes approx 3 to 4 seconds to sort .It happens because custom sorting logic iterates rows array.

Is there any way to optimize logic for custom sorting ?

/**

• Logic for Virtual Scrolling/loadDataLazy
• @param event
  */
  loadDataLazy(event: LazyLoadEvent) {

    // execute on sorting only 
    if (event.sortField !== undefined) { 
      let tableData = rowdDataArr.sort(function (a, b) {
             let  value1 = parseFloat(a[event.sortField];);
             let value2 = parseFloat(b[event.sortField];);
            if (event.sortOrder == -1) {
                return (value2 > value1) ? 1 : ((value2 < value1) ? -1 : 0);
            } else {
                return (value1 > value2) ? 1 : ((value1 < value2) ? -1 : 0);
            }
        });
    rowdDataArr = []; // empty old row data array
    rowdDataArr = tableData; // fill sorted  row data array
  }

}

[Cito]

You must be doing something strange. I have an example with over 200,000 rows which are sorted and displayed in an instant with lazy loading in PrimeNG. As I explained, I'm sorting the full set on the database only and lazy load only the portion of rows that is displayed. You still seem to sort on the client which is not possible with lazy loading since you don't have the full data set on the client.

[akashdeepg12]

Thanks @Cito .Can you share any example here ?

[akashdeepg12]

Actually We are using Rest Api service to get full data set.After getting full data set on the client,
now I need to be custom sort on client with lazy load.
Can you share any example here as you suggested above things?

[Cito]

When you fetch the full data set, you're not lazy loading but eager loading.

You need to have a service that allows sorting. Here is an example using a public REST API:

const base_url = 'https://earthquake.usgs.gov/fdsnws/event/1/';
let common_params = '?format=geojson';
common_params += '&starttime=2017-07-01&endtime=2017-07-31';
const count_url = base_url + 'count' + common_params;
const query_url = base_url + 'query' + common_params;

export interface EarthQuake {
  mag: number;
  time: number;
  place: string;
}

@Injectable()
export class EarthQuakeService {

    constructor(private http: Http) {}

    count(): Observable<number> {
        return this.http.get(count_url).map(data => data.json()).map(
            data => data['count']);
    }

    query(limit = 10, offset = 1, orderby = 'time'): Observable<EarthQuake[]> {
        let url = query_url;
        url += `&orderby=${orderby}&limit=${limit}&offset=${offset}`;
        return this.http.get(url).map(data => data.json()).map(
            data => data['features'].map(feature => feature['properties']));
    }
}

The Angular component could look like this:

@Component({
    templateUrl: './quakes.component.html'
})
export class EarthQuakesComponent implements OnInit {

    totalRecords: number;
    loading = true;
    limit = 10;
    records: EarthQuake[];

    constructor(private quakeService: EarthQuakeService) { }

    ngOnInit() {
      this.quakeService.count().subscribe(
        totalRecords => { this.totalRecords = totalRecords; }
      );
    }

    loadRecords(event: LazyLoadEvent) {
        let orderby = event.sortField;
        if (orderby) {
            if (orderby === 'mag') {
                orderby = 'magnitude';
            }
            if (event.sortOrder > 0) {
                orderby += '-asc';
            }
        } else {
            orderby = '';
        }
        this.loading = true;
        const limit = event.rows;
        const offset = event.first + 1;
        this.quakeService.query(
        limit, offset, orderby).subscribe(
          records => {
            this.records = records;
            this.loading = false;
          });
    }
}

Your template can be something like this:

<h1>Virtual scroll through {{totalRecords}} earth quakes</h1>

<p-dataTable [rows]="limit" [lazy]="true"
             [scrollable]="true"
             [virtualScroll]="true" scrollHeight="300px"
             [value]="records" [totalRecords]="totalRecords"
             [loading]="loading" loadingIcon="fa-spinner"
             (onLazyLoad)="loadRecords($event)">
  <p-column field="mag" header="Magnitude" [sortable]="true"></p-column>
  <p-column field="time" header="Time" [sortable]="true">
    <ng-template pTemplate="body" let-row="rowData">
      {{row.time | date:'medium'}}
    </ng-template>
  </p-column>
  <p-column field="place" header="Place"></p-column>
</p-dataTable>

[akashdeepg12]

thanks for helping me

[suparnbector]

Hi,

I have a similar requirement of Server side paging / sorting & filter but in addition to that, we need to allow user to update the cell value.

One way to handle that is in onEditComplete of a Cell.
Other is that, we allow the use to update the required rows/cell and then we trigger one API to the back-end to persist updated data. (In-short we can name is that "BATCH MODE UPDATE")

So, for Batch mode update one use case may be like :

Step 1 ) User FILTERS the data with Magnitude = 6.6 and data table shows 10 records.
Step 2) User do an in-line modification/update the Place column of 3rd row.
Step 3) User SORT the data by Magnitude

Present Behavior : Changes done in step 2 are getting over written as lazy loading will fetch the data from the Server.

Intended Behavior : After Step3 , modified data should be shown for that row

Given that , User should be allowed to do

1. Filter and modified the row(s)
2. Filter , Sort and modify the row(s)

Once all the required rows the modified, user will update all the modified data by Clicking "Update" button on the page.

Thanks & Regards,

[jeshcalls]

Hi Cito,

I am trying to use virtual scroll in p-dataTable and find that on lazy load, the new values are loaded and instance variable mapped to [Values] property is updated. When this done in the DOM, the old values are lost and new values are listed and there is no continuity.

I have coded as per prime ng docs and your example but the continuity is missing. The new content is only displayed and the behaviour is as if one more scrolling is done and new content loads to the top of the display..

3010
3011
3012
3013
3014

are displayed now when lazy load completes.

4001(hidden at top)
4002
4003
4004
4005
4006

gets displayed. My data to display is (3010, 3011, 3012, 3013, 3014, 4001, 4002,4003,4004,4005,4006.

Please share your views.

[Cito]

It's important that you pass the event.first, event.rows and sorting properly to your server side. similar to how I did it in the above example. Also note that there is now a new p-table component (aka "TurboTable") and p-dataTable is considered deprecated. If you care about performance, you should first switch to p-table.

[jeshcalls]

Thanks for quick response. The data is retrieved fine from data. The old data is removed immediately from DOM when new data is received leading to loss of continuity in display. The new data is not rendered at the same location of scroll. It dispalys as if one more scroll is done and the entries are moved to the top(3 entries get hidden).

[jeshcalls]

Hi Cito, Now I understood your comment, I was not using the data given by primeng in lazyLoad method that was the problem. Thanks.

[johnrichardrinehart]

What do you mean with "is not working"?

When you're using lazy loading data, you must do the sorting via the lazy load event, e.g. by passing the sortField of the lazy load event as a query parameter that will then be used for the order by clause in an SQL database.

This worked pretty well for me. When changing the sort order, I get the sortField in the lazy load event, and the pagination starts on page 1 again. The only problem I observed is that the scroll position of the component does not jump back to the start.

@Cito I'm actually having this problem with the scroll position not jumping back to the start. How do I force the scroll position to go to the top? I'm using PrimeReact, not PrimeEng.

I'll try elem.scrollTo(0,0)... Let me know if you have other suggestions.