"no such column" error was thrown when query with predicate created on related model

[HuiSF]

Describe the bug

Taking schema:

enum PostStatus {
  ACTIVE
  INACTIVE
}

type Post @model {
  id: ID!
  title: String!
  rating: Int!
  status: PostStatus!
  # New field with @hasMany
  comments: [Comment] @hasMany(indexName: "byPost", fields: ["id"])
}

# New model
type Comment @model {
  id: ID!
  postID: ID! @index(name: "byPost", sortKeyFields: ["content"])
  post: Post! @belongsTo(fields: ["postID"])
  content: String!
}

Execute to create post, comment and associate them.

let post = Post(title: "Test post", rating: 10, status: PostStatus.active)
let comment = Comment(post: post, content: "Test comment");

Amplify.DataStore.save(post) { result in
    switch(result) {
    case .success(let savedPost):
        print("saved post: \(savedPost)")
        Amplify.DataStore.save(comment) { result in
            switch(result) {
            case .success(let savedComment):
                print("saved comment: \(savedComment)")
            case .failure(let error):
                print("save comment error: \(error)")
            }
        }
    case .failure(let error):
        print("save post error: \(error)")
    }
}

All good, then run query against the Comment model. Notice that the predicate is Post.keys.status.eq(PostStatus.active).

Amplify.DataStore.query(
    Comment.self,
    where: Post.keys.status.eq(PostStatus.active)
) { result in
    switch(result) {
    case .success(let comments):
        print("comments: \(comments)")
    case .failure(let error):
        print("query error: \(error)")
    }
}

Below error was thrown

no such column: root.status in "select
  "root"."id" as "id", "root"."content" as "content", "root"."createdAt" as "createdAt",
  "root"."updatedAt" as "updatedAt", "root"."postID" as "postID", "post"."id" as "post.id",
  "post"."createdAt" as "post.createdAt", "post"."rating" as "post.rating", "post"."status" as "post.status",
  "post"."title" as "post.title", "post"."updatedAt" as "post.updatedAt"
from Comment as "root"
inner join Post as "post"
  on "post"."id" = "root"."postID"
where 1 = 1
  and "root"."status" = ?"

Where the predicate on the inner join seems wrong

and "root"."status" = ?"

should be as below?

and "status = ?"

NOTE: The same query predicate works in amplify-android without issues. Attaching amplify-android generated SQL command for the same query:

SELECT `comment`.`id`      AS `Comment_id`,
       `comment`.`content` AS `Comment_content`,
       `comment`.`postid`  AS `Comment_postID`,
       `post`.`id`         AS `Post_id`,
       `post`.`rating`     AS `Post_rating`,
       `post`.`status`     AS `Post_status`,
       `post`.`title`      AS `Post_title`
FROM   `comment`
       INNER JOIN `post`
               ON `comment`.`postid` = `post`.`id`
WHERE  status = ?; 

Steps To Reproduce

Steps to reproduce the behavior:
1. Follow the code example in description
2. Observe the error

Expected behavior

The query should works as the same as how it works in amplify-android.

Amplify Framework Version

1.18.1

Amplify Categories

DataStore

Dependency manager

Swift PM

Swift version

5

CLI version

7.6.5

Xcode version

13.2.1

Relevant log output

2021-12-22 12:17:57.279690-0800 v2Relations[64393:1642091] [logging] no such column: root.status in "select
  "root"."id" as "id", "root"."content" as "content", "root"."createdAt" as "createdAt",
  "root"."updatedAt" as "updatedAt", "root"."postID" as "postID", "post"."id" as "post.id",
  "post"."createdAt" as "post.createdAt", "post"."rating" as "post.rating", "post"."status" as "post.status",
  "post"."title" as "post.title", "post"."updatedAt" as "post.updatedAt"
from Comment as "root"
inner join Post as "post"
  on "post"."id" = "root"."postID"
where 1 = 1
  and "root"."status" = ?"

Is this a regression?

No

Regression additional context

No response

Device

iPhone 13 simulators

iOS Version

15.2

Specific to simulators

No response

Additional context

No response

[djorgji]

This bug is affecting us and it is currently a major blocker. Is there an eta on when this will be resolved?

I am getting the same issue when utilizing @hasOne with @belongsTo and trying to query through the @belongsTo relationship.

I am only facing this issue in the iOS version of a flutter app, the android version is working as expected.

Is this possibly related to #965 it seems like there was a regression?

[AkiValiaho]

We are currently experiencing this bug as well and it's a major blocker for using Amplify.

[PetroShylaiev]

It's been quite for a long here, I have the same error with the same schema models configuration but bit simple in terms of absence of the relation 1-to-many, just the field that stores and id of another model

[lawmicha]

Although this works on Android, i'm not sure other platforms allow the same. iOS currently has not implemented or tested this use case:

Amplify.DataStore.query(
    Comment.self,
    where: Post.keys.status.eq(PostStatus.active)
)

There are some designs around nested predicates which will allow this use case to work, ie. comments.post.status.eq(PostStatus.active), thus this is more of a feature request

[github-actions[bot]]

This has been identified as a feature request. If this feature is important to you, we strongly encourage you to give a 👍 reaction on the request. This helps us prioritize new features most important to you. Thank you!