在使用关联表的Groovy createCriteria时出现的问题。

huangapple
go评论113阅读模式
英文:

Groovy createCriteria issue with joined table

问题

以下是您要翻译的内容：

我有一个领域类 Coach，它与另一个领域类 CoachProperty 之间存在一对多的关系。

Hibernate/Grails 在数据库中创建了第三个联接表。

在下面的示例中，我试图获取那些其文本值既包含 foo 又包含 bar 的教练。我在 Grails 中尝试了不同的解决方案，使用了 'or' 和 'and'，但要么返回一个空列表，要么返回一个同时包含 foo 和 bar 的列表。

Coach:

  1. class Coach {
  3.     static hasMany = [ coachProperties : CoachProperty ]
  5. CoachProperty:
  7. class CoachProperty {
  9.     String text
  10.     boolean active = true
  11.     
  12.     static constraints = {
  13.         
  14.         text(unique: true, nullable: false, blank: false)
  15.     }
  16. }

数据库中自动创建的联接表，并且我填充了一些数据。在这个示例中，我试图获取教练 372，因为该教练既有 1 又有 2，即 foo 和 bar：

  1. +---------------------------+-------------------+
  2. | coach_coach_properties_id | coach_property_id |
  3. +---------------------------+-------------------+
  4. |                       150 |                 2 |
  5. |                       372 |                 1 |
  6. |                       372 |                 2 |
  7. |                        40 |                 3 |
  8. +---------------------------+-------------------+

在 Coach.createCriteria().list() 中，还有其他的过滤条件。这应该返回教练 372，但却返回空：

  1. def tempList = ["foo", "bar"]			
  2.     coachProperties{
  3.         for(String temp: tempList){
  4.             and {
  5.                 log.info "temp = " + temp
  6.                 ilike("text", temp)
  7.             }
  8.         }						
  9.     }
英文:

I have a domain class Coach which has a has many relationship to another domain class CoachProperty.

Hibernate/Grails is creating a third joined table in the database.

In the example below I am trying to fetch the coaches which both have foo AND bar for their text value. I have tried different solutions with 'or' and 'and' in Grails which either returns an empty list or a list with BOTH foo and bar.

Coach:

  1. class Coach {
  3. static hasMany = [ coachProperties : CoachProperty ]

CoachProperty:

  1. class CoachProperty {
  3. String text
  4. boolean active = true
  6. static constraints = {
  7. 	
  8. 	text(unique: true, nullable: false, blank: false)
  9. }
  10. }

Joined table which is being auto-created and I populated with some data, in this example I am trying to fetch coach 372 since that coach has both 1 and 2 i.e foo and bar:

  1. +---------------------------+-------------------+
  2. | coach_coach_properties_id | coach_property_id |
  3. +---------------------------+-------------------+
  4. |                       150 |                 2 |
  5. |                       372 |                 1 |
  6. |                       372 |                 2 |
  7. |                        40 |                 3 |
  8. +---------------------------+-------------------+

Inside Coach.createCriteria().list() among with other filters. This should return coach 372 but return empty:

  1. def tempList = ["foo", "bar"]			
  2. 					coachProperties{
  3. 						for(String temp: tempList){
  4. 							and {
  5. 								log.info "temp = " + temp
  6. 								ilike("text",temp)
  7. 							}
  8. 						}						
  9. 					}

答案1

得分: 0

我好像记得这个错误。好像是关于不能同时使用可为 null 和空白。尝试只用 'nullable:true'。

英文:

I seem to remember this error. Was something about not being able to use both nullable & blank at the same time.Try with just 'nullable:true'

答案2

得分: 0

我不得不使用executeQuery创建一个解决方法，其中ids是包含我试图提取的coach属性的id的列表。

  1. def coaches = Coach.executeQuery '''
  2.     select coach from Coach as coach 
  3.     join coach.coachProperties as props 
  4.     where props.id in :ids 
  5.     group by coach
  6.     having count(coach) = :count''', [ids: ids.collect { it.toLong() }, count: ids.size().toLong()]
  7.     
  8. or {
  9.     coaches.each {
  10.         eq("id", it.id)
  11.     }
  12. }
英文:

I had to create a workaround with executeQuery where ids is the list containing the id's of the coachproperties i was trying to fetch.

  1. 	def coaches = Coach.executeQuery '''
  2. 					select coach from Coach as coach 
  3. 					join coach.coachProperties as props 
  4. 					where props.id in :ids 
  5. 					group by coach
  6. 					having count(coach) = :count''', [ids: ids.collect { it.toLong() 
  7.                     }, count: ids.size().toLong()]
  8. 						
  9. 					
  10. 					or{
  11. 						coaches.each{ 
  12. 							eq("id", it.id)
  13. 						}
  14. 					}

huangapple
  • 本文由 发表于 2020年10月16日 20:37:51
  • 转载请务必保留本文链接：https://go.coder-hub.com/64389355.html
  • grails
  • groovy
  • hibernate
  • hibernate-criteria
  • java
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定