Changing Spects to use Exists

gistfile1.groovy

import com.cassidian.ip.data.*
import com.cassidian.ip.data.repository.specification.*
import com.cassidian.ip.data.repository.specification.util.JoinBuilder
import groovy.util.logging.Slf4j
import org.springframework.data.jpa.domain.Specification

import javax.persistence.criteria.CriteriaBuilder
import javax.persistence.criteria.CriteriaQuery
import javax.persistence.criteria.Join
import javax.persistence.criteria.JoinType
import javax.persistence.criteria.Predicate
import javax.persistence.criteria.Root
import javax.persistence.criteria.Subquery

import org.springframework.data.domain.Page
import org.springframework.data.domain.PageRequest
import org.springframework.data.domain.Pageable
import org.springframework.data.domain.Sort
import org.springframework.data.jpa.domain.Specification
import org.springframework.data.jpa.domain.Specifications



import org.apache.log4j.Level
import org.apache.log4j.Logger

Logger.getLogger('org.hibernate.SQL').level = Level.toLevel('debug')







/**
 *  gets all affected orgs of an event i.e. union of all its activationScenario.affectedGeographicArea's
 */
class AffectedGeographicalAreaOfEventSpec extends JoinBuilder implements Specification {


    long eventId

    AffectedGeographicalAreaOfEventSpec(long eventId) {
        this.eventId = eventId
    }

    @Override
    Predicate toPredicate(Root<Organization> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

        Subquery<Event> eventQuery = query.subquery(Event)
        Root<Event> eventRoot = eventQuery.from(Event)

        eventQuery.select(
                eventRoot.join("activationScenarios")
        ).where(
                cb.equal(eventRoot.get("id"), eventId)
        )


        Subquery<ActivationScenario> activationScenariosQuery = query.subquery(ActivationScenario)
        Root<ActivationScenario> activationScenariosRoot = activationScenariosQuery.from(ActivationScenario)

        activationScenariosQuery.select(
                activationScenariosRoot.join("affectedGeographicArea")
        ).where(
                cb.in(activationScenariosRoot, eventQuery)
        )



        cb.in(root, activationScenariosQuery)

    }
}








//returns insteaction of usersjurisdiction and affected geo area of event  - may change it to return a boolean
class UserJurisdictionIntersectsAffectedGeographicalAreaOfEventSpec extends JoinBuilder implements Specification<Organization> {
 
     long userOrganizationId
  	 long eventId
  		
 
     UserJurisdictionIntersectsAffectedGeographicalAreaOfEventSpec(long userOrganizationId, long eventId) {
         this.userOrganizationId = userOrganizationId
         this.eventId = eventId
     }
 
    Predicate toPredicate(Root root, CriteriaQuery query, CriteriaBuilder cb) {
 		
         
      
      	Subquery<Organization> affectedGeographicalAreaOfEventQuery = query.subquery(Organization)
       
		Root<Organization> affectedGeographicalAreaOfEvenRoot = affectedGeographicalAreaOfEventQuery.from(Organization)
      
      	affectedGeographicalAreaOfEventQuery.select(
                affectedGeographicalAreaOfEvenRoot.get("id")
        ).where(new AffectedGeographicalAreaOfEventSpec(eventId).toPredicate(root, query, cb))
      
        
      
      	Subquery<Organization> userJurisdictionQuery = query.subquery(Organization)
       
		Root<Organization> userJurisdictionRoot = userJurisdictionQuery.from(Organization)
      	userJurisdictionQuery.select(
                userJurisdictionRoot.get("id")
        ).where(
               cb.and(
                 cb.in(root.get('id'),userJurisdictionRoot.get('id')),
               cb.or(
                         new NewOrganizationAndDescendants(userOrganizationId).toPredicate(root, query, cb),
                         new NewSharedOrganizationsAndDescendants(userOrganizationId).toPredicate(root, query, cb)
                 ))
          )
      	
      
      	return cb.and(
        	cb.exists(affectedGeographicalAreaOfEventQuery),
            cb.exists(userJurisdictionQuery)
            
        )
         
      
     }
 
 }








//new version of lei's original -
class NewOrganizationsByUserJurisdiction implements Specification<Organization> {

    /**
     * User's organizationId
     */
    Long userOrganizationId

    NewOrganizationsByUserJurisdiction(Long userOrganizationId) {
        this.userOrganizationId = userOrganizationId
    }

    @Override
    Predicate toPredicate(Root<Organization> root, CriteriaQuery<?> query, CriteriaBuilder cb) {

        Predicate predicate = cb.or(
                new NewOrganizationAndDescendants(userOrganizationId).toPredicate(root, query, cb),
                new NewSharedOrganizationsAndDescendants(userOrganizationId).toPredicate(root, query, cb)
        )

        return predicate
    }


}







