[TYPO3-dev] table joins and TCA

[Martin Kutschker]

Stig N. Færch schrieb:
>>>Wouldn't this be the suggested way to do it from a database viewpoint?
>>
>>The problem with such a DB layout is that you cannot get details for all
>>types with on select nicely. You can get easily the main data with a
>>select on the main table. You can get main data and details for one type
>>with s join. But you would have to do a giant outer join select if you
>>need the details for all types.
> 
> 
> So what you are saying is, that when we join more than one table to the main
> table, we get into problems?

Do you like a stetement like this?

SELECT main.*, detail1.foo, detail2.bar, [etc]
  FROM main
   LEFT OUTER JOIN detail1 USING uid
   LEFT OUTER JOIN detail2 USING uid
   LEFT OUTER JOIN detail3 USING uid
   LEFT OUTER JOIN detail4 USING uid
   LEFT OUTER JOIN detail5 USING uid

This is what you'll need to get all details for all types in one select.

>>So why don't you use flexforms for your detail data? Flexform are great if
>>you need soe extra fields and you set it up so that you have a datasource
>>for each record type. Have a look at tt_content:pi_flexform to see how
>>it's done.
>>
>>Of course you cannot select on flexform data. All fields that must be part
>>of a where clause must be real DB fields.
> 
> I'm not sure that this is the way to go. I might need the where clause on
> these fields.

I doubt you need it on all fields. Pick those you need an put them as DB 
fields. The rest may go intto flexfields.

Real problem: a "full-text" search. If you put "description" fields into 
flex forms you cannot do a search on them easily. What you could do for 
Mysql (beware this is not possible on all DBs) is to do a regexp search 
within these fields (eg /<tag>searchvalue<\/tag>/, where tag is the 
approriate flexform field). On some other DBs you migght be able to do an 
XQuery on the flexform.

> I'm surprised that it will be so difficult to implement something like this.

Really? Well I could imagen that TCE and TCEforms could be adapted to make 
such a combined "virtual record" editable, but to me the above list query 
is a horror if you add more and more subtypes.

> So my only option would be to add fields to the extension main table. If I
> in worst case add 30 fields to the table, should I expect a big perfomance
> hit?

Depends on the number of records. For sorting the DB tries to load all 
records into the memory (mem buffers). If your record size is large this 
will fail earlier than with a small records size (using then buffers on disk).

Masi