[TYPO3-english] QueryResultInterface for latest tt_content images

[Mark Boland]

Hi Sergio,

a quick shot at a query that should do the trick:

SELECT sys_file.identifier
FROM tt_content 
  JOIN sys_file_reference ON sys_file_reference.tablenames = "tt_content³
    AND sys_file_reference.uid_foreign = tt_content.uid
  JOIN sys_file ON sys_file_reference.uid_local = sys_file.uid
WHERE Ctype in ("image","textpic³)
  AND tt_content.pid IN (2,3,4,5)
  AND tt_content.sys_language_pid = 0
GROUP BY tt_content.pid
ORDER BY tt_content.crdate DESC;



This is for FAL. tt_content.image doesn¹t contain the file names anymore
since TYPO3 4.5. If you are using 4.5 you can remove the two JOINs and
return tt_content.image instead.

Cheers
Mark

Am 14.07.14 10:43 schrieb "Sergio Catalá" unter <scatala.enet at gmail.com>:

>Hi Mark,
>
>Thanks for your answer.
>I need a GROUP BY and HAVING, because I want to get the first image for
>the latest created tt_content element (normally an Image element with
>several images) but from certain pids. And I don't want to display two
>latest tt_content elements from the same pid, just the latest tt_content
>of every pid.
>
>Regards,
>Sergio
>
>
>On 13/07/14 16:42, Mark Boland wrote:
>> Hi Sergio
>>
>> Why using GROUP BY and HAVING? WHERE should be enough and don't forget
>>to use deleted = 0 AND hidden = 0 to exclude hidden or deleted records.
>>
>> You could achieve this with a pure Typoscript .select in a CONTENT
>>element.
>>
>> Cheers
>> Mark
>>
>>> Am 13.07.2014 um 14:33 schrieb Sergio Catalá <scatala.enet at gmail.com>:
>>>
>>> Hi list,
>>>
>>> I've built a new extension that maps tt_content table. I would like to
>>>get the images from the latest created tt_content elements.
>>>
>>> I would like to transform this SQL sentence:
>>>
>>>     SELECT tt_content.image
>>>     FROM (SELECT * FROM tt_content ORDER BY tt_content.crdate DESC)
>>>tt_content
>>>     GROUP BY tt_content.pid, tt_content.sys_language_uid
>>>     HAVING tt_content.pid in (2,3,4,5) AND tt_content.sys_language_uid
>>>= 0
>>>     LIMIT 0,4
>>>
>>> into a QueryResultInterface for my content repository.
>>>
>>> But I am not sure if I can use GROUP and HAVING clausules with
>>>existing functions for the $query.
>>> I saw I can use a "raw sql" through statement() function, but I don't
>>>want to use that.
>>>
>>> Is this possible?
>>>
>>> Thanks in advance,
>>> Sergio
>>>
>>> _______________________________________________
>>> TYPO3-english mailing list
>>> TYPO3-english at lists.typo3.org
>>> http://lists.typo3.org/cgi-bin/mailman/listinfo/typo3-english
>> _______________________________________________
>> TYPO3-english mailing list
>> TYPO3-english at lists.typo3.org
>> http://lists.typo3.org/cgi-bin/mailman/listinfo/typo3-english
>
>_______________________________________________
>TYPO3-english mailing list
>TYPO3-english at lists.typo3.org
>http://lists.typo3.org/cgi-bin/mailman/listinfo/typo3-english