public function scopeForUserWithNewMessages(Builder $query, $userId)
    {
        $participantTable = Models::table('participants');
        $threadsTable = Models::table('threads');

        return $query->join($participantTable, $this->getQualifiedKeyName(), '=', $participantTable . '.thread_id')
            ->where($participantTable . '.user_id', $userId)
            ->whereNull($participantTable . '.deleted_at')
            ->where(function (Builder $query) use ($participantTable, $threadsTable) {
                $query->where($threadsTable . '.updated_at', '>', $this->getConnection()->raw($this->getConnection()->getTablePrefix() . $participantTable . '.last_read'))
                    ->orWhereNull($participantTable . '.last_read');
            })
            ->select($threadsTable . '.*');
    }
public function scopeForUserWithNewMessages(Builder $query, $userId)
    {
        $participantTable = Models::table('participants');
        $threadsTable = Models::table('threads');

        return $query->join($participantTable, $this->getQualifiedKeyName(), '=', $participantTable . '.thread_id')
            ->where($participantTable . '.user_id', $userId)
            ->whereNull($participantTable . '.deleted_at')
            ->where(function (Builder $query) use ($participantTable, $threadsTable) {
                $query->where($threadsTable . '.updated_at', '>', $this->getConnection()->raw($this->getConnection()->getTablePrefix() . $participantTable . '.last_read'))
                    ->orWhereNull($participantTable . '.last_read');
            })
            ->select($threadsTable . '.*');
    }
public function scopeForUserWithNewMessages ( Builder $ query , $ userId ) { $ participantTable = Models :: table ( 'participants' ) ; $ threadsTable = Models :: table ( 'threads' ) ; return $ query -> join ( $ participantTable , $ this -> getQualifiedKeyName ( ) , '=' , $ participantTable . '.thread_id' ) -> where ( $ participantTable . '.user_id' , $ userId ) -> whereNull ( $ participantTable . '.deleted_at' ) -> where ( function ( Builder $ query ) use ( $ participantTable , $ threadsTable ) { $ query -> where ( $ threadsTable . '.updated_at' , '>' , $ this -> getConnection ( ) -> raw ( $ this -> getConnection ( ) -> getTablePrefix ( ) . $ participantTable . '.last_read' ) ) -> orWhereNull ( $ participantTable . '.last_read' ) ; } ) -> select ( $ threadsTable . '.*' ) ; }
Returns threads with new messages that the user is associated with.

@param \Illuminate\Database\Eloquent\Builder $query
@param int $userId

@return \Illuminate\Database\Eloquent\Builder
Returns threads with new messages that the user is associated with .