grails - 如何使用Grails GORM进行多个字段匹配的左外部联接？

Question

我们需要对grails项目执行以下操作:

select * from message m  
LEFT JOIN votes v ON v.message_id = m.id and v.user_id = 1 
where parent_id is null;

域类非常简单:

class Message {

    String text
    User user

    static belongsTo = [parent:Message]

    static hasMany = [messages:Message, votes:Votes]

    static constraints = {
        parent nullable: true
    }

    static mapping = {
        votes lazy: false
    }
}

class Votes {

    User user
    Message message
    int vote = 0

    static belongsTo = [user:User, message:Message]

    static constraints = {
    }

    static mapping = {
        vote defaultValue: 0
    }
}

到目前为止，我们已经能够使 v.message_id = m.id条件起作用，但是无法将 and v.user_id = 1条件添加到LEFT OUTER JOIN。

这是 Controller 代码:

    def test = Message.withCriteria {

        isNull('parent')
        createAlias('votes', 'v', CriteriaSpecification.LEFT_JOIN)

    } 

任何想法如何做到这一点？

Answer 1

我们弄清楚了，正确的代码是

def test = Message.withCriteria {
        isNull('parent')        
        createAlias('votes', 'v', CriteriaSpecification.LEFT_JOIN,   Restrictions.eq("v.user.id", 1 as long));
    }

这将完全根据需要生成SQL查询。