title: Laravel : Comparing datetime between two table while selecting record
body: <p>I have Laravel app where i check the thread comment (thread_comment table) created date time and thread (thread table) last visit date.</p>
  
  <p>i will select if any thread comment created date time is > thread last visit date, i will query them out.
  The purpose of this is to notify the user how many new comments since their last visit to their thread.</p>
  
  <p>Below is my code.</p>
  
  <pre><code>$new_thread_comment_count = DB::table('ap_thread')
              -&gt;join('ap_thread_comment', 'ap_thread_comment.ThreadID', '=', 'ap_thread.ThreadID')
              -&gt;where('ap_thread.CreatedBy', Auth::user()-&gt;UserID)
              -&gt;where('ap_thread_comment.CreatedDateTime','&gt;','ap_thread.last_visit_date')
              -&gt;count();
  </code></pre>
  
  <p>My problem is, this doesn't work. If I change the operand to &lt;, it will like display all records, which is incorrect.
  Do i need to do any datetime convertion in where clause while comparing datetime between two table? </p>
  
  <pre><code>CREATE TABLE `ap_thread`
  (`ThreadID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ThreadTitle` text NOT NULL,
  `Remark` text NOT NULL,
  `CountryID` smallint(6) NOT NULL,
  `last_visit_date` datetime NOT NULL,
  `Thread_StatusID` tinyint(4) NOT NULL,
  `StatusID` tinyint(4) NOT NULL,
  `CreatedBy` bigint(20) NOT NULL,
  `CreatedDateTime` datetime NOT NULL,
  `EditedBy` bigint(20) NOT NULL,
  `EditedDateTime` datetime NOT NULL,
  `IsSelling` tinyint(1) NOT NULL,
  PRIMARY KEY (`ThreadID`)) 
  
  CREATE TABLE `ap_thread_comment` (
  `Thread_CommentID` bigint(20) NOT NULL AUTO_INCREMENT,
  `ThreadID` bigint(20) NOT NULL,
  `StatusID` tinyint(4) NOT NULL,
  `reply_to` bigint(20) unsigned NOT NULL,
  `CreatedBy` bigint(20) NOT NULL,
  `CreatedDateTime` datetime NOT NULL,
  `Comment` text NOT NULL,
  PRIMARY KEY (`Thread_CommentID`))
  </code></pre>
  
  <p>Sorry for the confusion. The thread table stores all the threads created by the users. Whereas thread_comment stores the comments that commented by other users.
  What i'm trying to do is to select the total new comments for every threads that created by the logged in user by comparing ap_thread_comment CreatedDateTime is > ap_thread last_visit_date.</p>
  
  <ul>
  <li>The last_visited_date will only be updated when the owner of the thread visited the thread.</li>
  </ul>