[TYPO3-mvc] How to make a join inside a Query object

[Jochen Rau]

Hi Franz.

On 08.01.10 20:55, Franz Koch wrote:
> how did you solve your problem? I'm currently stuck in the same situation as you, but having to use two joins. After hours trying to find a extbase-way using native functionality (following Jochens hint and started experimenting), I for now came to the conclusion to simply use a TYPO3_DB call and map the results myself. My temporary solution now looks like this:
>
> public function findByStore(Tx_EbWebcatalog_Domain_Model_Store $store) {
> 	$result = NULL;
> 	$res = $GLOBALS['TYPO3_DB']->exec_SELECTquery(
> 		'*',
> 		'tx_ebdatamodel_domain_model_medium
> 			LEFT JOIN tx_ebdatamodel_domain_model_compilation ON tx_ebdatamodel_domain_model_compilation.advertising_medium = tx_ebdatamodel_domain_model_medium.uid
> 			LEFT JOIN tx_ebdatamodel_store_compilation_mm ON tx_ebdatamodel_store_compilation_mm.uid_foreign = tx_ebdatamodel_domain_model_compilation.uid',
> 		'tx_ebdatamodel_store_compilation_mm.uid_local = '.$store->getUid().
> 			' AND tx_ebdatamodel_domain_model_medium.media_type = '.$this->mediaType.
> 			$GLOBALS['TSFE']->cObj->enableFields('tx_ebdatamodel_domain_model_medium').
> 			$GLOBALS['TSFE']->cObj->enableFields('tx_ebdatamodel_domain_model_compilation'),
> 		'tx_ebdatamodel_domain_model_medium.uid',
> 		'tx_ebdatamodel_domain_model_medium.year DESC, tx_ebdatamodel_domain_model_medium.title DESC');
>
> 	if($res) {
> 		$rowIterator = t3lib_div::makeInstance('Tx_Extbase_Persistence_RowIterator');
> 		while ($row = $GLOBALS['TYPO3_DB']->sql_fetch_assoc($res)) {
> 			$rowIterator->append(t3lib_div::makeInstance('Tx_Extbase_Persistence_Row',$row));
> 		}
> 		$GLOBALS['TYPO3_DB']->sql_free_result($res);
> 		
> 		$dataMapper = Tx_Extbase_Dispatcher::getPersistenceManager()->getBackend()->getDataMapper();
> 		$result = $dataMapper->map($this->objectType,$rowIterator);
> 	}
> 	return $result;
> }
>
> Not nice messing with extbase core stuff, but it works. And as I don't care for persistence in this case (which probably even should work), I can live with it for now. But I'm always open to suggestions how to create such a query with extbase features. I already was thinking about extending the Query model of extbase with a join method but finally gave up.


Did you try

$query->statement('SELECT * FROM tx_ebdatamodel_domain_model_medium LEFT 
JOIN tx_ebdatamodel_domain_model_compilation ON [...]')->execute();

It should also do the mapping magic.

Regards
Jochen