Grails/GORM sequence per table in Postgres

Today I answered a question that outlines how to implement a custom dialect for Hibernate so that your Grails application can use a unique sequence per table within Postgres. I am surprised that not many people have run into this issue as it's a pretty common requirement for applications.

Real-time Grails Help

You may not know, but there is a very active #grails IRC channel on freenode. Come join us and discuss all things Grails. Stay awhile and help others, and learn a few new things too! Hope to see you there!

Multi-Associative GORM Criteria with Projections

I have three domain classes: Lead, Submission and BuyerLog:

	class Lead {
	static hasMany = [ submissions: Submission ]
	Date dateCreated
	// ...
	}
	class Submission {
	static belongsTo = [ lead: Lead ]
	static hasMany = [ buyerLogs: BuyerLog ]
	Lead lead
	// ...
	}
	class BuyerLog {
	static belongsTo = [ submission: Submission ]
	Submission submission
	String leadBuyer
	// ...
	}

view raw blogPost1.groovy hosted with ❤ by GitHub

I have a need to get the number of duplicate leads which share the same leadBuyer (in the BuyerLog domain class). Here is the SQL:

	SELECT count(l.id)
	FROM lead AS l, submission AS s, buyerLog as bl
	WHERE l.id = s.leadId
	AND s.id = bl.submissionId
	AND bl.leadBuyer = $buyerName
	AND l.id != $lead.id
	AND l.dateCreated::date > $daysAgo

view raw blogPost2.sql hosted with ❤ by GitHub

I want to do this using GORM / Criteria Builder. Here's my final code:

	/**
	* Count duplicate submissions within 45 days
	* SELECT count(l.id)
	* FROM lead AS l, submission AS s, buyerLog as bl
	* WHERE l.id = s.leadId
	* AND s.id = bl.submissionId
	* AND bl.leadBuyer = $buyerName
	* AND l.id != $lead.id
	* AND l.dateCreated::date > $daysAgo
	*/
	protected def Boolean isDuplicateSubmission(Lead lead, ArrayList<String> buyerNames) {
	def isDuplicate = false
	def daysAgo = new Date() - 45
	def cnt = Lead.withCriteria {
	not {
	idEq(lead.id)
	}
	and {
	le('dateCreated', daysAgo)
	submissions {
	buyerLogs {
	inList('leadBuyer', buyerNames)
	}
	}
	}
	projections {
	rowCount()
	}
	}
	return (cnt > 0)
	}

view raw blogPost3.groovy hosted with ❤ by GitHub

Thanks to schmolly159 on the #grails freenode IRC channel for the examples and continued help.