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

[Brian Bendtsen]

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