Support the ongoing development of Laravel.io →
Database Eloquent Architecture
Last updated 2 years ago.
0

I recommend you to use relationships.

I am starting on a clean installation of laravel 4.2

First create your migrations.

tax_rate_values table

php artisan migrate:make create_tax_rate_values_table --table=tax_rate_values

products table

php artisan migrate:make create_products_table --table=products

product_prices table

php artisan migrate:make create_product_prices_table --table=product_prices

product_taxes table

php artisan migrate:make create_product_taxes_table --table=product_taxes

Then edit your migrations as follows.

  • ***_create_products_table.php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateProductsTable extends Migration {

	/**
	 * Run the migrations.
	 *
	 * @return void
	 */
	public function up()
	{
		Schema::create('products', function(Blueprint $table)
		{
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->string('product_name');
            $table->decimal('product_stock_amount');
            $table->softDeletes();
            $table->timestamps();
		});
	}

	/**
	 * Reverse the migrations.
	 *
	 * @return void
	 */
	public function down()
	{
        Schema::drop('products');
	}

}
  • ***_create_product_prices_table.php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateProductPricesTable extends Migration {

	/**
	 * Run the migrations.
	 *
	 * @return void
	 */
	public function up()
	{
		Schema::create('product_prices', function(Blueprint $table)
		{
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->integer('product_id')->unsigned();
            $table->foreign('product_id')->references('id')->on('products')->onDelete('cascade');
            $table->decimal('product_price_value');
            $table->date('product_price_valid_from');
            $table->softDeletes();
            $table->timestamps();
		});
	}

	/**
	 * Reverse the migrations.
	 *
	 * @return void
	 */
	public function down()
	{
        Schema::drop('product_prices');
	}

}
  • ***_create_product_taxes_table.php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateProductTaxesTable extends Migration {

	/**
	 * Run the migrations.
	 *
	 * @return void
	 */
	public function up()
	{
		Schema::create('product_taxes', function(Blueprint $table)
		{
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->integer('product_id')->unsigned();
            $table->foreign('product_id')->references('id')->on('products')->onDelete('cascade');
            $table->integer('taxRate_id')->unsigned();
            $table->foreign('taxRate_id')->references('id')->on('tax_rate_values');
            $table->date('product_tax_valid_from');
            $table->softDeletes();
            $table->timestamps();
		});
	}

	/**
	 * Reverse the migrations.
	 *
	 * @return void
	 */
	public function down()
	{
        Schema::drop('product_taxes');
	}

}
  • ***_create_tax_rate_values_table.php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateTaxRateValuesTable extends Migration {

	/**
	 * Run the migrations.
	 *
	 * @return void
	 */
	public function up()
	{
		Schema::create('tax_rate_values', function(Blueprint $table)
		{
            $table->engine = 'InnoDB';
            $table->increments('id');
            $table->integer('tax_rate_value');
            $table->date('tax_rate_value_valid_from');
            $table->softDeletes();
            $table->timestamps();
		});
	}

	/**
	 * Reverse the migrations.
	 *
	 * @return void
	 */
	public function down()
	{
        Schema::drop('tax_rate_values');
	}

}

Run your migrations.

php artisan migrate

All your database tables should be successfully created

Now on your models -Products.php

class Products extends Eloquent {
    protected $table = 'products';
    /**
     * Blacklist
     *
     * Allow for mass Update
     *
     * @var string
     */
    protected $guarded = array();

    public function productPrice()
    {
        return $this->hasOne('ProductPrice');
    }

    public function productTax()
    {
        return $this->hasOne('ProductTax');
    }
}

-ProductPrice.php

class ProductPrice extends Eloquent {
    protected $table = 'products';
    /**
     * Blacklist
     *
     * Allow for mass Update
     *
     * @var string
     */
    protected $guarded = array();

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

-ProductTax.php

class ProductTax extends Eloquent {
    protected $table = 'products';
    /**
     * Blacklist
     *
     * Allow for mass Update
     *
     * @var string
     */
    protected $guarded = array();

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

    public function taxRate()
    {
        return $this->hasOne('TaxRateValue');
    }
}

-TaxRateValue.php

class TaxRateValue extends Eloquent {
    protected $table = 'products';
    /**
     * Blacklist
     *
     * Allow for mass Update
     *
     * @var string
     */
    protected $guarded = array();

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

This should be able to sort you out well, because now in your controller instead of thinking in joins, you would do something like

$product = Product::find(1)->productPrice();

And to create a product price for an already existing product, you would do something like

$date = new DateTime('NOW');
return $date->format('Y-m-d H:i:s');
$data = array('product_price_value' => '1500', 'product_price_valid_from' => $date);
Product::find(1)->productPrice()->save(new ProductPrice($data));

And plus you can use laravels softdeletes functionality to perform archiving.

Hope this helps

Last updated 2 years ago.
0

Thank you for your very long and helpful answer. Unfortunately, I think it misses an important (for me) point. Consider the following example

Product table (1 entry):
ID=1, name="Test product"

Price-Table (2 entries)
ID=1, product_id=1, price_value=100, price_valid_from="2010-01-01"
ID=1, product_id=1, price_value=200, price_valid_from="2014-01-01"

At today's date, I must get the price value of 200. Is this possible with your approach from above? Or is it accomplished by the softdelete mechanism? Only one entry for a product is "there" at any time and all others are "deleted" but still accessible? If so, how are they accessible if not through the model?

Thanks again for your help.

Last updated 2 years ago.
0

You may consider selecting from products_price WHERE price_valid_from <= "2014-06-13" ORDER BY price_valid_from DESC.

This would select the newest price for an item that is not beyond than today's date. So in case you had set pricing to increase as specified date in the future, this would get the current price.

Last updated 2 years ago.
0

Thank you for your reply. That's what I thought, but where do I have to put this? Does it have to be put to

 hasOne('productprice')

?

Thanks in advance.

Last updated 2 years ago.
0

Sign in to participate in this thread!

Eventy

Your banner here too?

fkerber fkerber Joined 12 Jun 2014

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.