Typo3 foreign_table & foreign_table_where in TCA

Question

I am struggling with the following problem.

I have two database tables, "Books" and "Category". I am getting all the data from "books"-table via Sysfolder in Backends List-view for editing, sorting and controlling them.

What I would like to get, is that there would be in that list view also the name of the category where the book belongs.

In "Books"-table there is a field foreign-key "category_id" which defines that for which category the Book belongs. I have tried via this "category_id" to get the name of the Category in List-view of the Books.

When I define in TCA['books'] that category_id like:

'category_id' => array (        
            'exclude' => 0,     
            'label' => 'Cat name',      
            'config' => array (
                'type'     => 'select',
                'foreign_table' => 'category',
                'foreign_table_where' => 'AND category.id=###REC_FIELD_category_id###',
                'eval'     => 'int',
                'checkbox' => '0',
                'default' => 0
            )
        ),

it connects the Books and Categories using category_id (in Books-table) and uid (in Category-table).

Not like I would like, that it would connect them using category_id(in Books-table) and id(in Category-table). This id is a id of the category and can be different that uid.

Am I doing something wrong or does Typo3 somehow automatically makes this "connection" to foreign tables uid. ? Is there some way to get it like I would like?

Answer 1

I'm afraid it's not possible to specify different foreign key. So unless somebody proves me wrong, here is a workaround that I would use.

itemsProcFunc of the select type allows you to completely override the items in the menu and thus create a different relation.

1. Create an extra class that will be loaded in the backend only and that will have the method that will be called in the itemsProcFunc:

   yourMethod($PA, $fobj)

2. Make the method to load all the categories you want to have in the SELECT box and set them in the $PA['items'] by completely overriding it so that it is an array of arrays where the 0 key is element title and 1 key is the category ID that you want. See items in select.

   $PA['items'] = array(array($title, $id, ''));

3. Include the class in the ext_tables.php:

   if(TYPO3_MODE == 'BE') require_once(t3lib_extMgm::extPath($_EXTKEY).'class.YOUR_CLASS.php');

4. Set the config for the category field in the books table:

   'itemsProcFunc' => 'tx_YOUR_CLASS->yourMethod',