how to use sortby with (translated array) with pagination

[YousufRizvi]

i have this data and want to apply sorting on name which is saved like this in db {"myVehicle en","myVehicle fr"}

{ "id": "string", "name": { "en": "string", "fr": "string" }, "licenseNumber": "string", "manufacturer": "string", "modelYear": 0, "type": "string", "capacity": 0, "color": "string", "status": "string", "createdAt": "2024-01-09T12:38:11.325Z" }

[ppetzold]

what exactly is your question/problem? 😅

[YousufRizvi]

actually, this is my paginate config and i have a column name in which data is saved in two languages like english and french
I want to sort it by language like name[1] , is there anything i can do to achieve this?

from API its coming like this

"name": { "en": "string", "fr": "string" }

thanks for your help

import { FilterOperator, PaginateConfig } from 'nestjs-paginate'
import { Vehicle } from '../entity/vehicle.entity'

export const VEHICLE_PAGINATION_CONFIG: PaginateConfig<Vehicle> = {
  sortableColumns: ['name', 'licenseNumber'],
  nullSort: 'last',
  defaultSortBy: [['createdAt', 'DESC']],
  searchableColumns: ['name', 'licenseNumber'],
  filterableColumns: {
    type: [FilterOperator.IN],
    status: [FilterOperator.IN],
    createdAt: [FilterOperator.BTW],
  },
}

[ppetzold]

is this a JSON column, or embedded entity?

[YousufRizvi]

yes this is a column with type VARCHAR [] in postgres db

looks like this when fetched from db

Vehicle {
  id: 'd0f731e6-7d68-43bb-a9ab-03387c127576',
  name: [ 'myVehicle en', 'myVehicle fr' ],
  licenseNumber: null,
  manufacturer: null,
  modelYear: null,
  type: 'bus',
  capacity: null,
  color: null,
  status: 'active',
  createdAt: 2024-01-09T10:03:55.644Z,
  updatedAt: 2024-01-09T10:03:55.644Z,
  deletedAt: null
}

[ppetzold]

You can use $contains operator like ?filter.name=$contains:foobar to filter on array columns

[YousufRizvi]

need to do something like this to sort by array indices, But I think it's not supported

``` export const VEHICLE_PAGINATION_CONFIG: PaginateConfig<Vehicle> = {
  sortableColumns: ['name[0]', 'name[1]', 'licenseNumber'],
  // ... other properties
};

[ppetzold]

This is not supported. You are welcome to submit a feature PR.

Would check array length tho to avoid 500er:

SELECT *
FROM your_table
ORDER BY CASE WHEN array_length(your_array_column, 1) >= 2 THEN your_array_column[2] ELSE NULL END;

[YousufRizvi]

thanks , will submit soon