Extbase Queries : NOT IN query and Conditional sorting not there

When I was working with extbase extension development, I was wondered about the TYPO3 query builders.
In TYPO3, the query builder create a object and using that object we can get all required related values without any manual join query. But you will only have such object when you use TYPO3 extbase Queries. I found a great blog post on https://lbrmedia.net/codebase/Eintrag/extbase-query-methods/.

1. NOT IN query not there in extbase queries :

When I was working with that, I was in need of a NOT IN Query as we do in Mysql.
Like :

SELECT * FROM 'table1' WHERE  uid NOT IN (SELECT ID FROM table1 WHERE lang = 'FR');

You can use IN Query in TYPO3 like :

$query->in($myProperty, $isInThisObjectOrArray);

But there is no query for NOT IN in TYPO extbase Query. I was in such a need that I want to return values in object only, so stuck in that situation. At the end, I used manual query instead.

2. ORDER BY with specific conditions liks NULL and 0 values not possible in extbase query :
In  TYPO3 extbase queries, you can do sorting by extbase queries as per below :

$query->setOrderings(array("field" => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_ASCENDING));
$query->setOrderings(array("field" => \TYPO3\CMS\Extbase\Persistence\QueryInterface::ORDER_DESCENDING)

But in case of we need conditional sorting like :

SELECT * FROM my_table WHERE myattr=1 ORDER BY (CASE WHEN districts.id IS NULL then 1 ELSE 0 END),districts.name, schools.name;

Here, I want a conditional sorting in such case not extbase query helped and again, I need to do all using manual query.

Please comment below if you have better solution for the same. :)