.. You may want to use the usual include line. Uncomment and adjust the path. .. include:: ../Includes.txt ============================== Browser Tutorial Database (en) ============================== :Author: Kasper Skårhøj :Created: 2002-11-01T00:32:00 :Changed: 2011-03-14T21:50:58 :Classification: browser_tut_database_en :Description: Tutorial Database for the Browser - the Frontend Engine :Keywords: forAdmins, forDevelopers, forBeginners, forAdvanced, browser, tutorial, frontend-engine, frontend, engine, database, sql, relations :Author: Dirk Wildt, Die Netzmacher :Email: wildt.at.die-netzmacher.de :Website: http://die-netzmacher.de :Language: en |img-1| |img-2| extKey: browser\_tut\_database\_en |img-3| .. _Browser-Tutorial-Database-en: Browser Tutorial Database (en) ============================== |img-4| Version: 3.6.3, 2011-03-14 Extension Key: browser\_tut\_database\_en - Tutorial for the extension Browser (extkey: browser) Language: en Keywords: forAdmins, forDevelopers, forBeginners, forAdvanced, browser, tutorial, frontend-engine, frontend, engine, database, sql, relations Copyright 2009 - 2011, Dirk Wildt, Die Netzmacher, This document is published under the Open Content License available from `http://www.opencontent.org/opl.shtml `_ The content of this document is related to TYPO3 \- a GNU/GPL CMS/Framework available from www.typo3.org .. _Table-of-Contents: Table of Contents ----------------- `Browser Tutorial Database (en) 1 <#__RefHeading__14741_1144889124>`_ `Introduction 3 <#__RefHeading__14743_1144889124>`_ `What does the Tutorial do? 3 <#__RefHeading__14745_1144889124>`_ `Further Information 3 <#__RefHeading__14747_1144889124>`_ `This tutorial in PDF format 3 <#__RefHeading__14749_1144889124>`_ `Browser Manual and Browser Tutorial Basics 3 <#__RefHeading__14751_1144889124>`_ `What are Relations? 4 <#__RefHeading__14753_1144889124>`_ `TYPO3 Relations 4 <#__RefHeading__14755_1144889124>`_ `Simple Relation 5 <#__RefHeading__14757_1144889124>`_ `MM Relation 6 <#__RefHeading__14759_1144889124>`_ `Programming your own Relation? 6 <#__RefHeading__14761_1144889124>`_ `Example SQL Query 7 <#__RefHeading__14763_1144889124>`_ `Enjoy the Browser 7 <#__RefHeading__14765_1144889124>`_ `What does the Browser do? 7 <#__RefHeading__14767_1144889124>`_ `Configuration with the Plugin 8 <#__RefHeading__14769_1144889124>`_ `Simple Relation - Example with FE Users and their Groups 8 <#__RefHeading__14771_1144889124>`_ `TypoScript 8 <#__RefHeading__14773_1144889124>`_ `Configuring the Plugin 8 <#__RefHeading__14775_1144889124>`_ `Result in the Frontend 9 <#__RefHeading__14777_1144889124>`_ `Simple Relation - Example with BE Users and their Groups 9 <#__RefHeading__14779_1144889124>`_ `Records on the Root Level 9 <#__RefHeading__14781_1144889124>`_ `TypoScript 9 <#__RefHeading__14783_1144889124>`_ `Configuring the Plugin 9 <#__RefHeading__14785_1144889124>`_ `Result in the Frontend 10 <#__RefHeading__14787_1144889124>`_ `MM Relation - Example with News and their Categories 11 <#__RefHeading__14789_1144889124>`_ `TypoScript 11 <#__RefHeading__14791_1144889124>`_ `Configuring the Plugin 11 <#__RefHeading__14793_1144889124>`_ `Result in the Frontend 11 <#__RefHeading__14795_1144889124>`_ `Configuration with TypoScript 12 <#__RefHeading__14797_1144889124>`_ `Customizing 12 <#__RefHeading__14799_1144889124>`_ `Plugin 12 <#__RefHeading__14801_1144889124>`_ `TypoScript 13 <#__RefHeading__14803_1144889124>`_ `DRS - Development Reporting System 13 <#__RefHeading__14805_1144889124>`_ `Simple Relation 14 <#__RefHeading__14807_1144889124>`_ `Plugin 14 <#__RefHeading__14809_1144889124>`_ `TypoScript 14 <#__RefHeading__14811_1144889124>`_ `Simple Relation - Several Relations to one Foreign Table 15 <#__RefHeading__11816_1496035429>`_ `Under Development 15 <#__RefHeading__11818_1496035429>`_ `TypoScript 15 <#__RefHeading__14811_11448891241>`_ `Simple Relation with configured Relation Field 16 <#__RefHeading__22372_1203822456>`_ `Example: Mapping to fe\_users.username 16 <#__RefHeading__22458_1203822456>`_ `MM Relation 16 <#__RefHeading__14813_1144889124>`_ `Plugin 17 <#__RefHeading__14815_1144889124>`_ `TypoScript 17 <#__RefHeading__14817_1144889124>`_ `One table, nine relations 18 <#__RefHeading__14819_1144889124>`_ `SQL Functions 19 <#__RefHeading__14821_1144889124>`_ `Step 1: Synonyms 19 <#__RefHeading__14823_1144889124>`_ `TypoScript 20 <#__RefHeading__14825_1144889124>`_ `Step 2: Hierarchical Order 21 <#__RefHeading__14827_1144889124>`_ `TypoScript 22 <#__RefHeading__14829_1144889124>`_ `The Generated SQL Query 23 <#__RefHeading__14831_1144889124>`_ `orderBy 24 <#__RefHeading__14833_1144889124>`_ `Local table (parent) 24 <#__RefHeading__14835_1144889124>`_ `TypoScript snippet 24 <#__RefHeading__14837_1144889124>`_ `Comment 24 <#__RefHeading__14839_1144889124>`_ `Reference 24 <#__RefHeading__14843_1144889124>`_ `Foreign table (children) 25 <#__RefHeading__2520_359489424>`_ `Screen shots 25 <#__RefHeading__2522_359489424>`_ `TypoScript snippet 26 <#__RefHeading__2524_359489424>`_ `Comment 26 <#__RefHeading__14839_11448891241>`_ `Reference 26 <#__RefHeading__14843_11448891241>`_ `Reference 27 <#__RefHeading__14845_1144889124>`_ `What's new in Version 3.6? 28 <#__RefHeading__22460_1203822456>`_ `Simple relations: Configurable foreign table field 28 <#__RefHeading__15238_354458180>`_ `Helpful suggestions 29 <#__RefHeading__14847_1144889124>`_ `Forum 29 <#__RefHeading__22462_1203822456>`_ `Credits 30 <#__RefHeading__8088_353271708>`_ `netresearch 30 <#__RefHeading__8090_353271708>`_ `To-Do List 31 <#__RefHeading__14849_1144889124>`_ `Change Log 32 <#__RefHeading__14851_1144889124>`_ `Illustration Index 33 <#__RefHeading__14853_1144889124>`_ |img-5| Illustration 1: Tutorial in PDF format Introduction What does the Tutorial do? ^^^^^^^^^^^^^^^^^^^^^^^^^^ This is a tutorial for the extension Browser (extkey: browser). It is a step-by-step introduction for the configuration of the database. only. Topics are - Relation Building - Using special functions, which are provided by the Browser. If you are interested in other issues, please note the section "Further Informations" below. .. _Further-Information: Further Information ^^^^^^^^^^^^^^^^^^^ .. _This-tutorial-in-PDF-format: This tutorial in PDF format """"""""""""""""""""""""""" You find this manual as PDF file at - doc/manual.pdf - `http://typo3.org/extensions/repository/view/browser\_tut\_database\_e n/current/info/?tx\_terfe\_pi1%5BdownloadFile%5D=doc%252Fmanual.pdf `_ .. _Browser-Manual-and-Browser-Tutorial-Basics: Browser Manual and Browser Tutorial Basics """""""""""""""""""""""""""""""""""""""""" Manual: The Browser has it's own manual (with the reference of course). - HTML `http://typo3.org/documentation/document-library/extension- manuals/browser/current/ `_ - PDF: `http://typo3.org/extensions/repository/view/browser/current/info /?tx\_terfe\_pi1%5BdownloadFile%5D=doc%252Fmanual.pdf `_ Tutorial Basics: A lot of step-by-step introductions. - HTML: `http://typo3.org/documentation/document-library/extension- manuals/browser\_tut\_basics\_en/current/ `_ - PDF: `http://typo3.org/extensions/repository/view/browser\_tut\_basics \_en/current/info/?tx\_terfe\_pi1%5BdownloadFile%5D=doc%252Fmanual.pdf `_ .. _What-are-Relations: What are Relations? ------------------- If you are using more than one table, you need a relation, which connects both tables with each other. You are working with relations all the time. For example: - If you selecting a group for a backend user. - If you selecting a category for a news. See the illustrations below. |img-6| |img-6| .. _TYPO3-Relations: TYPO3 Relations ^^^^^^^^^^^^^^^ TYPO3 enables two relation methods: - The "Comma list" method: The local tables stores the relation to the records in a foreign table as a comma list. We call the comma list method the simple relation. - The "MM" method: Relations will be stored in an extra relation table. .. _Simple-Relation: Simple Relation """"""""""""""" |img-6| The local table be\_users (on the left hand) stores in the field "usergroup" the uids of the records in the table be\_groups (right). "usergroup " is empty, if there is no relation like in the record 1. "usergroup" has comma separated values (csv), if there is more than one relation like in the record 7. .. _MM-Relation: MM Relation """"""""""" |img-6| The local table tt\_news (on the left hand) needs the field "category" only for storing the case, if a record has a relation or not. If it has a relation, category will be 1, if it hasn't, category will be 0. The MM table tt\_news\_cat\_mm stores any relation (middle). Any relation needs one record. It connects a record in the local table with its uid to the record in the foreign table (right hand). |img-6| The tt\_news\_record 5 has one relation in the illustration above. See the table tt\_news\_cat\_mm in the middle of the illustration. The tt\_news record 11 has four relations. .. _Programming-your-own-Relation: Programming your own Relation? ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ If you are developing an extension with a frontend plugin, you have to program any relation by your own. You have to care about - permissions like if a record is hidden, - if it is owned by a frontend user group, - if it has needs for a search and - if it has needs for localization. If you like to program a professional extension, you will have a lot of work. .. _Example-SQL-Query: Example SQL Query """"""""""""""""" Maybe you guess it's easy. We show you a SQL query, which should displays - a news with its category - in a language selected by the visitor - and with the sword "EUR" SELECT tt\_news.title AS 'tt\_news.title', tt\_news\_cat.title AS 'tt\_news\_cat.title', tt\_news.uid AS 'tt\_news.uid', tt\_news\_cat.uid AS 'tt\_news\_cat.uid', tt\_news.sys\_language\_uid AS \`tt\_news.sys\_language\_uid\`, tt\_news.l18n\_parent AS \`tt\_news.l18n\_parent\`, tt\_news\_cat.title\_lang\_ol AS \`tt\_news\_cat.title\_lang\_ol\` FROM tt\_news LEFT JOIN tt\_news\_cat\_mm ON (tt\_news.uid = tt\_news\_cat\_mm.uid\_local ) LEFT JOIN tt\_news\_cat ON ( tt\_news\_cat\_mm.uid\_foreign = tt\_news\_cat.uid AND tt\_news\_cat.deleted=0 AND tt\_news\_cat.hidden=0 AND ( tt\_news\_cat.starttime<=1254660120 ) AND ( tt\_news\_cat.endtime=0 OR tt\_news\_cat.endtime>1254660120 ) AND ( tt\_news\_cat.fe\_group='' OR tt\_news\_cat.fe\_group IS NULL OR tt\_news\_cat.fe\_group='0' OR ( tt\_news\_cat.fe\_group LIKE '%,0,%' OR tt\_news\_cat.fe\_group LIKE '0,%' OR tt\_news\_cat.fe\_group LIKE '%,0' OR tt\_news\_cat.fe\_group='0' ) OR ( tt\_news\_cat.fe\_group LIKE '%,-1,%' OR tt\_news\_cat.fe\_group LIKE '-1,%' OR tt\_news\_cat.fe\_group LIKE '%,-1' OR tt\_news\_cat.fe\_group='-1' ) ) AND tt\_news\_cat.pid IN (1081) ) WHERE tt\_news.deleted=0 AND tt\_news.t3ver\_state<=0 AND tt\_news.hidden=0 AND ( tt\_news.starttime<=1254660120 ) AND ( tt\_news.endtime=0 OR tt\_news.endtime>1254660120 ) AND ( tt\_news.fe\_group='' OR tt\_news.fe\_group IS NULL OR tt\_news.fe\_group='0' OR ( tt\_news.fe\_group LIKE '%, 0, %' OR tt\_news.fe\_group LIKE '0, %' OR tt\_news.fe\_group LIKE '%, 0' OR tt\_news.fe\_group='0' ) OR ( tt\_news.fe\_group LIKE '%, -1, %' OR tt\_news.fe\_group LIKE '-1, %' OR tt\_news.fe\_group LIKE '%, -1' OR tt\_news.fe\_group='-1' ) ) AND ( tt\_news.sys\_language\_uid <= 0 OR tt\_news.sys\_language\_uid = 1 ) .. _AND-tt-news-title-LIKE-EUR-OR-tt-news-cat-title-LIKE-EUR: AND ( tt\_news.title LIKE '%EUR%' OR tt\_news\_cat.title LIKE '%EUR%' ) AND tt\_news.pid IN (1081) ORDER BY tt\_news.title, tt\_news\_cat.title Do you guess any longer, it's easy to build a professional relation? .. _Enjoy-the-Browser: Enjoy the Browser ^^^^^^^^^^^^^^^^^ .. _What-does-the-Browser-do: What does the Browser do? """"""""""""""""""""""""" Let the Browser work for you: It generates the whole query. .. _Configuration-with-the-Plugin: Configuration with the Plugin ----------------------------- |img-6| The Browser processes both table relations by default. .. _Simple-Relation-Example-with-FE-Users-and-their-Groups: Simple Relation - Example with FE Users and their Groups ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ You need the tables fe\_users and fe\_groups. .. _TypoScript: TypoScript """""""""" plugin.tx\_browser\_pi1 { views.list.1.select = fe\_users.username, fe\_groups.title } .. _Configuring-the-Plugin: Configuring the Plugin """""""""""""""""""""" |img-6| The Startingpoint must be connected with the sysfolder, which contains the frontend user - in our example it is the sysfolder fe\_user. That's all. .. _Result-in-the-Frontend: Result in the Frontend """""""""""""""""""""" |img-6| .. _Simple-Relation-Example-with-BE-Users-and-their-Groups: Simple Relation - Example with BE Users and their Groups ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ .. _Records-on-the-Root-Level: Records on the Root Level """"""""""""""""""""""""" To display backend users is a special case, because they aren't stored in any sysfolder. All be\_users have the pid 0, the pid of the TYPO3 root. The Browser enables the displaying of records on the root level. .. _TypoScript: TypoScript """""""""" plugin.tx\_browser\_pi1 { views.list.1.select = be\_users.username, be\_groups.title } |img-7| Illustration 10: Display records without any pid Configuring the Plugin Please configure the tab [General]. Field [Configure Relations]: Please select "Configured" Field [Records without Pid]: Please enable "Display records of the root level too". Field [Startingpoint]: Leave it empty, you don't need it. That's all. .. _Result-in-the-Frontend: Result in the Frontend """""""""""""""""""""" |img-6| .. _MM-Relation-Example-with-News-and-their-Categories: MM Relation - Example with News and their Categories ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ You need the extension tt\_news. .. _TypoScript: TypoScript """""""""" plugin.tx\_browser\_pi1 { views.list.1.select = tt\_news.title, tt\_news\_cat.title } .. _Configuring-the-Plugin: Configuring the Plugin """""""""""""""""""""" |img-6| The Startingpoint must be connected with the sysfolder, which contains the news - in our example it is the sysfolder news. That's all. .. _Result-in-the-Frontend: Result in the Frontend """""""""""""""""""""" |img-6| .. _Configuration-with-TypoScript: Configuration with TypoScript ----------------------------- Sometimes you don't have a proper TYPO3 relation. One reason for a non proper TYPO3 relation can be, that an extension is developed as a standalone application and is integrated in TYPO3 later. The extension civserv is an example. In cases of failure there are two possibilities for a solution: If you have a simple relation:Use the automatically relation building but adapt the configuration array plugin.tx\_browser\_pi1.autoconfig.relations If you have a complex relation, this is recommended:Build your own SQL query. .. _Customizing: Customizing ^^^^^^^^^^^ .. _Plugin: Plugin """""" |img-6| Set the field [Table Relations] to "Only TypoScript" .. _TypoScript: TypoScript """""""""" The relation building has a TypoScript array. plugin.tx\_browser\_pi1 { autoconfig { relations { ... } } } The elements of the array should be self-explanatory. See the illustration below. |img-6| .. _DRS-Development-Reporting-System: DRS - Development Reporting System """""""""""""""""""""""""""""""""" Switch on the DRS - Development Reporting System. Take one of these logging modes: - SQL development - All You should get helpful messages, if you want to change the configuration for auto relation building like this: [INFO/SQL] TCA 'tt\_news.category.config.foreign\_table: 'tt\_news\_cat' [INFO/SQL] Result (MM): tt\_news - tt\_news\_cat\_mm - tt\_news\_cat [HELP/SQL] Switch off the result above? Use TS config: autoconfig.csvDontUseFields = ...,tt\_news.category, ... See the illustration below. |img-6| .. _Simple-Relation: Simple Relation ^^^^^^^^^^^^^^^ This is an example with the frontend users. You need the tables fe\_users and fe\_groups. .. _Plugin: Plugin """""" |img-6| Set the field [Table Relations] to "Only TypoScript" Startingpoint has to be the sysfolder, which contains the frontend users and their groups. .. _TypoScript: TypoScript """""""""" plugin.tx\_browser\_pi1 { autoconfig { relations = 0 } views { list { 1 { select = fe\_users.username, fe\_groups.title relations { simple { fe\_users { usergroup = fe\_groups } } } } } } } **Comment** Line 3: The Browser doesn't build any relation. From line 9 to 15: Building the relation by your own. The syntax should be easy to understand: - relations.simple. *your\_table.your\_field\_for\_relation = related\_table* - relations.simple. *fe\_users.usergroup = fe\_group* .. _Simple-Relation-Several-Relations-to-one-Foreign-Table: Simple Relation - Several Relations to one Foreign Table ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ Since version 3.4.4 it is possible, to use several relations to one foreign table. This feature is wonderful, if you like to relate from several fields in the local table to a table like fe\_users. .. _Under-Development: Under Development """"""""""""""""" Take care: This feature is under development. Restrictions: - I is only available for single views. - The value of the TypoScript array has to contain marker with the foreign table. - The TypoScript array type has to be TEXT. There isn't any localisation support! .. _TypoScript: TypoScript """""""""" plugin.tx\_browser\_pi1 { views { single { 1 { select ( tx\_organiser\_production.title, tx\_organiser\_production.actor ) tx\_organiser\_production { actor = COA actor { 10 = TEXT 10 { browser { handleRelation = 1 orderBy = fe\_users.last\_name devider = TEXT devider { value = , noTrimWrap = \|\| \| } } value = ###FE\_USERS.FIRST\_NAME### ###FE\_USERS.LAST\_NAME### typolink { parameter = {$myConst.pages.staff} parameter { insertData = 1 } additionalParams = &tx\_browser\_pi1[staffUid]=###FE\_USERS.UID###&###CHASH### } } } } } } } } .. _Comment: Comment ~~~~~~~ The TypoScript snippet from above displays a production `:sup:`0` <#sdfootnote1sym>`_ (local table) with a list of fe\_users (foreign table). There are two specialties: - The select statement doesn't contain any foreign table. fe\_users is missing. It isn't needed. - The TEXT array has an array browser. .. _In-Details: In Details ~~~~~~~~~~ Line 10 - 12: The TEXT array have to be a member of a COA array Line 14 - 22: browser arrayhandleRelation: You have to enable the feature (because it has performance costs)orderBy: The statement will be part of the SQL query.devider: Array for deviding the children records. First record will ignore it. If devider isn't set,. the Browser will take the values from the array advanced.sql.devider.childrenRecords.devider has to be of the type TEXT. .. _Simple-Relation-with-configured-Relation-Field: Simple Relation with configured Relation Field ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ It is possible to configure which foreign table field will be used as relation endpoint. Currently, 'uid' is always used: relations { simple { tx\_some\_projektliste { proj\_dozent = fe\_users } } } This maps tx\_some\_projektliste.proj\_dozent to fe\_users.uid and it is the same like relations { simple { tx\_some\_projektliste { proj\_dozent = fe\_users.uid } } } .. _Example-Mapping-to-fe-users-username: Example: Mapping to fe\_users.username """""""""""""""""""""""""""""""""""""" If you like to map to username, you have to use this TypoScript snippet relations { simple { tx\_some\_projektliste { proj\_dozent = fe\_users.username } } } .. _MM-Relation: MM Relation ^^^^^^^^^^^ This is an example with the extension tt\_news. .. _Plugin: Plugin """""" |img-6| Set the field [Table Relations] to "Only TypoScript" Startingpoint has to be the sysfolder, which contains the news and categories. .. _TypoScript: TypoScript """""""""" plugin.tx\_browser\_pi1 { autoconfig { relations = 0 } views { list { 1 { select = tt\_news.title, tt\_news\_cat.title relations { mm { tt\_news { tt\_news\_cat\_mm = tt\_news\_cat } } } } } } } **Comment** Line 3: The Browser doesn't build any relation. From line 9 to 15: Building the relation by your own. The syntax should be easy to understand: - relations.mm. *your\_table.your\_table\_for\_relation = related\_table* - relations.mm. *tt\_news.tt\_news\_cat\_mm =tt\_news\_cat* .. _One-table-nine-relations: One table, nine relations ^^^^^^^^^^^^^^^^^^^^^^^^^ If you need an example for an table with a lot of relations here it is. In this case we have a database with ships. The most data are stored in one table, the main table: tx\_ships\_main. But all data for select boxes i. e. are stored in other tables. relations { mm { tx\_ships\_main { tx\_ships\_main\_g3\_application\_mm = tx\_ships\_application tx\_ships\_main\_a3\_boilerconstructor\_mm = tx\_ships\_boilerconstructor tx\_ships\_main\_g7\_class\_mm = tx\_ships\_class tx\_ships\_main\_a2\_engineconstructor\_mm = tx\_ships\_engineconstructor tx\_ships\_main\_a2\_enginesystem\_mm = tx\_ships\_enginesystem tx\_ships\_main\_g7\_material\_mm = tx\_ships\_material tx\_ships\_main\_g2\_shipowner\_mm = tx\_ships\_owner tx\_ships\_main\_g3\_powersystem\_mm = tx\_ships\_powersystem tx\_ships\_main\_g3\_rigortype\_mm = tx\_ships\_rigortype } } } We will publish the extension ships too but later. The extension ships is a backend extension really, It hasn't any frontend possibility. All datas are delivered to the frontend by this extension Browser. .. _SQL-Functions: SQL Functions ------------- The Browser Tutorial Database (en) is extended with three functions in context with SQL: - clean up - synonyms - hierarchical All three functions are developed for using the database of the extension "Virtual Civil Services" `:sup:`0` <#sdfootnote2sym>`_ (extKey: civserv). And all three are useful for every other database of any extension too. .. _Step-1-Synonyms: Step 1: Synonyms ^^^^^^^^^^^^^^^^ You can use synonyms for your data. - You can use a synonym of a field of your choice for the whole value only. - The value of the synonym needs its own field. - The amount of fields for synonyms is unlimited. - You have to configure the SQL statement in TypoScript in the manual mode. Example for the structure in the database table tx\_civserv\_service: |img-6| .. _TypoScript: TypoScript """""""""" You find the TypoScript in res/tutorial\_01/step\_01.txt plugin.tx\_browser\_pi1 { views { list { 1 { select = sv.uid AS 'sv.uid', sv.sv\_name AS 'sv.sv\_name' from { table = tx\_civserv\_service alias = sv } andWhere = sv.pid IN (###PID\_LIST###) aliases { tables { sv = tx\_civserv\_service } fields { uid = sv.uid sv\_name = sv.sv\_name } } functions { synonym = 1 synonym { andSelect = 'synonym\_value' AS 'sv.synonym\_alias' for { table\_field = sv.sv\_name noTrimWrap = \| (= \|)\| } csvTableFields = sv.sv\_synonym1, sv.sv\_synonym2, sv.sv\_synonym3 synonym\_value = synonym\_value synonym\_alias = synonym\_alias andWhere = AND synonym\_alias != '' } clean\_up { csvTableFields = sv.synonym\_alias } } } } } } We have an example in the screen shot above with the record 478, which has one name and three synonyms: - uid: 478 - sv\_name: Schwerbehindertenfeststellung - sv\_synonym1: Schwerbehindertenausweis - sv\_synonym2: Schwerbehindertenangelegenheiten - sv\_synonym3: Versorgungsamt We will have the following list in the frontend with the TypoScript from above: |img-6| Because of the sword is we have only results with the string part "Schwerbehindert". .. _Step-2-Hierarchical-Order: Step 2: Hierarchical Order ^^^^^^^^^^^^^^^^^^^^^^^^^^ The Browser Tutorial Database (en) can order datas hierarchical. It is a function, which optimized the Browser Tutorial Database (en) for teamwork with or using the database of the extension "Virtual Civil Services" (extKey: civserv). |img-6| There is the condition precedent to the following database structure: table\_data.uid <-> uid\_local.table\_mm.uid\_foreign <-> uid.table\_data Or in simplified SQL (without any TYPO3 needs): SELECT table\_data.uid, table\_data.title FROM table\_data LEFT JOIN table\_mm ON table\_data.uid = table\_mm.uid\_local LEFT JOIN table\_data AS table\_data2 ON table\_data2.uid = table\_mm.uid\_foreign .. _TypoScript: TypoScript """""""""" You find the TypoScript in res/tutorial\_01/step\_02.txt plugin.tx\_browser\_pi1 { views { list { 1 { template < plugin.tx\_browser\_pi1.template template { file = EXT:browser/res/extensions/civserv/res/organisation.tmpl } select = or.uid AS \`or.uid\`, or.or\_name AS \`or.or\_name\` from { table = tx\_civserv\_organisation alias = or } andWhere = or.pid IN (###PID\_LIST###) joins { 0 { type = LEFT JOIN table = tx\_civserv\_organisation\_or\_structure\_mm alias = or\_or2 on = or.uid = or\_or2.uid\_local } 1 { type = LEFT JOIN table = tx\_civserv\_organisation alias = or2 on = or2.uid = or\_or2.uid\_foreign AND or2.pid IN (###PID\_LIST###) } } aliases { tables { or = tx\_civserv\_organisation or2 = tx\_civserv\_organisation or\_or2 = tx\_civserv\_organisation\_or\_structure\_mm } fields { uid = or.uid pid = or2.pid } } functions { hierarchical = 1 hierarchical { andSelect = or2.uid AS \`or.pid\` order\_tableField = tx\_civserv\_organisation.or\_name wrap\_tableField = \ \| display\_root = 0 } clean\_up { csvTableFields = or.pid } } } } } } .. _The-Generated-SQL-Query: The Generated SQL Query """"""""""""""""""""""" If you like to imagine the power of the Browser, please take a look in the generated SQL query: SELECT or.uid AS \`or.uid\` , or.or\_name AS \`or.or\_name\`, or2.uid AS \`or.pid\` FROM tx\_civserv\_organisation AS \`or\` LEFT JOIN tx\_civserv\_organisation\_or\_structure\_mm AS \`or\_or2\` ON ( or.uid = or\_or2.uid\_local ) LEFT JOIN tx\_civserv\_organisation AS \`or2\` ON ( or2.uid = or\_or2.uid\_foreign AND or2.pid IN ( 1094, 1153, 1146, 1152, 1151, 1150, 1149, 1148, 1147, 1140, 1145, 1144, 1143, 1142, 1141,1129, 1139, 1138, 1137, 1136, 1135, 1134, 1133, 1132, 1131, 1130, 1120, 1128, 1127, 1126,1125, 1124, 1123, 1122, 1121, 1113, 1119, 1118, 1117, 1116, 1115, 1114, 1104, 1112, 1111,1110, 1109, 1108, 1107, 1106, 1105, 1100, 1103, 1102, 1101, 1096, 1099, 1098, 1097, 1095,1091, 1093, 1092, 1090, 1089, 1082, 1088, 1087, 1086, 1085, 1083, 1084, 1081, 1078, 1080,1079, 1077 ) AND or2.deleted =0 AND or2.hidden =0 AND ( or2.fe\_group = '' OR or2.fe\_group IS NULL OR or2.fe\_group = '0' OR ( or2.fe\_group LIKE '%,0,%' OR or2.fe\_group LIKE '0,%' OR or2.fe\_group LIKE '%,0' OR or2.fe\_group = '0' ) OR ( or2.fe\_group LIKE '%,-1,%' OR or2.fe\_group LIKE '-1,%' OR or2.fe\_group LIKE '%,-1' OR or2.fe\_group = '-1' ) ) ) WHERE or.deleted =0 AND or.hidden =0 AND ( or.fe\_group = '' OR or.fe\_group IS NULL OR or.fe\_group = '0' OR ( or.fe\_group LIKE '%,0,%' OR or.fe\_group LIKE '0,%' OR or.fe\_group LIKE '%,0' OR or.fe\_group = '0' ) OR ( or.fe\_group LIKE '%,-1,%' OR or.fe\_group LIKE '-1,%' OR or.fe\_group LIKE '%,-1' OR or.fe\_group = '-1' ) ) AND or.pid IN ( 1094, 1153, 1146, 1152, 1151, 1150, 1149, 1148, 1147, 1140, 1145, 1144, 1143, 1142, 1141, 1129,1139, 1138, 1137, 1136, 1135, 1134, 1133, 1132, 1131, 1130, 1120, 1128, 1127, 1126, 1125, 1124,1123, 1122, 1121, 1113, 1119, 1118, 1117, 1116, 1115, 1114, 1104, 1112, 1111, 1110, 1109, 1108,1107, 1106, 1105, 1100, 1103, 1102, 1101, 1096, 1099, 1098, 1097, 1095, 1091, 1093, 1092, 1090,1089, 1082, 1088, 1087, 1086, 1085, 1083, 1084, 1081, 1078, 1080, 1079, 1077 ) LIMIT 0, 30 .. _orderBy: orderBy ------- orderBy is different from the SQL orderBy statement. If you are using orderBy in your TypoScript, your statement won't be used in the SQL query, but will be used for ordering records with the php method array\_multisort. This has several advantages. A better performance. The Browser needs one query for data only. It is possible to order children records too. See the example below: .. _Local-table-parent: Local table (parent) ^^^^^^^^^^^^^^^^^^^^ .. _TypoScript-snippet: TypoScript snippet """""""""""""""""" plugin.tx\_browser\_pi1.views.list { 1 { select = ... orderBy (tx\_organiser\_cal\_mm\_calentrance.sorting,tx\_organiser\_ca lentrance.title) } } .. _Comment: Comment """"""" We have this relation in the example above (you can't see it): tx\_organiser\_cal -> tx\_organiser\_cal\_mm\_calentrance -> tx\_organiser\_calentrance It is like: event -> mm table -> entrance fees The orderBy clause from above will have the effect, that the titles of the entrance fees (values like: "students 10 EUR, adults 16 EUR, ..." will first ordered by the values of the field sorting in the mm table and second will be ordered by the title. The browser detects a context between mm tables and foreign tables! And the orderBy clause from above will have any effect to all other children tables. .. _Reference: Reference """"""""" See the reference in the Browser manual. See views.list order By and views.single orderBy. .. _Foreign-table-children: Foreign table (children) ^^^^^^^^^^^^^^^^^^^^^^^^ .. _Screen-shots: Screen shots """""""""""" |img-6| |img-6| Illustration above: - The local table – the parent – is tx\_org\_headquarters. Values (titles) from this table are - Standort Schnellerstraße - Standort Belforter Straße - The foreign table – the children – is tx\_org\_departments. The titles of the departments are ordered manually in the backend (see illustration above). If you like to get the same order of the children in the frontend, you need a SQL query. See the TypoScript below. .. _TypoScript-snippet: TypoScript snippet """""""""""""""""" plugin.tx\_browser\_pi1 { views { list { 501 = Headquarters 501 { name = Standorte showUid = headquartersUid select ( tx\_org\_headquarters.title, tx\_org\_headquarters.mail\_postcode, tx\_org\_headquarters.mail\_city, tx\_org\_headquarters.mail\_address, tx\_org\_department.title, tx\_org\_headquarters.image, tx\_org\_headquarters.imageseo ) orderBy = tx\_org\_headquarters.sorting orderBy { tx\_org\_department ( SELECT tx\_org\_department.uid AS 'uid', tx\_org\_department.title AS 'title' FROM tx\_org\_department, tx\_org\_headquarters\_mm\_tx\_org\_department WHERE tx\_org\_headquarters\_mm\_tx\_org\_department.uid\_local =###UID\_LOCAL### AND tx\_org\_headquarters\_mm\_tx\_org\_department.uid\_foreign =tx\_org\_department.uid ORDER BY tx\_org\_headquarters\_mm\_tx\_org\_department.sorting ) } csvLinkToSingleView = tx\_org\_headquarters.title } } } } .. _Comment: Comment """"""" You control the ordering with the TypoScript property orderBy."foreignTable". foreignTable is the name of the foreign table like tx\_org\_department. See from line 18 to line 27. Be aware: - orderBy."foreignTable" is real SQL. It will be send as a SQL query directly – in contrast to orderBy for the local table above. - Because of a restriction of the TYPO3 API it isn't possible to create complex SQL queries: You are allowed to use one table in the SELECT only! .. _Reference: Reference """"""""" See the reference in the Browser manual. See views.list orderBy."foreignTable" and views.single orderBy."foreignTable". .. _Reference: Reference --------- The Browser has it's own manual with the reference of course. - HTML `http://typo3.org/documentation/document-library/extension- manuals/browser/current/ `_ - PDF: `http://typo3.org/extensions/repository/view/browser/current/info /?tx\_terfe\_pi1%5BdownloadFile%5D=doc%252Fmanual.pdf `_ .. _What-s-new-in-Version-3-6: What's new in Version 3.6? -------------------------- .. _Simple-relations-Configurable-foreign-table-field: Simple relations: Configurable foreign table field ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ See section "Simple Relation with configured Relation Field" on page 16 above. .. _Helpful-suggestions: Helpful suggestions ------------------- .. _Forum: Forum ^^^^^ |img-8| Illustration 24: The TYPO3-Frontend-Engine Browser Forum The Browser has its own forum. We speak English and German. You are welcome to post any question, bug or snippet code at TYPO3-Frontend-Engine Browser: Forum `http://typo3-browser-forum.de/ `_ .. _Credits: Credits ------- .. _netresearch: netresearch ^^^^^^^^^^^ Thanks to Christian WeiskeNetresearch GmbH & Co.KG `http://www.netresearch.de/ `_ who had developed the feature "Simple relations: Configurable foreign table field" .. _To-Do-List: To-Do List ---------- Nothing to do. .. _Change-Log: Change Log ---------- 3.6.3 **New Feature** \* 13803: Ordering children records 3.6.0 **New Feature** \* 11650: Simple relations: Configurable foreign table field. Thanks to cweiske 3.4.3 **New Feature** \* 9727: Order children records in a single view by mm sorting 3.0.3 **Bugfixing\*** Examples in Comments for single-relation and mm-relation had wrong syntax. 3.0.2 **Maintenance\*** No extension dependency, no extension suggestion 3.0.1 **Publishing** .. _Illustration-Index: Illustration Index ------------------ Illustration 1: Tutorial in PDF format 3 Illustration 2: Relation between be\_users and be\_groups 4 Illustration 3: Relation between tt\_news and tt\_news\_cat 4 Illustration 4: be\_users and be\_groups has a simple relation 5 Illustration 5: tt\_news and tt\_news\_cat has a MM relation 6 Illustration 6: Any relation needs one record. 6 Illustration 7: Table Relations in the plugin. 8 Illustration 8: Startingpoint with a link to the sysfolder fe\_user 8 Illustration 9: Frontend users with groups 9 Illustration 10: Display records without any pid 9 Illustration 11: Backend users with groups 10 Illustration 12: Startingpoint with a link to the sysfolder news 11 Illustration 13: Backend users with groups 11 Illustration 14: Table Relations in the plugin. 12 Illustration 15: The TypoScript array for auto relation building. 13 Illustration 16: The messages for auto relation building in the DRS. 13 Illustration 17: Only TypoScript 14 Illustration 18: Only TypoScript 17 Illustration 19: Table tx\_civserv\_service with name and three synonyms 19 Illustration 20: Synonyms in the Frontend - every synonym will be a result. 20 Illustration 21: Example for datas in hierarchical order 21 Illustration 22: Frontend with ordered children records 25 Illustration 23: Backend with ordered children records 25 Illustration 24: The TYPO3-Frontend-Engine Browser Forum 29 `0 <#sdfootnote1anc>`_ The extension "Organiser: Events & Stuff & News & More" (extKey: organiser) is under development. We scheduled the publishing in the TYPO3 repository for February 2011. `0 <#sdfootnote2anc>`_ "Virtual Civil Services" is called "O.S.I.R.I.S" too. 33 .. ######CUTTER_MARK_IMAGES###### .. |img-1| image:: img-1.png .. :align: left .. |img-2| image:: img-2.png .. :border: 0 .. :height: 27 .. :hspace: 4 .. :id: Grafik2 .. :name: Grafik2 .. :width: 98 .. |img-3| image:: img-3.png .. :align: bottom .. :border: 0 .. :height: 27 .. :id: graphics25 .. :name: graphics25 .. :width: 30 .. |img-4| image:: img-4.png .. :align: bottom .. :border: 0 .. :height: 268 .. :id: graphics5 .. :name: graphics5 .. :width: 302 .. |img-5| image:: img-5.jpeg .. :align: left .. :border: 0 .. :height: 205 .. :id: graphics1 .. :name: graphics1 .. :width: 227 .. |img-6| image:: data: :alt: Frame4 .. :align: bottom .. :id: Frame4 .. :name: Frame4 .. |img-7| image:: img-7.jpeg .. :align: left .. :border: 0 .. :height: 285 .. :id: graphics11 .. :name: graphics11 .. :width: 454 .. |img-8| image:: img-8.jpeg .. :align: left .. :border: 0 .. :height: 161 .. :id: graphics81 .. :name: graphics81 .. :width: 302