TYPO3 FileReference repository query search through file name

Question

I have made a 'document' model which contains a field 'file' which is a FileReference. Now im working on a repository query function that retrieves all documents containing certain string in the files name ( using $query->like() for this ). However I run against the following error:

When ever I disable this $query->like and I debug the 'document' that I receive it looks like the relation information regarding the field 'uidLocal' is correct because I receive the name of the file.

Some more code im using:

class FileReference extends \TYPO3\CMS\Extbase\Domain\Model\FileReference

{

/**
 * @var \**\***\Domain\Model\File
 */
protected $uidLocal;

/**
 * @param \**\***\Domain\Model\File $uidLocal
 * @return void
 */
public function setUidLocal($uidLocal)
{
    $this->uidLocal = $uidLocal;
}

/**
 * @return \**\***\Domain\Model\File
 */
public function getUidLocal()
{
    return $this->uidLocal;
}

}

The repository query:

$query->matching(
        $query->logicalAnd(
            $query->greaterThanOrEqual('crdate', $from),
            $query->contains('usergroups', $participant),
            // TODO: Onderstaande check moet aan maar resulteerd in error..
            $query->like('file.uidLocal.name', '%'.$filename.'_'.$type.'.%')
        )
    );

Now I can ofcourse filter the document name after the query but that wont do well for the performance of the task. Does anyone know what im missing and where the error comes from?

Thanks in advance for contributing ideas,

Falko

Answer 1

I guess you are trying something like that?

$query = $this->createQuery();
$query->matching($query->like('file.uidLocal.name', '%somefile%'));

I am not sure if you can join from sys_file_reference to sys_file with extbase query. For me it looks like the TCA or the FileReference Model is not implemented that way.

Maybe a workaround is to create a custom sql query wich returns uids and then create your document models with something like ->findAllByUids($uids);

Here is a working example wich uses the query builder instead of extbase query.

class ArticleRepository extends Repository
{

    public function findAllByFilename($filename)
    {
        /* @var $queryBuilder \TYPO3\CMS\Core\Database\Query\QueryBuilder */
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
            ->getQueryBuilderForTable('tx_example_domain_model_article');

        $queryBuilder->setRestrictions(GeneralUtility::makeInstance(FrontendRestrictionContainer::class));
        $res = $queryBuilder
            ->select('article.uid')
            ->from('tx_example_domain_model_article', 'article')
            ->leftJoin('article', 'sys_file_reference', 'reference',
                $queryBuilder->expr()->andX(
                    $queryBuilder->expr()->eq('reference.uid_foreign', $queryBuilder->quoteIdentifier('article.uid')),
                    $queryBuilder->expr()->eq('reference.tablenames', $queryBuilder->quote('tx_example_domain_model_article')),
                    $queryBuilder->expr()->eq('reference.fieldname', $queryBuilder->quote('image')),
                    $queryBuilder->expr()->eq('reference.table_local', $queryBuilder->quote('sys_file'))
                ))
            ->leftJoin('article', 'sys_file', 'file',
                $queryBuilder->expr()->eq('file.uid', $queryBuilder->quoteIdentifier('reference.uid_local'))
                )
            ->where($queryBuilder->expr()->eq('file.name', $queryBuilder->createNamedParameter($filename)))
            ->execute();


        debug($queryBuilder->getSQL());

        $uids = [];
        while ($row = $res->fetch()) {
            $uids[] = $row['uid'];
        }

        return $this->findAllByUids($uids);
    }

    public function findAllByUids($uids) {
        $query = $this->createQuery();
        $query->matching($query->in('uid', $uids));
        return $query->execute();
    }