Laravel eloquent: Only retriev entries where relationship has entries (join)

$products = Product::whereHas('marks', function ($query) {
    $query->where('products_marks.user_id', 2);
})->orderBy('created_at', 'desc')->paginate(10);

Docs: https://laravel.com/docs/8.x/eloquent-relationships#querying-relationship-existence

Yes, this is working. Thank you. However, I do get all products instead of only the marked once.. is my relation wrong?

This is what I get as raw SQL: select * from products where exists (select * from users inner join products_marks on users.id = products_marks.user_id where products.id = products_marks.product_id and products_marks.user_id = 2) and page_status_id = 1 or page_status_id = 4 order by created_at desc

Problem is this or page_status_id = 4

You don't show your full code here.

$products = Product::whereHas('marks', function ($query) {
    $query->where('products_marks.user_id', 2);
})->where(function ($query) {
    $query->where('page_status_id ', 1)->orWhere('page_status_id', 4);
})->orderBy('created_at', 'desc')->paginate(10);

or

$products = Product::whereHas('marks', function ($query) {
    $query->where('products_marks.user_id', 2);
})->whereIn('page_status_id', [1, 4])->orderBy('created_at', 'desc')->paginate(10);

Yes, you are right!

This is my query at the moment:

$products = Product::whereHas('marks', function ($query) use ($user) {
            $query->where('products_marks.user_id', $user->id);
        })
            ->where('page_status_id', PageStatus::getIdByStatus('publish'))
            ->orWhere('page_status_id', PageStatus::getIdByStatus('pending'))
            ->orderBy('created_at', 'desc')
            ->paginate(10);

page_status_id represents whether a product is published (1) or pending (4). I want to get only published articles OR pending articles where the logged in user is also the author (products.user_id) of the product.

I think I have it know. Is this correct?

$products = Product::whereHas('marks', function ($query) use ($user) {
            $query->where('products_marks.user_id', $user->id)
                ->where('page_status_id', PageStatus::getIdByStatus('publish'))
                ->orWhere('page_status_id', PageStatus::getIdByStatus('pending'))
                ->where('products.user_id', $user->id);
        })
            ->orderBy('created_at', 'desc')
            ->paginate(10);

Yes, this is working. But I want to sort the complete relation either by products_marks.id or products_marks.created_at... how can I do this?

If you want to order it by column in different table you have to use join as you have in OP.

But this leads to the problem that my $appends attributes are not correct anymore...

$pageStatusIds = PageStatus::whereIn('status', [publish' , 'pending')->pluck('id')->toArray();

$products = Product::select('products.*')->join('products_marks', 'products_marks.product_id', '=', 'products.id')->whereHas('marks', function ($query) use ($user) {
    $query->where('products_marks.user_id', $user->id);
})->whereIn('page_status_id', $pageStatusIds)->orderBy('products_marks.created_at', 'desc')->paginate(10);

But in that case you don't need whereHas

Thanks! You are a legend!

May any idea on my other thread about the comments and child comments?

@frazecolder I told you there what you have to do. And mark this thread as solved thanks.