CODEWITHSUNDEEP
phplaravelquery-builder
user14293451
user14293451

Reputation:

Laravel date comparasion not working for created_at column

I've written a query for checking the messages that are written to conversation after it's deleted by a user that's in conversation. Here is my query:

$messages = DB::table('conversations')
                    ->where('conversations.id', $conversation->id)
                    ->join('messages', 'messages.conversation_id', '=', 'conversations.id')
                    ->join('deleted_conversations', 'deleted_conversations.conversation_id', '=', 'conversations.id')
                    ->where('messages.created_at', '>=', 'deleted_conversations.created_at')
                    ->orderBy('messages.created_at', 'desc')
                    ->select('messages.*')
                    ->get()
                    ->all();

But everytime i try to run this query with where('messages.created_at, '<', 'deleted_conversations_created_at statement if doesn't check for dates precisely. Why?

Here is migrations:

Schema::create('conversations', function (Blueprint $table) {
            $table->id();
            $table->integer('sender_id');
            $table->integer('receiver_id');
            $table->enum('status', ['read', 'unread']);
            $table->timestamps();
        });

 Schema::create('messages', function (Blueprint $table) {
            $table->id();
            $table->integer('conversation_id');
            $table->integer('sender_id');
            $table->string('message');
            $table->timestamps();
        });

Schema::create('deleted_conversations', function (Blueprint $table) {
            $table->id();
            $table->integer('conversation_id');
            $table->integer('user_id');
            $table->timestamps();
        });

deleted_conversations table

id conversation_id user_id created_at updated_at
1 1 2 2021-02-28 11:26:36 2021-02-28 11:30:45

conversations table

id sender_id user_id receiver_id status created_at updated_at
1 1 1 2 unread 2021-02-28 11:07:09 2021-02-28 11:31:50

messages table

id conversation_id sender_id message created_at updated_at
1 1 1 S.A 2021-02-28 00:00:00 2021-02-28 00:00:00
2 1 2 A.S 2021-02-28 00:00:00 2021-02-28 00:00:00

Upvotes: 0

Views: 385

Answers (1)

Rwd
Rwd

Reputation: 35190

When you want to add a where clause where the value is another column, you need to use whereColumn():

->whereColumn('messages.created_at', '>=', 'deleted_conversations.created_at')

Upvotes: 2

Related Questions