Select newest prices for each shop

Question

I am trying to get the newest prices of all products for each shop. My table setup is like this:

Table: products

Name       Type             Collation        Attributes   NullDefaultExtra
id              int(10)                                   UNSIGNEDNo   None   AUTO_INCREMENT
name        varchar(255)utf8_unicode_ci                  No   None
descriptiontext              utf8_unicode_ci                  Yes  Null

Table: products_prices

Name        Type              Collation        Attributes   NullDefault                      Extra
id               int(10)                                    UNSIGNEDNo   None                         AUTO_INCREMENT
shop_id     int(10)                                    UNSIGNEDNo   None
product_id int(10)                                    UNSIGNEDNo   None
price          decimal(10,2)                                            No   None
created_at timestamp                                                 No   0000-00-00 00:00:00
updated_attimestamp                                                 No   0000-00-00 00:00:00

Migration: products

Schema::create('products', function ($table) {
    $table->increments('id')->unsigned();
    $table->string('name')->unique();
    $table->text('description')->nullable();
});

Migration: products_prices

Schema::create('products_prices', function($table) {
    $table->increments('id')->unsigned();
    $table->integer('shop_id')->unsigned();
    $table->integer('product_id')->unsigned();
    $table->decimal('price', 10, 2);
    $table->timestamps();

    $table->foreign('product_id')->references('id')->on('products')
        ->onUpdate('cascade')->onDelete('cascade');
    $table->foreign('shop_id')->references('id')->on('shops')
        ->onUpdate('cascade')->onDelete('cascade');
});

Model: Product

<?php

class Product extends Eloquent {

    protected $table = 'products';


    public function prices()
    {
        return $this->hasMany('ProductPrice');
    }

}

Model: ProductPrice

<?php

class ProductPrice extends Eloquent {

    protected $table = 'products_prices';

    public function product()
    {
        return $this->belongsTo('Product');
    }

}

Controller

<?php

class ProductController extends BaseController {

    public function index()
    {
        $products = Product::with(array('prices'))->get();

        return View::make('products', array('products' => $products));
    }
}

So now in my view I have all products with all prices. But I only want to return the newest prices for all shop_ids. For example if I have shop_ids 1, 2, 3 and 4. I would like to return 4 rows per product with the newest price of these shops.

EDIT:

When I execute following query directly on mySQL it gives me the correct result, but how do I do this in Eloquent way?

SELECT * FROM products_prices p JOIN
(SELECT shop_id, product_id, MAX(created_at) AS created_at
FROM products_prices GROUP BY product_id, shop_id) lastEntry
ON p.product_id = lastEntry.product_id AND p.shop_id = lastEntry.shop_id AND p.created_at = lastEntry.created_at;

I have succeeded making an Eloquent solution, but I need DB::raw in it, could I do it without raw?

$products = Product::with(array(
    'prices' => function($q) {
        $q->join(DB::raw('(SELECT shop_id, product_id, MAX(created_at) AS created_at FROM products_prices GROUP BY product_id, shop_id) lastEntry'), function($join)
        {
            $join->on('products_prices.product_id', '=', 'lastEntry.product_id');
            $join->on('products_prices.shop_id', '=', 'lastEntry.shop_id');
            $join->on('products_prices.created_at', '=', 'lastEntry.created_at');
        })->orderBy('price', 'asc');
    }
))->get();

how did you formatted tables descriptions? is there any tool for this or you did that manually? — Limon Monte, Apr 04 '15 at 10:34
how about this http://stackoverflow.com/questions/18143061/laravel-orderby-on-a-relationship — Dhiraj, Apr 04 '15 at 15:25
@limonte I did it manually using the `kbd` tag and a lot of spaces — Mark Veenstra, Apr 05 '15 at 07:17

score 0 · Accepted Answer · answered Apr 08 '15 at 09:10

I solved my issue using some raw querying, like:

  $products = Product::with(array(
    'prices' => function($q) {
      $q->join(DB::raw('(SELECT shop_id, product_id, MAX(created_at) AS created_at FROM products_prices GROUP BY product_id, shop_id) lastEntry'), function($join)
      {
        $join->on('products_prices.product_id', '=', 'lastEntry.product_id');
        $join->on('products_prices.shop_id', '=', 'lastEntry.shop_id');
        $join->on('products_prices.created_at', '=', 'lastEntry.created_at');
      })->orderBy('price', 'asc');
    }
  ))->get();