Complex whereHas query

[royal zenith]

Hi
I'm working on a messaging component. We have conversations, a model that holds messages between multiple participants. We actually only have 2 participants for now. The database diagram looks like the picture. Thus I have an Account model (which is what we're used to have as User), a Conversation model and a Message model. We can see that account_conversation is a pivot table so that I have a many-to-many relationships between accounts and conversations.
What I struggle to accomplish is to select conversations where deleted_at is either null or where the last conversation message is more recent that the conversation.deleted_at.
I wrote a test for it: https://gist.github.com/AlexandreGerault/80eeaa5e80b2c3b366d9ff8b34eb0855
I can't find a way to exclude successfully. I either have all conversations, either none. The query I wrote is also on the gist, if any of you have an idea 😛

[icy portal]

Conversation::query()
    ->with('participants')
    ->with('lastMessage')
    ->whereHas('participants' function ($q) {
        $q->whereNull('deleted_at'); 
    })
    ->orWhereHas('lastMessage.conversation.participants', function ($q) {
        $q->where('deleted_at', '<', 'lastMessage.created_at');
    })
    ->get();

Not sure but maybe something like this?

[icy portal]

Conversation::query()
    ->with('participants')
    ->whereHas('participants' function ($q) {
        $q->whereNull('deleted_at'); 
    })
    ->orWhereHas('participants.messages' function ($q) {
        // Sort by latest
        
        // Conditional check on message time
    })
    ->get();

Or completely from the participants

[royal zenith]

I tried the first one

->whereHas('participants', function (Builder|BelongsToMany $q) {
                $q->whereNull('account_conversation.deleted_at');
            })
            ->orWhereHas('lastMessage.conversation.participants', function (Builder|BelongsToMany $q) {
                $q->whereColumn('account_conversation.deleted_at', '<', 'messages.created_at');
            })

But still have either one, either none. Also the // Conditional check on message time is where I struggle. I don't see how it can be done somehow differently 🤔

[vernal ruin]

if i understand this right, you want to exclude the comments that are deleted?

[royal zenith]

Nop, I want to exclude the conversations that don't have messages created after the account_conversation.deleted_at 😅