//new version of lei's original - uses exists
class NewOrganizationAndDescendants implements Specification<Organization> {

    /**
     * ancestor organization id
     */
    private Long ancestorOrganizationId

    NewOrganizationAndDescendants(Long ancestorOrganizationId) {
        this.ancestorOrganizationId = ancestorOrganizationId
    }

    @Override
    Predicate toPredicate(Root<Organization> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        Subquery<Organization> orgQuery = query.subquery(Organization)
        Root<Organization> orgRoot = orgQuery.from(Organization)
        Join ancestors = orgRoot.join("ancestors", JoinType.LEFT)

        orgQuery.select(orgRoot.get("id")).where(
                cb.and(
                cb.in(root.get("id"),orgRoot.get("id") ),  
          		cb.or(
                    cb.equal(orgRoot.get("id"),cb.literal(ancestorOrganizationId)),//user's organization
                    cb.in(ancestors).value(cb.literal(ancestorOrganizationId)),//descendants of user's organization
                )
                  )
        )

      	return cb.exists(orgQuery)
        //return cb.in(root.get("id"), orgQuery)
      
      
    }
}







//new version of lei's original - uses exists
class NewSharedOrganizationsAndDescendants implements Specification<Organization> {

    /**
     * The id of the organization that receives the shared resources from all its Shared Organizations
     */
    private Long receivingOrganizationId

    NewSharedOrganizationsAndDescendants(Long receivingOrganizationId) {
        this.receivingOrganizationId = receivingOrganizationId
    }

    @Override
    Predicate toPredicate(Root<Organization> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
        //Shared organizations
        Subquery<OrganizationSharing> orgSharingQuery = query.subquery(OrganizationSharing)
        Root<OrganizationSharing> orgSharingRoot = orgSharingQuery.from(OrganizationSharing)
        orgSharingQuery.select(orgSharingRoot.get("shared"))
                .where(
                  cb.and(
                  	cb.equal(orgSharingRoot.get("master"), root.get("id")),
                    cb.equal(orgSharingRoot.get("master"), cb.literal(receivingOrganizationId)))
				)
        //Descendants of shared organizations
        Subquery<Organization> descendantsQuery = query.subquery(Organization)
        Root<Organization> descendantsRoot = descendantsQuery.from(Organization)
        Join ancestors = descendantsRoot.join("ancestors", JoinType.LEFT)
        descendantsQuery.select(descendantsRoot.get("id"))
                .where(
                	cb.and(
                      	cb.equal(descendantsRoot.get("id"), root.get("id")),
                  		cb.in(ancestors).value(orgSharingQuery)
                      )
                )

        return cb.or(
                cb.exists(orgSharingQuery),
          		//cb.in(root.get("id")).value(orgSharingQuery),
                cb.exists(descendantsQuery)
          		//cb.in(root.get("id")).value(descendantsQuery)
        )
    }
}









def time = System.currentTimeMillis()



def intersection = ctx.organizationRepository.count( new UserJurisdictionIntersectsAffectedGeographicalAreaOfEventSpec(21624L,59660L ))


//def usersJurisdiction = ctx.organizationRepository.findAll(Specifications.where(new OrganizationsByUserJurisdiction(21624L))).collect{it.id}.toSet()
//def geoAre = ctx.organizationRepository.findAll(Specifications.where(new AffectedGeographicalAreaOfEventSpec( 59660L))).collect{it.id}.toSet()



//def usersJurisdiction =ctx.organizationRepository.findAll(Specifications.where(new OrganizationsByUserJurisdiction(21621L)));  //takes LONG
//def usersJurisdiction =ctx.organizationRepository.findAll(Specifications.where(new OrganizationsByUserJurisdiction(21624L)));   //faster


//def usersJurisdiction = ctx.organizationRepository.findAll(Specifications.where(new NewOrganizationsByUserJurisdiction(21624L))).size()   //faster
//def usersJurisdiction = ctx.organizationRepository.count(Specifications.where(new NewOrganizationsByUserJurisdiction(21621L)))   //slower



//println "jur: ${usersJurisdiction}  }"
//println "geoa: ${geoAre.size()} }"
println "intersection: ${intersection}" 
//println "interset : ${usersJurisdiction.intersect(geoAre).size()}"



//ctx.organizationRepository.count(Specifications.where(new UserJurisdictionIntersectsAffectedGeographicalAreaOfEventSpec( 21621L,59660L)))
//print ctx.organizationRepository.count(Specifications.where(new UserJurisdictionIntersectsAffectedGeographicalAreaOfEventSpec( 21624L,59660L)))

time = System.currentTimeMillis() - time

println "took ${time}"

//ctx.organizationRepository.count(Specifications.where(new AffectedGeographicalAreaOfEventSpec( 59660L)))