Left Join with Where clause returns empty result

Question

I have got a post table and it's schema is like this:

CREATE TABLE IF NOT EXISTS `post` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) DEFAULT NULL,
  `site_id` bigint(20) DEFAULT NULL,
  `parent_id` bigint(20) DEFAULT NULL,
  `title` longtext COLLATE utf8_turkish_ci NOT NULL,
  `status` varchar(20) COLLATE utf8_turkish_ci NOT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_5A8A6C8DA76ED395` (`user_id`),
  KEY `IDX_5A8A6C8DF6BD1646` (`site_id`),
  KEY `IDX_5A8A6C8D727ACA70` (`parent_id`),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci AUTO_INCREMENT=16620 ;

I'm using this DQL to fetch a post and it's children:

$post = $this->_diContainer->wordy_app_doctrine->fetch(
           "SELECT p,c FROM Wordy\Entity\Post p LEFT JOIN p.children c WHERE p.site = :site AND p.id = :id AND p.language = :language AND p.status != 'trashed'  AND c.status != 'trashed' ORDER BY c.title",
           array(
               'params' => array(
               'id' => $id,
               'site' => $this->_currentSite['id'],
               'language' => $this->_currentLanguage->code,
           )
       )
   );

What i'm trying to do is: Fetch a post and all of it's children. The criteria is, don't include trashed posts or trashed children.

But when i run this query with a post which doesn't even have children, the returned result set is empty.

When i remove the c.status != 'trashed' part from query, everything works fine but i will get trashed posts too.

Thanks, in advance.

edit: here is the SQL output of given DQL:

SELECT p0_.id AS id0, p0_.title AS title5, p0_.status AS status8, p0_.parent_id AS parent_id9, p1_.id AS id15, p1_.title AS title20, p1_.status AS status23, p1_.parent_id AS parent_id24 FROM post p0_ LEFT JOIN post p1_ ON p0_.id = p1_.parent_id WHERE p0_.site_id = ? AND p0_.id = ? AND p0_.language = ? AND p0_.status <> 'trashed' ORDER BY p1_.title ASC

Answer 1

I think i solved my own problem.

Just use a with clause on join field instead of where clause, like this:

"SELECT p,c FROM Wordy\Entity\Post p LEFT JOIN p.children c WITH c.status != 'trashed' WHERE p.site = :site........."