Grails命名查询：找到A子和B子的父母 [英] Grails named queries: find parents of child A and child B
问题描述
class父类{
字符串名称
static hasMany = [children： Child]
}
class Child {
String name
}
我想找到所有父母都有一个名为Tom的孩子，另一个名为Sam。
id | parentName
---------------
1 |彼得
2 |乔
3 | Ann
id | childName
---------------
1 |汤姆
2 | Sam
idParent | idChild
---------------
1 | 1
2 | 2
3 | 1
3 | 2
在这个例子中，它是Ann。
我试过这个：
static namedQueries = {
findParents {nameList - >
children {
nameList.each {childName->
和{
ilike（'nombre'，'％'+ childName +'％'）
}
}
}
但是这种方式我只在Tom和Sam的名下搜寻一个孩子。
我尝试过在'name'，['Tom'，'Sam']中使用' 代替ilike操作符，但现在我将为所有父母带上名为Tom的孩子或名为Sam的孩子。在这个例子中，它会返回彼得，乔和安
任何想法？
在此先感谢！ / p>
所以我现在明白了，我重新创建了一个答案给你
idParent | idChild
---------------
1 | 1
2 | 2
* 3 | 1
* 3 | 2
所以它真的是3或Ann，你是正确的？
Peter [test.Child：1]
Joe [test.Child：2]
Ann [test.Child：1，test .Child：2]
您想要的是：
list是[[test.Parent：3，test.Child：2，test.Parent：3，test.Child：1]]中使用
E2A 2016年12月比所有早期版本更好的方法
字符串查询=选择新的地图（p.name作为父母名称，pre>
时的情况（从p.children选择count（*）as pc pc.name in（：children））> = 2 then'true'
else'false'end as childMatch）from Parent p
def inputs = [：]
inputs.children = ['Tom'，'Sam']
def listing = Parent.executeQuery（query，inputs，[只读：true]）
产生：
print lnlist is $ listing
list是[[childMatch：false，parentName：Peter]，
[childMatch：false，parentName：Joe]，
[childMatch：true，parentName：Ann ]]
现在，如果我们简单地更改：
< pre $def listing = Parent.executeQuery（query，inputs，[readonly：true]）？\
.findAll {it.childMatch =='true'}
printlnlist is $ listing
list is [[childMatch：true，parentName：Ann]]
您可以看到比以前的方法少得多的并发症 / strong>
您还可以在元素，它绑定到真实对象而不是像下面这样的连接：
（：children）in elements（p.children）
但即使使用这种方法，如下面讨论的问题。这个答案的第一个方法是相当强大的，使用计数你可以用它作为一种权重的形式，看看有多少记录有2多少1多少0等等，所以更多的用法从它 -
以前的方法
我使用hql和交叉加入：
来自父p1的字符串query =left left p1.children pc1，
父p左连接p.children pc其中
pc.name =：input1和pc1.name =：input2和
p1.id = p.id group by p.name
def inputs = [：]
输入。 input1 ='Tom'
inputs.input2 ='Sam'
def list = Parent.executeQuery（query，inputs，[readonly：true]）
printlnlist is $ {list}
但是因为所有的东西都叫做名字，所以如果你觉得它更难管理希望使用grails groupBy来获得每个parentName更紧凑的列表：
String query =select new map（p1 .name作为paren tname，pc1.name作为childName，pc.name作为childName2）从父p1左连接p1.children pc1，
父p左连接p.children pc其中pc.name =：input1和pc1.name =：input2和p1.id = p.id// group by p1.name，p.name
def inputs = [：]
inputs.input1 ='Tom'
inputs.input2 ='Sam'
def list = Parent.executeQuery（query，inputs，[readonly：true]）
def list1 = list.groupBy {it.parentName}
printlnlist是$ {list} vs $ {list1}
像上面这样将它返回给控制台：
Joe [test.Child：2]
Ann [test.Child：2，test.Child：1]
list是[[childName：Sam，childName2：Tom，parentName：Ann]] vs [Ann：[[childName：Sam，childName2：Tom，parentName：Ann]]]
在这一点上最终确定，完全取决于您或者您是否认为这种处理应该保持原样（或许比较费劲，比较轻量级的查询和更多的q
//找到所有有我们匹配的1的父母
// Tom但所有也有孩子大于1
def parents = Parent.findAll {children.name =='Tom'&& children.size（）> 1}
//这已经返回我们唯一的其他选项3
printlnfound $ {parents}
//现在确认孩子的
//父母包含我们的另一种情景：
//让我们从它构建我们自己的列表
def finalList = []
/ /通过我们的父母上市迭代
父母？.each {父母p - >
//如果子名称与其他情况匹配
if（p.children.name.contains（'Sam'））{
//将其添加到我们的finalList
finalList< ;< p
}
}
//我们在这个列表中有正确的结果
println我们现在有$ {finalList}
这可能不会产生跨越连接的大量db查询，但最终还是做了很多小的查找再次列出了我们感兴趣的每个父项的惰性列表（谁有超过1个孩子）
这是场景 - 我想这取决于您的数据模型以及最适合您的方式。最初的hql可能是一个关闭生产轻量级列表的查询。第二个轻量级查询有很多轻量级查找。第二个示例输出：
code> found [test.Parent：3]
我们现在有[test.Parent：3]
上面的finalList是举例说明如何手动完成，整个部分可以转换为1班轮：
def parents = Parent.findAll {children.name =='Tom'&& children.size（）> 1} .collect {[name：it.name，children：it.children]}。findAll {it.children.name.contains（'Sam'）}
产生：
found [ [名称：Ann，children：[test.Child：1，test.Child：2]]]
VS：
def parents = Parent.findAll {children.name =='Tom'&& children.size（）> 1} .collect {[name：it.name，children：it.children.name]}。findAll {it.children.contains（'Sam'）}
产生：
found [ [姓名：Ann，孩子：[Tom，Sam]]]
最后一个例子仅收集特定字段，所以最终的父母列表不包含整个子/父对象，而是包含他们名字的光明列表。
所以请根据需要使用。
I have this:
class Parent {
String name
static hasMany = [children: Child]
}
class Child {
String name
}
And I want to find all the parents that have a child named "Tom" and another one named "Sam".
id | parentName
---------------
1 | Peter
2 | Joe
3 | Ann
id | childName
---------------
1 | Tom
2 | Sam
idParent | idChild
---------------
1 | 1
2 | 2
3 | 1
3 | 2
In the example, it will be Ann.
I've tried this:
static namedQueries = {
findParents{nameList ->
children{
nameList.each{childName->
and{
ilike('nombre', '%'+childName+'%')
}
}
}
But this way I am searching only one child with Tom and Sam in his name. In the example, it will return nothing.
I have tried with 'in' 'name', ["Tom","Sam"] instead ilike operator, but now I will get all parents with a child named "Tom" or a child named "Sam". In the example, it will return Peter, Joe and Ann
Any idea?
Thanks in advance!
解决方案 So I now understand and I recreated and made an answer for you
idParent | idChild
---------------
1 | 1
2 | 2
*3 | 1
*3 | 2
So it is really 3 or Ann that you are after right ?
Peter [test.Child : 1]
Joe [test.Child : 2]
Ann [test.Child : 1, test.Child : 2]
What you want is :
list is [[test.Parent : 3, test.Child : 2, test.Parent : 3, test.Child : 1]]
E2A Dec 2016 A much better way than all of the earlier versions
String query="""select new map(p.name as parentName,
case when (select count(*) from p.children as pc where pc.name in (:children)) >= 2 then 'true'
else 'false' end as childMatch) from Parent p
"""
def inputs=[:]
inputs.children=['Tom','Sam']
def listing = Parent.executeQuery(query,inputs,[readonly:true])
Produces:
println "list is $listing"
list is [[childMatch:false, parentName:Peter],
[childMatch:false, parentName:Joe],
[childMatch:true, parentName:Ann]]
Now if we simply changed:
def listing = Parent.executeQuery(query,inputs,[readonly:true])?\
.findAll{it.childMatch=='true'}
println "list is $listing"
list is [[childMatch:true, parentName:Ann]]
As you can see much less complication than earlier methods
Alternative to above but still not as good as above
You can also use in elements which binds to real object rather than your join like below:
(:children) in elements(p.children)
But even with this method you will hit the issues like discussed below. The very first method on this answer is rather powerful, using the count you could use it as a form of weight to see how many records have 2 how many 1 how many 0 etc so lots more usages from it -
Earlier methods
I used hql and cross joined:
String query="""from Parent p1 left join p1.children pc1 ,
Parent p left join p.children pc where
pc.name =:input1 and pc1.name= :input2 and
p1.id=p.id group by p.name"""
def inputs=[:]
inputs.input1='Tom'
inputs.input2='Sam'
def list = Parent.executeQuery(query,inputs,[readonly:true])
println "list is ${list}"
But because everything is called name it makes things a little more difficult to manage if you want to use the grails groupBy to get a more compact list per parentName:
String query="""select new map(p1.name as parentName, pc1.name as childName, pc.name as childName2) from Parent p1 left join p1.children pc1 ,
Parent p left join p.children pc where pc.name =:input1 and pc1.name= :input2 and p1.id=p.id """ //group by p1.name, p.name"""
def inputs=[:]
inputs.input1='Tom'
inputs.input2='Sam'
def list = Parent.executeQuery(query,inputs,[readonly:true])
def list1=list.groupBy{it.parentName}
println "list is ${list} vs ${list1}"
like above which returns this to the console:
Joe [test.Child : 2]
Ann [test.Child : 2, test.Child : 1]
list is [[childName:Sam, childName2:Tom, parentName:Ann]] vs [Ann:[[childName:Sam, childName2:Tom, parentName:Ann]]]
And to finalise on this point, it is entirely upto you or the given process / complexity on whether you feel this processing should be as it is (maybe quite db strenuous vs more light weight query and some further query from grails with light lookups)
// Find all the parents that have 1 of our matches
// Tom but all that also have children greater than 1
def parents = Parent.findAll{children.name=='Tom' && children.size()>1}
//This already returns our only other option 3
println "found ${parents}"
// Now to confirm that the childrens the
// parent has contains our other scenario:
// Lets build our own list from it
def finalList=[]
//Iteration through our listing of parents
parents?.each { Parent p ->
// If the child name matches other case
if (p.children.name.contains('Sam')) {
// add this to our finalList
finalList << p
}
}
// We have correct results in this list
println "we now have ${finalList}"
That probably did not generate huge db queries cross joining but in the end did lots of small lookups agains the lazy list of each parent that we had an interest in (who had more than 1 child)
This is the scenarios - I guess it is down to your data model and what works best for you. The initial hql maybe a huge one off query that produces a light weight list. 2nd light weight query with lots of light weight lookups.
The second example outputs:
found [test.Parent : 3]
we now have [test.Parent : 3]
The above finalList was to give an example of how you would do it manually, that entire segment could be converted to a 1 liner:
def parents = Parent.findAll{children.name=='Tom' && children.size()>1}.collect{[name:it.name, children:it.children]}.findAll{it.children.name.contains('Sam')}
which produces:
found [[name:Ann, children:[test.Child : 1, test.Child : 2]]]
VS:
def parents = Parent.findAll{children.name=='Tom' && children.size()>1}.collect{[name:it.name, children:it.children.name]}.findAll{it.children.contains('Sam')}
which produces:
found [[name:Ann, children:[Tom, Sam]]]
The very last example collects only specific fields so the final parents listing does not contain entire child/parent objects but a light list containing their names.
So use as required.
这篇关于Grails命名查询：找到A子和B子的父母的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
