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

[Ries van Twisk]

I am not sure if I fully understand what you are trying to do..

However it sounds like that you want get your data in a object tree,
rater then simple records.

Well, in SQL you can only return records, and never a object tree (a  
real object tree that is, SQL can do recursive queries in tree like  
structures).

So, if you want to get your data in a object tree, then you have to  
loop over your dataset.

If you just want to have a 'flat' list of records then this can be  
done with a single SQL command.

So may be you can explain a bit better what you need??

Ries




On Aug 7, 2009, at 3:11 AM, StephenBungert wrote:

>
> 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.
>
> _______________________________________________
> TYPO3-dev mailing list
> TYPO3-dev at lists.netfielders.de
> http://lists.netfielders.de/cgi-bin/mailman/listinfo/typo3-dev


			regards, Ries van Twisk

-------------------------------------------------------------------------------------------------
tags: Freelance TYPO3 Glassfish JasperReports JasperETL Flex Blaze-DS  
WebORB PostgreSQL DB-Architect
email: ries at vantwisk.nl        web:   http://www.rvantwisk.nl/     
skype: callto://r.vantwisk
Phone: +1-810-476-4196    Cell: +593 9901 7694                   SIP:  
+1-747-690-5133