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.column1, 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.