You can do it like this in your repository class, please note the comments inside the code:
class CompanyRepository extends \TYPO3\CMS\Extbase\Persistence\Repository
{
public function findWithAvailablePlaces(bool $returnRawQueryResult = false)
{
// Create a QueryBuilder instance
$queryBuilder = $this->objectManager->get(\TYPO3\CMS\Core\Database\ConnectionPool::class)
->getConnectionForTable('company')->createQueryBuilder();
// Create the query
$queryBuilder
->select('*')
->from('company')
->where(
// Note: this string concatenation is needed, because TYPO3's
// QueryBuilder always escapes the value in the ExpressionBuilder's
// methods (eq(), lt(), gt(), ...) and thus render it impossible to
// compare against an identifier.
$queryBuilder->quoteIdentifier('number_places')
. \TYPO3\CMS\Core\Database\Query\Expression\ExpressionBuilder::GT
. $queryBuilder->quoteIdentifier('occupied_places')
);
// Execute the query
$result = $queryBuilder->execute()->fetchAll();
// Note: this switch is not needed in fact. I just put it here, if you
// like to get the Company model objects instead of an array.
if ($returnRawQueryResult) {
$dataMapper = $this->objectManager->get(\TYPO3\CMS\Extbase\Persistence\Generic\Mapper\DataMapper::class);
return $dataMapper->map($this->objectType, $result);
}
return $result;
}
}
Notes:
If you have lots of records to deal with, I would - for performance reasons - not use the data mapping feature and work with arrays.
If you want to use the fluid pagination widget, be sure you don't and build your own pagination. Because of the way this works (extbase-internally), you'd get a huge system load overhead when the table grows. Better add the support for limited db queries to the repository method, for example:
class CompanyRepository extends \TYPO3\CMS\Extbase\Persistence\Repository
{
public function findWithAvailablePlaces(
int $limit = 10,
int $offset = 0,
bool $returnRawQueryResult = false
) {
// ...
$queryBuilder
->setMaxResults($limit)
->setFirstResult($offset);
$result = $queryBuilder->execute()->fetchAll();
// ...
}
}