[TYPO3-mvc] Extbase query with AND over 1:n related objects

g4-lisz at tonarchiv.ch g4-lisz at tonarchiv.ch
Fri Mar 30 03:40:37 CEST 2012


On 03/30/2012 02:12 AM, Jochen Rau wrote:
> Hi,
>    
>> Ok i uncommented the debug line... where does the log go? Do i have
>> to enable logging in the config tool?
>>      
> Yes. But you can also replace the debug with var_dump. Anyway, you
> should not do this in a production environment as the internal database
> structure might become visible to the FE user. The output of debug can
> (and should) be constrained to be visible only for your IP.
>    
Hmmm it is like i excepted... this is the debug output (in this project 
its "klinik" with the property "kategorie"):


*|SELECT DISTINCT tx_hplusinfo_domain_model_klinik.* FROM 
tx_hplusinfo_domain_model_klinik LEFT JOIN 
tx_hplusinfo_klinik_kategorie_mm ON 
tx_hplusinfo_domain_model_klinik.uid=tx_hplusinfo_klinik_kategorie_mm.uid_local 
LEFT JOIN tx_hplusinfo_domain_model_kategorie ON 
tx_hplusinfo_klinik_kategorie_mm.uid_foreign=tx_hplusinfo_domain_model_kategorie.uid 
WHERE (tx_hplusinfo_domain_model_klinik.is_online = '1' AND 
tx_hplusinfo_domain_model_kategorie.uid IN ('1','3')) AND 
tx_hplusinfo_domain_model_klinik.deleted=0 AND 
tx_hplusinfo_domain_model_klinik.pid IN (24, 23, 29) AND 
tx_hplusinfo_domain_model_kategorie.deleted=0 AND 
tx_hplusinfo_domain_model_kategorie.pid IN (24, 23, 29)|*

It does a WHERE klinik.kategorie IN(1, 3) query, so it gets all klinks 
with kategorie 1 _or_ 3 but i'd like kliniks with kategorie 1 _and_ 3...

I cannot imagine a single SQL statement to do this.

I should be something like this (a sub-select for each kategorie):

SELECT * FROM
(
     SELECT klinik.*
     FROM klinik
     JOIN klinik_kategorie_mm ON 
klinik.kategorie=klinik_kategorie_mm.uid_local
     WHERE klinik_kategorie_mm.uid_foreign=1
) sub1
JOIN
(
     SELECT klinik.*
     FROM klinik
     JOIN klinik_kategorie_mm ON 
klinik.kategorie=klinik_kategorie_mm.uid_local
     WHERE klinik_kategorie_mm.uid_foreign=3
) sub2
ON sub1.uid = sub2.uid

Is something similar implemented?

Cheers,
Till


More information about the TYPO3-project-typo3v4mvc mailing list