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.
1条答案
按热度按时间s5a0g9ez1#
I tried a sample query -
It got executed successfully. I think your problem is not how to use
SUBSTRING
inORDER BY
. There might be an issue while getting thephoto
repository.