grails - grails hql左外连接

Question

如何保留外部联接2个表？

class Person {

    String firstName
    String lastName
    String gender

    //static hasMany = [votes: Vote]

    static mapping = {
        version false
    }

    static constrains = {
    }

}
class Vote {

    Person voter;
    Person subject;

    static mapping = {
        version false
    }

    static constraints = {
        voter nullable: false
        subject nullable: false
    }


}

我需要让每个人都不必为特定的人投票。
假设某人1票中有5票中有3票，我需要他没有投票给他的另外2票给他看。
该查询应该如何？

编辑:

def personInstance1 = new Person(firstName: "Antonio", lastName: "Vivaldi", gender: "m")
def personInstance2 = new Person(firstName: "Dennis", lastName: "Rodman", gender: "m")
def personInstance3 = new Person(firstName: "Marc", lastName: "Oh", gender: "m")
def personInstance4 = new Person(firstName: "Gudrun", lastName: "Graublume", gender: "w")
def personInstance5 = new Person(firstName: "Hilde", lastName: "Feuerhorn", gender: "w")
def personInstance6 = new Person(firstName: "Mandy", lastName: "Muller", gender: "w")

personInstance1.save()
personInstance2.save()
personInstance3.save()
personInstance4.save()
personInstance5.save()
personInstance6.save()

def voteInstance1 = new Vote(voter: personInstance1, subject: personInstance2)
def voteInstance2 = new Vote(voter: personInstance1, subject: personInstance3)
def voteInstance3 = new Vote(voter: personInstance1, subject: personInstance4)
def voteInstance4 = new Vote(voter: personInstance1, subject: personInstance5)
def voteInstance5 = new Vote(voter: personInstance2, subject: personInstance1)

voteInstance1.save()
voteInstance2.save()
voteInstance3.save()
voteInstance4.save()
voteInstance5.save()

这是我的文件，Antonio和Dennis已投票，并且每个人都需要提供他们未投票的人的名单。

编辑:
这样一来，我似乎为丹尼斯赢得了成果，因为他只投票了一次，
但是如果我把v.voter_id = 1
为了获得安东尼奥的结果，结果将根据他获得的票数加倍。

 SELECT first_name FROM vote as v 
 LEFT OUTER JOIN person as p 
 ON v.subject_id != p.id AND v.voter_id = 2 
 WHERE p.id IS NOT NULL

Answer 1

试试这个:

SELECT * FROM Person P
WHERE NOT EXISTS(
    SELECT 'Vote' FROM Vote V
    WHERE V.subject = P
)

这样，您将提取所有没有投票的人

编辑

在SQL中，您可以通过以下方式检索矩阵:

CREATE TABLE #person (nome varchar(30))
CREATE TABLE #vote (votante varchar(30), candidato varchar(30))

INSERT INTO #person values 
('Antonio Vivaldi'),
('Dennis Rodman'),
('Marc Oh'),
('Gudrun Graublume'),
('Hilde Feuerhorn'),
('Mandy Muller')

INSERT INTO #vote values
('Antonio Vivaldi', 'Dennis Rodman'),
('Antonio Vivaldi', 'Marc Oh'),
('Antonio Vivaldi', 'Gudrun Graublume'),
('Antonio Vivaldi', 'Hilde Feuerhorn'),
('Dennis Rodman', 'Antonio Vivaldi')

SELECT *
FROM #person p
CROSS JOIN #person c
WHERE NOT EXISTS(
    SELECT 'X'
    FROM #vote v
    WHERE v.votante = p.nome
    AND v.candidato = c.nome
)
AND p.nome <> c.nome
ORDER BY p.nome