Tag: Tesseract

Store a picture in FAL, and retrieve file name using standard sql statement

Scenario: store a picture in FAL, and retrieve file name using standard sql statement
A) TCA declaration is as follows:
‘news_picture’ => array(
‘exclude’ => 0,
‘label’ => ‘LLL:EXT:mynews/Resources/Private/Language/Database.xlf:tx_mynews.news_picture’,
‘config’ => \TYPO3\CMS\Core\Utility\ExtensionManagementUtility::getFileFieldTCAConfig(
‘news_picture’,
array(
‘appearance’ => array(
‘collapseAll’ => TRUE
),
‘maxitems’ => 1,
)
)
),
B) An SQL example:
SELECT * FROM (
SELECT 
tx_mynews.uid as news_uid,
tx_mynews.news_title,
tx_mynews.news_subtitle,
`sys_file`.name AS filename,
`sys_file`.uid AS fileuid,
`sys_file`.identifier
FROM `tx_mynews` 
LEFT JOIN `sys_file_reference` ON 
`tx_mynews`.`uid` = `sys_file_reference`.`uid_foreign`
AND 
 `sys_file_reference`.`tablenames` = ‘tx_mynews’ 
 AND 
 `sys_file_reference`.`fieldname` = ‘news_picture’
 AND 
 `sys_file_reference`.`table_local` = ‘sys_file’ AND
 `sys_file_reference`.`deleted` = 0
LEFT JOIN `sys_file` 
ON
`sys_file_reference`.`uid_local` = `sys_file`.`uid`)
AS docs where docs.filename <> ”
ORDER BY news_title
LIMIT 100

Running a SQL Query using TypoScript

Well, after using this feature for quite long time, I though I should post this info:Consider the following Query:

SELECT DISTINCT FROM_UNIXTIME(`datetime`, ‘%Y’) AS `year` FROM tt_news where pid=113 order by datetime limit 1

My main goal was to get the latest year from the tt_news records, that is then going to be pushed to Tesseract filter.

Soln:

20 = CONTENT
20 {
table = tt_news
select {
selectFields = FROM_UNIXTIME(`datetime`, ‘%Y’) AS year
orderBy = datetime desc
pidInList = 113
max = 1
}
renderObj = TEXT
renderObj.field = year
}

Reference: http://typo3.org/documentation/document-library/core-documentation/doc_core_tsref/4.5.0/view/1/5/#id2621093

A simple note on using “Fluid Display”

Tesseract is a powerful tool, combined with “Fluid Display” the possibilities are almost limitless.

Here is a simple note on how to use it to group based on a specific field.

The Query:

SELECT
  doc_cat.title AS mydocs.title,
mydocs.name,
mydocs.path,
mydocs.file_path

FROM mydocs
LEFT JOIN doc_cat_mm
ON …
LEFT JOIN
doc_cat
ON …

The Fluid Display:


<f:if condition="{datastructure.count} == 0">
        <f:else>
            <f:groupedFor
                    each="{datastructure.mydocs.records}"
                    as="docCategories"
                    groupBy="title"
                    groupKey="title">
                    {title_cat}
                        <f:for each="{docCategories}" as="document">
                            {document.title}

An example for using TypoScript inside
                           <f:cObject typoscriptObjectPath="lib.docFileSize" data="{document.file_size}" />
                        </f:for>
            </f:groupedFor>
        </f:else>
    </f:if>




Now TyposCript will be something as below: (NOTE: make sure you use .current = 1 )

lib.docFileSize = TEXT
lib.docFileSize.current = 1
lib.docFileSize.bytes = 1
lib.docFileSize.bytes.labels = |kb|Mb|Gb

That should be it!

Some points of interest:
 <f:debug>{datastructure}</f:debug>
<f:if condition=”{0: datastructure.count} == {0: ‘0’}”>

IF this didn’t work, try
 <f:if condition=”{0: datastructure.tablename.count} == {0: ‘0’}”>

URLs of interest:
http://www.t3node.com/blog/combining-fluid-viewhelpers-and-typoscript-in-typo3-5-basic-examples/
http://wiki.typo3.org/Fluid

Teeseract and JOIN tables using a CSV field

Latest News allows us to use content elements within a News. This opens up a vast set of possibilities.

However, when we look in depth, the news manages related content element IDs in a CSV format.

Now this seems like a bad way to mange it, as we do not have a mm_table, and there is no easy way to handle this, than using DB specific functions.

Luckily Tesseract supports most of the MYSQL functions, and thus we can make use of the FIND_IN_SET function here.

ex:
select tt_content.uid,tt_content.header, news.content_elements
FROM tx_news_domain_model_news AS news
LEFT JOIN tt_content
ON find_in_set(tt_content.uid, news.content_elements) WHERE news.uid=1

Hope this helps you.

Tesseract Tips / Notes

DATA FILTER:

Clear Filter/Query Cache: In some situations when “Additional SQL” field is cleared, the dataquery may still be using the cached value from this field.
In such case we should add following URL Parameter, which informs DataQuery to re-build the query from scratch.

EX:
www.example.com/?clear_cache = 1

Using main.subtable: When we wish to apply a specific condition to a sub-table, the query is applied on the JOIN itself:

EX 1:
subtable.uid = vars:event
maintable.uid = varsproject

This is translated to someting like this:

SELECT maintable.*, subtable.*
FROM maintable JOIN subtable
ON maintable.event_id = subtable.uid AND subtable.uid = 20
WHERE
maintable.uid = 45

However, if we add main.subtable.uid… the query gets converted to as shown below:
EX 2:
main.subtable.uid = vars:event
maintable.uid = varsproject

This is translated to someting like this:

SELECT maintable.*, subtable.*
FROM maintable JOIN subtable
ON maintable.event_id = subtable.uid
WHERE
subtable.uid = 20 AND maintable.uid = 45