Get a single product for each category in laravel

Question

I have two tables one is products and the other is categories with a one to many relationship

categories:

id	name
-	-
-	-

products:

id	name	price
First	row	-
Second	row	-

I already made the pivot table and the relation in code in Laravel. but what I'm trying to achieve is that for each category there is only one product and also that every time a new product is added it is placed first with the category.

I managed to make there be only one product per category with this code:

    Category::with('products')->orderBy('id', 'desc')->get()->map(function($categories) {
    $categories->setRelation('products', $categories->products->sortByDesc('id')->take(1));
    return $categories;
});

this solves the problem of a recent product by category but it does not solve the problem that when adding a new product in a category it is put first in the array

the query returns this where the last product I added was the coconut

 [
  {
    "id": 1,
    "name": "dairy",
    "products": {
      "6": {
        "id": 4,
        "name": "yoghurt",
        "price": "1",
        "created_at": "2022-09-02T16:45:24.000000Z",
        "updated_at": "2022-09-02T16:45:24.000000Z",
        "pivot": {
          "category_id": 1,
          "product_id": 4
        }
      }
    }
  },
  {
    "id": 2,
    "name": "fruits",
    "products": [
      {
        "id": 10,
        "name": coconut",
        "price": "2",
        "created_at": "2022-09-02T11:28:12.000000Z",
        "updated_at": "2022-08-15T00:24:00.000000Z",
        "pivot": {
          "category_id": 2,
          "product_id": 10
        }
      }
    ]
  }
]

This would be what I want to get:

where when adding a new product it is positioned in the first place with the category

    [
{
            "id": 2,
            "name": "fruits",
            "products": [
              {
                "id": 10,
                "name": coconut",
                "price": "2",
                "created_at": "2022-09-02T11:28:12.000000Z",
                "updated_at": "2022-08-15T00:24:00.000000Z",
                "pivot": {
                  "category_id": 2,
                  "product_id": 10
                }
              }
            ]
          },
          {
            "id": 1,
            "name": "dairy",
            "products": {
              "6": {
                "id": 4,
                "name": "yoghurt",
                "price": "1",
                "created_at": "2022-09-02T16:45:24.000000Z",
                "updated_at": "2022-09-02T16:45:24.000000Z",
                "pivot": {
                  "category_id": 1,
                  "product_id": 4
                }
              }
            }
          }
]

Thanks for reading and I appreciate the help.

Answer 1

There are multiple ways you can do this. One of them would be to use the new “one of many” relationship.

class Category extends Model
{
    public function latest_product()
    {
        return $this->hasOne(Login::class)->latestOfMany();
    }
}

Then you can do something like:

Category::with('latest_product')->latest('id')->get();

Also, since you have a pivot table, it should be a many-to-many relationship. If it is supposed to be a one-to-many, the category_id should be in the products table; not a pivot table.

Edit:

If you want the results to be ordered by the product_id instead of the category_id, something like this should work:

With Collection:

Category::with('latest_product')
    ->get()
    ->sortByDesc(fn ($query) => $query->latest_product->id);

With Subquery:

Category::with('latest_product')
    ->orderByDesc(
        DB::table('category_product')
            ->select('product_id')
            ->whereColumn('category_product.category_id', 'categories.id'))
            ->latest('product_id')
            ->take(1)
    ->get()

The second approach is probably faster. But I didn't test either of these. So might need some adjustments :)