I have a normal extbase extension.
In the controller I get all my records:
$stuff = $this->jobRepository->findAll();
$this->view->assign('stuff', $stuff);
and in the template I display them:
<f:for each="{stuffs}" as="stuff">
{stuff.title} <br />
{stuff.category}
</f:for>
Now I need a new field stuff.isnew
with the value 1
if the record is the newest by category.
An SQL-Statement for that would look like:
SELECT j2.isnew, j.* FROM `tx_stuff_domain_model_stuff` as j
left join
(SELECT max(crdate) as crdate, category, 1 as isnew FROM `tx_stuff_domain_model_stuff` group by category) as j2
on j.crdate=j2.crdate and j.category=j2.category
(If I have to write my own Query I will have to check deleted, hidden, starttime, endtime to check if the record is active right now)
My question now is, what is the cleanest/best way to add that to my extension?