Ticket #1529 (closed defect: fixed)

Opened 9 months ago

Last modified 9 months ago

SoftDeletable Template problem with left join relations

Reported by: MrOxiMoron Owned by: jwage
Priority: minor Milestone: 1.0.4
Component: Behaviors Version: 1.0.2
Severity: Keywords: SoftDeletable
Cc: Has Test: no
Status: Pending Core Response Has Patch: yes

Description

I have a Todo and Comments table. a Todo item has many comments. They are both softdeletable. (and use_dql_callbacks set to true)

$todos = Doctrine_Query::create()->from('Todo t')->leftJoin('t.Comments c')->execute();

returns no todo items that have 0 comments.

The query: SELECT t.id AS tid, t.component AS tcomponent, t.user AS tuser, t.title AS ttitle, t.description AS tdescription, t.created_at AS tcreated_at, t.updated_at AS tupdated_at, t.deleted AS tdeleted, c.id AS cid, c.message AS cmessage, c.todo_id AS ctodo_id, c.created_at AS ccreated_at, c.updated_at AS cupdated_at, c.deleted AS cdeleted FROM todo t LEFT JOIN comment c ON t.id = c.todo_id WHERE t.deleted = 0 AND c.deleted = 0

The problem is that for the leftJoin the c.deleted is NULL so it isn't 0 or 1, it simply isn't set.

Change History

Changed 9 months ago by jwage

  • milestone set to 1.0.4

Changed 9 months ago by tuct

my patch to fix this issue: 

Index: SoftDelete.php
===================================================================
--- SoftDelete.php	(revision 5037)
+++ SoftDelete.php	(working copy)
@@ -92,7 +92,7 @@
         if ( ! $query->contains($field)) {
             $query->from('')->update($params['component']['table']->getOption('name') . ' ' . $params['alias']);
             $query->set($field, '?', array(true));
-            $query->addWhere($field . ' = ?', array(false));
+            $query->addWhere($field . ' = ? OR '.$field.' IS NULL', array(false));
         }
     }
 
@@ -109,7 +109,7 @@
         $field = $params['alias'] . '.' . $this->_options['name'];
         $query = $event->getQuery();
         if ( ! $query->contains($field)) {
-            $query->addWhere($field . ' = ?', array(false));
+            $query->addWhere($field . ' = ? OR '.$field.' IS NULL', array(false));
         }
     }
 }
 No newline at end of file

Changed 9 months ago by tuct

  • has_patch set

Changed 9 months ago by MrOxiMoron

Don't use this patch, instead use the patch I added in #1157.

It circumvents the whole problem by using a nullable timestamp field.

Changed 9 months ago by jwage

  • status changed from new to closed
  • resolution set to fixed

(In [5089]) [1.0] fixes #1529 - issue with SoftDelete? where conditions. Added OR IS NULL to solve problem.

Changed 9 months ago by tuct

thx using the patch! now my last "custom patch" from the doctrine core is gone :)

Changed 9 months ago by jwage

What?

Note: See TracTickets for help on using tickets.