Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

FrazeColder's avatar

Laravel eloquent belongs to many query returns entries multiple time

I have a products, users and products_marks table. Users can "mark" products. When they mark one product the database stores the user_id and the product_id in the products_marks table.

Marks basically mean a user wants to watch this product and has a watchlist. Of course I want to show the user all products he has marked. This means I have to get all products where the user_id in the products_marks table matches the actually user. However, I have some extra requirements to this as well.

Products do have a post_status_id. This represents weather the product is publish, offline, in review, pending, etc. The marked products should only be represented to the user if the product is also online. BUT if the user has marked a product which he has posted by himself, so he is the owner of the product, he should also see this product when it is publish AND pending.

This also works in most cases. I already have an eloquent query for this. But now I have found a strange problem. A user reported me that he sees one product two times in his watchlist. I have run the SQL query for exactly this user and yes, the database is returning the product two times! Why this? What do I have to change in order to get each product only once?

User model:

/** Relationship */
public function markedProducts(){
    return $this->belongsToMany('App\Models\Product', 'products_marks');
}

/** Eloquent Query */
public static function markedProductsPaginated(User $user){
    $products = Product::select('products.*')
        ->join('products_marks', 'products_marks.product_id', '=', 'products.id')
        ->whereHas('marks', function ($query) use ($user) {
            $query->where(function($query) use ($user){
                $query->where('products_marks.user_id', $user->id);
            });
            $query->where(function($query){
                $query->where('page_status_id', PageStatus::getIdByStatus('publish'));
            });
            $query->orWhere(function($query) use ($user){
                $query->where('page_status_id', PageStatus::getIdByStatus('pending'))
                    ->where('products.user_id', $user->id);
            });
        })
        ->orderBy('products_marks.created_at', 'desc')
        ->paginate(10);

    $products->loadCount('allPublishedComments');

    return $products;
}

Query as SQL statement:

select `products`.* from `products` inner join `products_marks` on `products_marks`.`product_id` = `products`.`id` 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` = 19) and (`page_status_id` = 1) or (`page_status_id` = 4 and `products`.`user_id` = 3))) order by `products_marks`.`created_at` desc limit 10 offset 0

I have also created a db fiddle here where you can run this SQL statement and see that the database is returning the entry for the user_id 13 two times. But why is that? And why only sometimes?

It looks like when multiple users mark the same product I get it multiple times back. As you can see in the fiddle the product with the id 43 got marked two times!

Kind regards and thank you!

0 likes
5 replies
MarianoMoreyra's avatar

Hi @frazecolder

With that query, that's the expected result as you are joining products first, with products_marks, so in this case, for each product in products table, it will bring ALL the corresponding records that match on products_marks, duplicating the info from products table.

So, in this case, you should start your query from the products_marks table and then join with products. If you try with this query you'll see it works as expected:

select `products`.* 
from `products_marks` 
inner join `products` on `products_marks`.`product_id` = `products`.`id` 
where ((`products_marks`.`user_id` = 19) 
  and (`page_status_id` = 1) 
   or (`page_status_id` = 4 and `products`.`user_id` = 19))
order by `products_marks`.`id` desc limit 10 offset 0

Hope this makes sense to you! Otherwise let me know and I'll see if I can come with a better explanation!

MarianoMoreyra's avatar

@frazecolder traducing this to Eloquent it should be something similar to this I believe:

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

Most probably this won't work as I'm writing this on-the-fly, but you'll surely get the idea! :)

rodrigo.pedra's avatar
rodrigo.pedra
5 years ago
Best Answer
Level 56

hat the database is returning the entry for the user_id 13 two times

That is because you have a ->join(...) between products and products_marks. As product_marks retrieves two records for user 13 after the WHERE conditions, the resultset will have 2 records. Even if you are selecting only records from the products table.

As you want to order by product_marks.created_at, I suggest two options:

1 - Use ->distinct()

$products = Product::query()
    ->select('products.*')
    ->distinct()
    ->join('products_marks', 'products_marks.product_id', '=', 'products.id')
    ->whereHas('marks', function ($query) use ($user) {
        $query->where(function ($query) use ($user) {
            // product is published
            $query->orWhere('products.page_status_id', PageStatus::getIdByStatus('publish'));

            $query->orWhere(function ($query) use ($user) {
                // user created the product
                $query->where('products.user_id', $user->id);

                // AND user marked the product
                $query->where('products_marks.user_id', $user->id);

                // AND the product is either pending or published
                // this might not be needed
                $query->whereIn('page_status_id', [
                    PageStatus::getIdByStatus('pending'),
                    PageStatus::getIdByStatus('publish'),
                ]);
            });
        });
    })
    ->orderByDesc('products_marks.created_at')
    ->get();

2 - Remove the join clause and order by a subquery

$products = Product::query()
    ->whereHas('marks', function ($query) use ($user) {
        $query->where(function ($query) use ($user) {
            // product is published
            $query->orWhere('products.page_status_id', PageStatus::getIdByStatus('publish'));

            $query->orWhere(function ($query) use ($user) {
                // user created the product
                $query->where('products.user_id', $user->id);

                // AND user marked the product
                $query->where('products_marks.user_id', $user->id);

                // AND the product is either pending or published
                // this might not be needed
                $query->whereIn('page_status_id', [
                    PageStatus::getIdByStatus('pending'),
                    PageStatus::getIdByStatus('publish'),
                ]);
            });
        });
    })
    ->orderBy(function ($query) {
        $query->select('created_at')
            ->from('products_marks')
            ->whereColumn('products_marks.product_id', 'product_id');
    }, 'DESC')
    ->get();

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

I personally prefer option 2. Modern DBMS will optimize the execution to run the subquery just once.

I also tried to refactor the where conditions inside the ->whereHas(...) closure to better match your description. I might have got something wrong on that as it I didn't quite understood that part's description.

1 like

Please or to participate in this conversation.