[TYPO3-dev] SQL queries when record has lots of mm relations

[StephenBungert]

Hello,

I'm building an extension where the primay records have very many mm_tables.

Currently I'm looking up the information for the records and then going
through each of them in a loop and then getting the mm values.

Is there a better way of doing this?

I then tried using exec_SELECT_queryArray and building the sql query myself
and nearly have what I want (at least for one mm just to see if it worked):

$queryParts = Array(
	'SELECT'	=> 'tablea.*, tableb.type',
	'FROM'		=> 'tablea LEFT JOIN tableb_mm ON tablea.uid = tableb_mm.uid_local
LEFT JOIN tableb ON tableb_mm.uid_foreign = tableb.uid',
	'WHERE'		=> 'tablea.pid IN (' . $GLOBALS['TYPO3_DB']->cleanIntList($pids) .
')' . $this->cObj->enableFields('tablea'),
	'GROUPBY'	=> '',
	'ORDERBY'	=> 'name ASC',
	'LIMIT'		=> '10'
);
// . $this->cObj->enableFields($table)
$queryResults = $GLOBALS['TYPO3_DB']->exec_SELECT_queryArray($queryParts);

This returns records from tablea with tablea.type being turned into what is
stored in tableb.type, the problem is that when a record from tablea has
more than 1 related record in tableb I get two or more of the same tablea
records because of their being multiple records in tableb_mm.

I quite new to SQL and tried to see if it was possible to merge them. I
don't want to do this after with the results.


I then tried to use GROUP_CONCAT:
'SELECT'	=> 'tablea.*, GROUP_CONCAT(DISTINCT tableb.type ORDER BY ASC
SEPARATOR ", ")',


But this doesn't seem to work. As I said, I don't have much experience of
SQL and found the information about GROUP_CONCAT here:
http://www.severnsolutions.co.uk/twblog/archive/2009/01/21/string-concatenation-mysql-group-queries

It seems to do what I want. Can any SQL experts here see any mistakes? Is
there a better way for me to do this? I know there is the
exec_SELECT_mm_query but my records have 4 related mm tables and I want to
try and get all the info in just one sql query rather than looking up the
mm_tables separately.



-----
-- 
Stephen Bungert
-- 
View this message in context: http://www.nabble.com/SQL-queries-when-record-has-lots-of-mm-relations-tp24860660p24860660.html
Sent from the TYPO3 Dev mailing list archive at Nabble.com.