Issue with cursor_by and JOIN in SeaORM using SQLite

[Losses]

Description

We encountered an issue when using the cursor_by method in SeaORM with a JOIN operation in SQLite. Specifically, when performing a JOIN between two tables (e.g., table A and table B) and using cursor_by on a column from table B, the query fails. The generated SQL incorrectly references the column from table B as if it belongs to table A.

Environment

• Database: SQLite
• ORM: SeaORM
• Version: The issue occurs in version 1.1.0, but not in 0.12.15.

Example

Here's a code snippet illustrating the issue:

let result = media_files::Entity::find()
    .join(
        JoinType::LeftJoin,
        media_file_albums::Relation::MediaFiles.def().rev(),
    )
    .column(media_file_albums::Column::TrackNumber)
    .cursor_by(media_file_albums::Column::TrackNumber)
    .desc()
    .first(20)
    .all(&main_db)
    .await
    .unwrap();

Incorrect SQL Output

SELECT "media_files"."id", "media_files"."file_name", "media_files"."directory", "media_files"."extension", "media_files"."file_hash", "media_files"."last_modified", "media_files"."cover_art_id", "media_files"."sample_rate", "media_files"."duration", "media_file_albums"."track_number" 
FROM "media_files" 
LEFT JOIN "media_file_albums" ON "media_files"."id" = "media_file_albums"."media_file_id" 
ORDER BY "media_files"."track_number" DESC 
LIMIT 20

In this example, track_number is a column from media_file_albums, and we explicitly passed media_file_albums::Column::TrackNumber to cursor_by. However, the generated SQL incorrectly orders by "media_files"."track_number".

Thank you for your attention to this matter!

[Losses]

sea-orm-bug-cursor-by.zip

A minimum reproduce is attached.

[Hmikihiro]

I think to use a column from another table, it might be better to consider using function select_also and cursor_by_other.

Example

let result = media_files::Entity::find()
        .select_also(media_file_albums::Entity)
        .join(
            JoinType::LeftJoin,
            media_file_albums::Relation::MediaFiles.def().rev(),
        )
        .column(media_file_albums::Column::TrackNumber)
        .cursor_by_other(media_file_albums::Column::TrackNumber)
        .desc()
        .first(20)
        .all(&main_db)
        .await
        .unwrap();

SQL output

SELECT 
    "media_files"."id" AS "A_id", 
    "media_files"."file_name" AS "A_file_name", 
    "media_files"."directory" AS "A_directory", 
    "media_files"."extension" AS "A_extension", 
    "media_files"."file_hash" AS "A_file_hash", 
    "media_files"."last_modified" AS "A_last_modified", 
    "media_files"."cover_art_id" AS "A_cover_art_id", 
    "media_files"."sample_rate" AS "A_sample_rate", 
    "media_files"."duration" AS "A_duration", 
    "media_file_albums"."id" AS "B_id", 
    "media_file_albums"."media_file_id" AS "B_media_file_id", 
    "media_file_albums"."track_number" AS "B_track_number", 
    "media_file_albums"."album_id" AS "B_album_id", 
    "media_file_albums"."track_number" 
FROM 
    "media_files" 
LEFT JOIN 
    "media_file_albums" 
ON 
    "media_files"."id" = "media_file_albums"."media_file_id" 
ORDER BY 
    "media_file_albums"."track_number" DESC, 
    "media_files"."id" DESC 
LIMIT 20;