Trouble with Doctrine DQL Sorting on Related Entity Field

Question

I'm facing an issue with sorting in Doctrine's DQL (Doctrine Query Language) involving related entities, and I could use some guidance.

I have two entities, "Offer" and "OfferTrackingPublisher," with a one-to-many relationship. "OfferTrackingPublisher" logs changes related to "Offer" entities and has fields like "createdAt" and "status."

My goal is to sort "Offer" entities based on the "createdAt" field of related "OfferTrackingPublisher" records, but only when the "status" is 'add' or 'republish.' In SQL, I've successfully achieved this using a subquery:

ORDER BY (
    SELECT MAX(otp.created_at)
    FROM offer_tracking_publisher otp
    WHERE otp.offer_id = o0_.id
    AND otp.status IN ('add', 'republish')
) DESC

However, when I try to translate this into Doctrine's DQL, I encounter issues. Here's the DQL code I've attempted:

$subquery = $this->_em->createQueryBuilder()
    ->select('MAX(otp.createdAt)')
    ->from('App\Entity\OfferTrackingPublisher', 'otp')
    ->where('otp.offer = o.id')
    ->andWhere("otp.status IN ('add', 'republish')");

$qb->addOrderBy(
    '(' . $subquery->getDQL() . ')',
    'DESC'
);

Error :

[Syntax Error] line 0, col 412: Error: Expected Doctrine\ORM\Query\Lexer::T_IDENTIFIER, got '('

Unfortunately, this DQL code throws an error, and I'm unsure how to resolve it. I've tried various approaches, but none seem to work. Any assistance or suggestions on how to correctly achieve this please?