I'm trying to create a query with TypeORM and MySQL. I keep getting the following error:

[Nest] 44806 - 12/09/2021, 2:37:03 PM ERROR [ExceptionsHandler] ER_BAD_FIELD_ERROR: Unknown column 'sort_order' in 'order clause' QueryFailedError: ER_BAD_FIELD_ERROR: Unknown column 'sort_order' in 'order clause'

My query is:

const { limit, page: skip, userLat, userLng, searchQuery, weekday, startHour, endHour } = options;
let stores;

// get only stores that open in the start and end hours range
const openHoursQuery = `
          '${startHour}' BETWEEN \`from\` AND \`to\` AND
          '${endHour}' BETWEEN \`from\` AND \`to\`
          AND weekday = ${weekday}
      `;

// get the distance from user's location to each store
const getDistanceQuery = `
          SQRT(
            POW(69.1 * (lat - ${userLat}), 2) +
            POW(69.1 * (${userLng} - \`long\`) * COS(lat / 57.3), 2)
          ) AS distance
        `;

stores = this.storeRepository
  .createQueryBuilder('store')
  .leftJoinAndSelect('store.hours', 'store_hours')
  .addSelect(userLat && userLng ? getDistanceQuery : '')
  .where(searchQuery ? `name LIKE '%${searchQuery}%'` : '')
  .andWhere(weekday && startHour && endHour ? openHoursQuery : '')
  .orderBy(userLat && userLng ? 'distance' : 'sort_order') //sort_order
  .take(limit)
  .skip(skip)
  .getManyAndCount();

return stores;

The problem is caused by the "leftJoinAndSelect" method, when I comment the join the query executes without any problems.

My DB tables look like this:

TABLE: stores

COLUMNS: id, uuid, name, status, address, URL, email, lat, long, sort_order

Table: store_hours

COLUMNS: id, store_id, weekday, from, to, type

EDIT:

I managed to understand the issue, I had to use store.sortOrder which is the name corresponding field in the 'store' Entity.

I have now a follow-up issue that sort by distance is not working when I use the 'join' method.

'distance' is an additional field I created in the select to sort the stores by the distance from the user.

Thank you