Laravel Eloquent Custom Polymorphic Table Relations

laravel-eloquent-polymorphic-table-relations.md

Laravel Eloquent Polymorphic Table Relations

Consider this tables: products and product_settings.

Table products

id	name
1	Laptop
2	Tablet

Table product_settings

id	product_id	effectivity_date	price
1	1	2015-06-07	25,000.00
2	1	2015-07-07	23,000.00

Base from the tables, it is obvious that the example relationship is Product hasMany ProductSetting. But, we also need to access the currenSetting of a product based from the effectivity_date of the product. This relationship is achievable, look at the example below. The relationship can also be eager-loaded.

class Product 
{
  public function settings() 
  {
    return $this->hasMany('ProductSetting');
  }
  
  public function currentSetting() 
  {
     $currentDate = date('Y-m-d');

        $joinerSql = "
            (
                SELECT 
                product_id, max(effectivity_date) as max_date
                FROM product_settings
                where effectivity_date <= '{$currentDate}'
                group by product_id
            ) as joiner
        ";

        $onSql = "
            joiner.max_date   = product_settings.effectivity_date and 
            joiner.product_id =  product_settings.product_id
        ";

        return $this->hasOne('ProductSetting')
            ->select('product_settings.*')
            ->join(
                \DB::raw($joinerSql),
                \DB::raw(""),
                \DB::raw($onSql),
                \DB::raw("")
            );
  }
  
}

Reference

Thanks from this site: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/