Take this code example:

return ORMExecuteQuery(
	'FROM user U
	JOIN U.stateLicenses
	WHERE U.company.id IN (:companyIDs)'
	,
	{
		companyIDs = arguments.companyIDs
	},
	false,
	{ ignorecase = true }
);

Each user can have multiple state licenses, thus what we get back here is an array of array of entities that have duplicated users.

Lets take a couple of steps to fix this.

1. Change “JOIN U.stateLicenses” to “JOIN FETCH U.stateLicenses”. This will give us an array to work with instead of an array of an array. It also brings back the licenses right away. I didn’t investigate why this is though.
2. Add “SELECT DISTINCT U” to the beginning of the HQL. This will return distinct user records with an array of licenses. This fixes our duplicate users situation.

The modified working code is:

return ORMExecuteQuery(
	'SELECT DISTINCT U
	FROM user U
	JOIN FETCH U.stateLicenses
	WHERE U.company.id IN (:companyIDs)'
	,
	{
		companyIDs = arguments.companyIDs
	},
	false,
	{ ignorecase = true }
);

#hql