Propel equivalent of "exists"

I am new to Propel and have been reading the documentation. But, I have not found a clear equivalent to the EXISTS and NOT EXISTS constructs from SQL. Linq in .NET, for instance, has Any(). Is there an equivalent to the following in "idiomatic" Propel?

SELECT a.colu开发者_开发百科mn1, a column2, a.etc
FROM   TableA a
WHERE  NOT EXISTS (SELECT 1
                   FROM   TableB b
                   WHERE  b.someIdColumn = a.someIdColumn
                   AND    b.aNullableDateColumn IS NULL)

After doing some more digging, I believe I have an answer to my question, or at least as good an answer as is currently available.

What comes after EXISTS or NOT EXISTS is a subquery. While that fact seems obvious, it did not originally occur to me to focus my search for help on subqueries. I found a few resources on the topic. Essentially, the options are to rewrite the query using JOINs (as is the heart of the answer by @Kaltas) or to use Criteria::CUSTOM. I decided I would likely prefer the second option, since it allows me to keep the subquery, potentially helping my database performance.

I did a lot of reading, then, about Criteria::CUSTOM, but the only reading that really helped me was reading the Propel 1.5 source. It's very simple, really. Just put the subquery, verbatim (using the database's table and column names, not Propel's object names) along with EXISTS or NOT EXISTS in the where call, like:

TableAQuery::create()
    ->where('NOT EXISTS (SELECT 1 FROM TableB WHERE TableA.someIdColumn = TableB.someIdColumn AND TableB.aNullableDateColumn IS NULL)')
    ->find();

It's that simple. Internally, the where method goes through a few possibilities for interpreting the clause, and finding no matches, it treats the clause as being of Criteria::CUSTOM and inserts it into the SQL query as-is. So, I could not use table aliases, for example.

If I ever have time, maybe I'll work on a more "ORM-ish" way to do this and submit a patch. Someone will probably beat me to it, though.

As in propel 1.6 u now can use Criteria::IN and Criteria::NOT_IN

Example : Select all users that are not in an UserGroup

$users = UserQuery::create()->filterById(UserPerUserGroupQuery::create()->select('user_id')->find(), CRITERIA::NOT_IN)
                             ->orderByUserName()
                             ->find();

I think you could rewrite the query as:

SELECT
  a.column1,
  a.column2,
  a.etc
FROM
  TableA a
WHERE
  (SELECT
    COUNT(*)
  FROM
    TableB b
  WHERE
    b.someIdColumn = a.someIdColumn
      AND
    b.aNullableDateColumn IS NULL
  ) > 0

which is easily doable in Propel.

Or even cleaner and easier to accomplish in Propel:

SELECT
  a.column1,
  a.column2,
  a.etc
FROM
  TableA a
    LEFT JOIN
  TableB b ON (b.someIdColumn = a.someIdColumn)
WHERE
  b.aNullableDateColumn IS NULL
    AND
  b.primaryKeyColumn IS NOT NULL

Propel 2 can do:

TableAQuery::create()
  ->useTableBNotExistsQuery()
  ->filterByNullableDateColumn(null)
  ->endUse()
  ->find();

or

$nestedB = TableBQuery::create()
  ->filterByNullableDateColumn(null)
  ->where('TableB.someIdColumn = TableA.someIdColumn');
TableAQuery::create()->whereExists(nestedB)->find();