[TYPO3-dev] TCA - type select, select from joined tables?

[Steffen Ritter]

AFAIK Views are saved in DB such as tables, it's a kind of virtual 
table. So just create it once with phpmyadmin. If it's not saved you 
could create it on everey connection. Just add the create sql in install 
tool at the DB Init-Codes (or so).
IN View creation you should declare all columns you need... Its the 
whole table joined, so that you can process your TCA where quite easy 
with myViewSPECIALwhereColumn=###MYPARM###

greetings

Steffen
Brian Bendtsen schrieb:
> Steffen Ritter skrev:
>> CREATE
>> [OR REPLACE]
>> [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
>> [DEFINER = { user | CURRENT_USER }]
>> [SQL SECURITY { DEFINER | INVOKER }]
>> VIEW view_name [(column_list)]
>> AS select_statement
>> [WITH [CASCADED | LOCAL] CHECK OPTION]
>>
>> example:
>> CREATE VIEW v AS SELECT qty, price, qty*price AS value FROM t;
>>
>> more Details and examples you will find mysql reference Manual Chapter 
>> 21 (about Views) especially for you 21.2 (creating views)
>>
>> greetings
>>
>> Steffen
>>
>> Brian Bendtsen schrieb:
>>> Steffen Ritter skrev:
>>>> Well
>>>> forget about this crouded way :P
>>>> much more easy an not misusing typo3 api:
>>>> define a view containing you joined data and define this view as a 
>>>> own table with tca so that typo3 can access it,
>>>> then simply set foreign_table to tx_myext_specialview
>>>>
>>>> greetings
>>>>
>>>> Steffen
>>>
>>> Hi Steffen
>>>
>>> Sounds simple, but how is the view defined, can you give me an 
>>> example or link to one.
>>>
>>> I thank you for your help
>>>
>>> /Brian
> I am a bit confused by this, not the sql but how I implement it in the 
> TCA, if I do something like this:
> 
> $TCA["tx_specialView"] = array(
>  "ctrl" => array(
>     'title => 'specialView'...
> 
> 
> Where do I insert the sql?
> 
> Will this work when the sql will depend on some of the other values in 
> the main table?
> 
> SELECT table1.name FROM table1, table2, table3, table4 WHERE 
> table1.primKey = table2.foreignKey AND table2.primKey = 
> table3.ForeignKey AND table3.primKey = table4.ForeignKey AND 
> table4.primKey = 134 AND table3.field2 = ###REC_FIELD_field### AND 
> table2.field3 = 1;
> 
> I have tried another approach using the itemsProcFunc key pointing to a 
> function that returns values according to the sql abbove. This actually 
> works, the select box on the right is showing the right data.
> 
> But the select box on the left is now empty. Im using a m-m relation 
> with a localkey, foreignkey table
> 
>         "field" => Array (       
>             "exclude" => 1,       
>             "label" => "fieldlabel",       
>             "config" => Array (
>                 "type" => "select",   
>                 //"foreign_table" => "table1",   
>                 //"foreign_table_where" => "ORDER BY table1.name",
>                 "itemsProcFunc" => 
> 'tx_wkleague_addFieldsToFlexForm->getActiveItems',
>                 "MM" => "mmrelationtable",   
>                 "size" => 10,   
>                 "minitems" => 0,
>                 "maxitems" => 50,
>             )
>         ),
> 
> /Brian