Sort relation "Many To Many" using as base field of another table

Question

I am making a virtual store, to know better the Laravel framework, and I came across the following situation:

I have a table called products to store product-related information. I also have a table called types to store product types. I also have a table called prices, to store the prices of the products - I have separated the prices to be able to have a history of changing prices of the products.

There are three types of products:

1. Simple: Simple products, which have only one price;
2. Grouped: Products without price, as they are only a grouping of others products, each with its own price. Used when a product has several presentations;
3. Child: Products that are part of a product grouped. Have price, but are not displayed alone. These products are only displayed within Grouped type products of which are part.

I use the Children table to relate the products table to itself. This way, I can relate the products of type Child and Grouped.

Follow the database diagram:

The models are listed as follows:

Product belongsTo Type

Product belongsToMany Product

Product hasMany Price

The problem occurs when I try to recover the price of Child-type products from a Grouped product. This information comes in the form of an Eloquent Collection, with all the products that are part of this product grouped in the order of insertion. I’d like to sort them out for the price, but the fact that this information is on another table made it difficult. The closest I could get to the desired result was modifying the method of the relationship between the Product class and itself. Follow the code:

public function children(){

        return $this->belongsToMany('Product', 'children', 'father_id', 'child_id')->join('prices', 'child_id', '=', 'product_id')->orderBy('value');
    }

However, this repeats some values, since it does not bring only the last inserted price.

Answer 1

See if it helps:

How to order using Eloquent:
https://stackoverflow.com/questions/17553181/laravel-4-how-to-order-by-using-eloquent-orm

Select the first 10 lines:
https://stackoverflow.com/questions/21255508/select-the-first-10-rows-laravel-eloquent

How to pick up the first record with Laravel :
https://stackoverflow.com/questions/19794098/how-to-get-the-first-record-with-laravel-4-raw-queries

Answer 2

I think in this case it would be nice for you to save a timestamp so you know when that price was set and help you with ordination... put a created_at in the price table. Take the hint of putting in some tables there a creation field and another modification until you have a better control...