TYPO3 Extbase Join

Question

I'm try to make this query with extbase work:

SELECT a.* 
  FROM tx_apartments_domain_model_apartment a 
  LEFT 
  JOIN tx_apartments_domain_model_booking b 
    ON b.apartment = a.uid 
   AND b.start <= '2018-07-23' 
   AND b.end >= '2018-07-21'
 WHERE b.uid IS NULL AND a.hidden=0 AND a.deleted=0;

the following code does not work, i get an empty result:

    /** @var QueryBuilder $queryBuilder */
    $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
        ->getQueryBuilderForTable('tx_apartments_domain_model_apartment');
    $statement = $queryBuilder
        ->select('a.*')
        ->from('tx_apartments_domain_model_apartment','a')
        ->leftJoin(
            'a',
            'tx_apartments_domain_model_booking',
            'b',
            $queryBuilder->expr()->andX(
                $queryBuilder->expr()->eq('b.apartment','a.uid'),
                $queryBuilder->expr()->lte('b.start', '2018-07-23'),
                $queryBuilder->expr()->gte('b.end', '2018-07-21')
            )
        )
        ->where(
            $queryBuilder->expr()->isNull('b.uid')
        )
        ->execute();
    //DebuggerUtility::var_dump($queryBuilder->getSQL());
    return $statement->fetchAll();

can anybody help me?

score 0 · Answer 1 · answered Jul 17 '18 at 06:10

0

What kind of records do you expect to get?

as uid is a mandantory field you never have records where the uid is NULL.

If you want to access new records which are not stored to the database yet: they get a faked uid like NEW123

answered Jul 17 '18 at 06:10

Bernd Wilke πφ

10,390
1
19
38

the records are typical typo3 data, nothing special. and the sql statemant is an answer from my question here: https://stackoverflow.com/questions/51366904/overlapping-booking-query and you are right, the uid is always a mandatory field. but the pure sql query in my question above works only correct with an "b.uid IS NULL". but now i think that this is exactly the problem for my extbase query – exotec Jul 17 '18 at 08:33

score 0 · Answer 2 · answered Jul 17 '18 at 09:27

This works for me:

        /** @var QueryBuilder $queryBuilder */
        $queryBuilder = GeneralUtility::makeInstance(ConnectionPool::class)
            ->getQueryBuilderForTable('tx_apartments_domain_model_apartment');

        $queryBuilder
            ->getRestrictions()
            ->removeAll();

        $statement = $queryBuilder
            ->select('a.*')
            ->from('tx_apartments_domain_model_apartment','a')
            ->leftJoin(
                'a',
                'tx_apartments_domain_model_booking',
                'b',
                $queryBuilder->expr()->andX(
                    $queryBuilder->expr()->eq('b.apartment','a.uid'),
                    $queryBuilder->expr()->lte('b.start', $queryBuilder->createNamedParameter('2018-07-23')),
                    $queryBuilder->expr()->gte('b.end', $queryBuilder->createNamedParameter('2018-07-21'))
                )
            )
            ->where(
                $queryBuilder->expr()->andX(
                    $queryBuilder->expr()->isNull('b.uid'),
                    $queryBuilder->expr()->eq('a.hidden',0),
                    $queryBuilder->expr()->eq('a.deleted',0)
                )
            )
            ->execute();
        $results = $statement->fetchAll();
//        DebuggerUtility::var_dump($queryBuilder->getSQL());
//        DebuggerUtility::var_dump($results);
        return $results;

score 0 · Answer 3 · answered Jul 11 '19 at 14:26

I do not know where exactly you are using this code. Typically I would think that retrieving records from the database should happen in a repository.

If you are using this in the ApartmentRepository and if there is a relation properly configured between apartments and bookings (1:n, I would assume) in the TCA, then you could use something like this:

public function findInDateRange ($dateConstraint) 
{
    $query = $this->createQuery();
    $constraints = [];
    $constraints[] = $query->lessThanOrEqual(
        'bookings.start',
        $dateConstraint['start']
    );
    $constraints[] = $query->greaterThanOrEqual(
        'bookings.end',
        $dateConstraint['end']
    );

    $query->matching($query->logicalAnd($constraints));
    return $query->execute();
}

I deliberately left out the b.uid IS NULL part here, as that should always return nothing since uid should be AUTO_INCREMENT. Unless the default query settings are changed, there is also no need to check the disable fields (hidden, deleted) manually.

Note that in order for this to work, the Apartment Domain Model needs a field bookings with a corresponding column in the database (that is something I stumbled over last year).

As a side-note: Is it intentional that you allow for the end to be before the start in your query (start: 2018-07-23, end: 2018-07-21), or is that just exemplary?

TYPO3 Extbase Join

3 Answers3