CODEWITHSUNDEEP
grails
Dave
Dave

Reputation: 11

Grails criteria query fails with synatx error when using MYSQL

I have a query that fails when the application is connected to a MYSQL instance. The query works fine when using the H2 memory database. All other queries work fine with MYSQL.

error Message: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 1

Controller Action:

def index() {
def currentUser = currentUser()
def peopleFollowing = currentUser.following
def c = Post.createCriteria()
    def postList = c.list  {
       'in' ("user", peopleFollowing)
       'order' "dateCreated", "desc"
    }
[postList:postList, user : currentUser, peopleFollowing:peopleFollowing]
}

The query runs against the MYSQL when I remove the in clause. So it seems that the: 'in' ("user", peopleFollowing) is causing the issue. I think problem is that in is a MYSQL reserved word. I've tried using backticks around in, but keep getting syntax errors...? 

class Post {


String content
Date dateCreated
User user


static belongsTo = [user : User]

static hasMany = [postComments: PostComment]

static constraints = {
    content (blank: false)
}

static mapping = {
    sort dateCreated:"desc"
    content type:"text"
    postComments sort:"dateCreated",  order:"desc"
}

}

class Post { String content Date dateCreated User user static belongsTo = [user : User] static hasMany = [postComments: PostComment] static constraints = { content (blank: false) } static mapping = { sort dateCreated:"desc" content type:"text" postComments sort:"dateCreated", order:"desc" } } class Post { String content Date dateCreated User user static belongsTo = [user : User] static hasMany = [postComments: PostComment] static constraints = { content (blank: false) } static mapping = { sort dateCreated:"desc" content type:"text" postComments sort:"dateCreated", order:"desc" } }

Upvotes: 0

Views: 283

Answers (2)

IgniteCoders
IgniteCoders

Reputation: 4980

I solved it using the ids and adding one 0 to the List:

def currentUser = currentUser()
def peopleFollowingIds = currentUser.following.id
peopleFollowingIds.add(0)
def c = Post.createCriteria()
def postList = c.list  {
    'in' ("user_id", peopleFollowingIds)
    'order' "dateCreated", "desc"
}

Or something like that.

Upvotes: 0

Dave
Dave

Reputation: 11

I fixed the problem by doing this instead:

def postList = Post.findAllByUserInList( peopleFollowing )

Upvotes: 0

Related Questions