Left Join with Where clause returns an empty result

Question

I have a table of post , and this scheme is as follows:

 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 use this DQL to get the message and its 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: Receive a message and all his children. The criteria are: do not include colorful messages or broken children.

But when I run this query with a message that doesn't even have children, the returned result set is empty.

When I c.status != 'trashed' from the request, everything works fine, but I also receive tagged messages.

Thanks in advance.

edit: here is the SQL result of this 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 problem.

Just use the with clause in the join field instead of the where clause, for example:

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

Answer 2

Your long conditional WHERE TO GATHER together after the connection, and nothing passes all the checks. Move the join material to the ON clause and leave the c.trashed check in the WHERE clause. Try something like this:

 SELECT p,c FROM Wordy\Entity\Post p LEFT JOIN children c ON p.site = :site AND p.id = :id AND p.language = :language AND p.status != 'trashed' AND p.id = c.parent_id WHERE c.status != 'trashed' ORDER BY c.title 

Answer 3

You forgot the ON clause of the left join syntax .