Malformed SQL statement when using raw queries

[jeden]

If I use a raw query it doesn't work.

I have a raw query like this:

SELECT * FROM ws_comments comments 
WHERE recommendation_id = ? AND parent_comment_id IS NULL

After applying pagination, this is the actual query sent to the DB

SELECT * FROM ws_comments comments 
WHERE recommendation_id = ? AND parent_comment_id IS NULL
) AND (comments.created_at < '2019-07-29 19:40:58'

The pagination adds a close parenthesis before AND, and an open after, but without their respective counterparts - that generates a malformed statement, which of course fails.

I tried enclosing my filter between parenthesis, but the resulting statement is

SELECT * FROM ws_comments comments 
WHERE (recommendation_id = ? AND parent_comment_id IS NULL
)) AND (comments.created_at < '2019-07-29 19:40:58'

This problem seems to affect raw queries only.

[pilagod]

@jeden

Sorry for the late reply. Thanks for your reporting.

It seems like query builder in GORM cannot build query properly when base statement comes from db.Raw(...).

In paginator, I treat input statement as a query builder, and all paging-related queries are appended through stmt = stmt.Where(...).

To demonstrate what actually happens, if we move out query conditions from your original raw query, and use Where(...) to append these conditions instead:

stmt := db.Raw("SELECT * FROM ws_comments comments")
stmt = stmt.Where("recommendation_id = ?", 123)
stmt = stmt.Where("parent_comment_id IS NULL")

Statement above will generate following sql string:

SELECT * FROM ws_comments comments) AND (recommendation_id = 123) AND (parent_comment_id IS NULL

This is a limitation of raw query in GORM, which cannot fully leverage the power of its query builder. I am wondering if it is possible to change your raw query into query builder pattern, something like:

stmt := db.Table("ws_comments"). // cannot use alias here (describe at last)
    Select("*").
    Where("recommendation_id = ?", 123).
    Where("parent_comment_id IS NULL")

This will work fine as the original raw query, but with more readability and extendibility.

By the way, there are also some peculiarities of table name alias in GORM. For now, it is better to avoid using table name alias when goes with paginator.

[jeden]

Thanks, that might work - I've actually used the table name to overcome that, for now it's working, but probably I will try your suggestion too, which looks more elegant and easier to read.

[jeden]

And this doesn't look like a paginator bug, so I'm closing this issue.