[Flow] How to find all accounts for given role?

[Max Mustermann]

i could test it now and the given code causes the following error

An exception occurred while executing 'SELECT t0_.persistence_object_identifier AS persistence_object_identifier0, t0_.accountidentifier AS accountidentifier1, t0_.authenticationprovidername AS authenticationprovidername2, t0_.credentialssource AS credentialssource3, t0_.creationdate AS creationdate4, t0_.expirationdate AS expirationdate5, t0_.party AS party6 FROM typo3_flow_security_account t0_ WHERE (EXISTS (SELECT 1 FROM typo3_flow_security_account_roles_join t1_ INNER JOIN typo3_flow_security_policy_role t2_ ON t1_.flow_policy_role = t2_.identifier WHERE t1_.flow_security_account = t0_.persistence_object_identifier AND t2_.identifier = ?, ?, ?)) ORDER BY t0_.creationdate DESC' with params ["Admin", "Customer", "User"]:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' 'Customer', 'User')) ORDER BY t0_.creationdate DESC' at line 1 - See also: 20140820113650691b56.txt


the relevant part is
AND t2_.identifier = ?, ?, ? which would,
as i understand, results in
AND t2_.identifier = "Admin", "Customer", "User"
so the error makes sense

next thing i tried, is replacing $query->contains() with $query->in()
this results
14-08-20 11:46:50 2551       CRITICAL  ORM                  Uncaught exception in line 429 of /PATHTOPROJECT/Packages/Libraries/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php: [Semantical Error] line 0, col 52 near 'roles IN('Admin',': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected. - See also: 2014082011465042590e.txt
    previousException => Uncaught exception in line 429 of /PATHTOPROJECT/Packages/Libraries/doctrine/orm/lib/Doctrine/ORM/Query/Parser.php: SELECT e FROM TYPO3\Flow\Security\Account e WHERE e.roles IN('Admin', 'Customer', 'User') ORDER BY e.creationDate DESC

well, the $roles in TYPO3\Flow\Security\Account is an array, thus this error makes sense too - i didnt think about $roles here in the first place

anyway, i solved it by query every role in $roles array like suggested using this
	public function listAccountsByRoles($roles){
		$accounts = array();

		if(count($roles)>0){
			foreach($roles as $role){

				$query = $this->accountRepository->createQuery();
				$result = $query->matching(
						$query->contains("roles", $role)
				)->execute()->toArray();

				foreach($result as $account){
					$accounts[$account->getAccountIdentifier()] = $account;
				}
			}
		}
		return $accounts;
	}


above function works
but as an example i could replace it with
SELECT DISTINCT account.* from typo3_flow_security_account as account join typo3_flow_security_account_roles_join as roles
on account.persistence_object_identifier = roles.flow_security_account
where roles.flow_policy_role IN ('Admin', 'User');

which gets me back to my question:
how could one implement above function as a query?

i have two problems i dont know how to solve regarding this:
given the bare table names, how could i create my own very custom query like above?
how to get the table names regarding an entity? e.g. if i want to create the query i would need the table
typo3_flow_security_account joined by typo3_flow_security_account_roles_join

i hope its a bit clearer what my problem is. altough i have a solution i would be interested in how to solve this by a query

thanks all