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

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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')


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x