I'm trying to sort a table by substring of the string column (originalUrl).
I executed a raw SQL query and it worked well:

SELECT * FROM photo ORDER BY SUBSTRING(originalUrl, -40, 8) ASC;

But this code prints an error:

const [photos, count] = await this.photoRepository
        .createQueryBuilder('photo')
        .orderBy('SUBSTRING(photo.originalUrl, -40, 8)', 'ASC')
        .getManyAndCount();

And the error is:

Error: "SUBSTRING(photo" alias was not found. Maybe you forgot to join it?

I googled this issue many times but I was not able to find any solution. Any help would be greatly appreciated. Thank you!

EDIT:

I checked the console and found out that the alias of originalUrl column in photo table was photo_originalUrl so I tried this code and worked well!

const [photos, count] = await this.photoRepository
        .createQueryBuilder('photo')
        .orderBy(`SUBSTRING("photo_originalUrl", -40, 8)`, 'ASC')
        .getManyAndCount();

EDIT2:

Actually, it didn't work. It returns a substring of "photo_originalUrl" string, not its column value.