Return values to null in laravel

Question

I'm working on e-commerce project with Laravel 5.5 where in my products table I have 2 columns named discount and discount_date, what I want is when the date of column discount_date comes both discount and discount_date columns become null automatically.

How can I do that?

Update:

productcontroller:

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Product;
use App\Subcategory;
use App\Category;
use App\Attribute;
use Carbon\Carbon;
use App\User;
use Auth;
use DB;
use Storage;
use Spatie\Permission\Models\Role;
use Spatie\Permission\Models\Permission;
use jpmurray\LaravelCountdown\Countdown;

class ProductController extends Controller
{

    public function __construct() {
        $this->middleware(['auth', 'isAdmin']);
    }

    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $products = Product::orderby('id', 'desc')->get();
        return view('admin.products.index', compact('products'));
    }

    public function show($slug)
    {
        $product = Product::where('slug', $slug)->firstOrFail();
        $countdown = DB::table('products')->where('discount_date', '!=', null)->pluck('discount_date');
        $now = Carbon::now();
        return view('admin.products.show', compact('product', 'countdown', 'now'));
    }



    public function create()
    {
      $categories = Category::all();
      $subcategories = Subcategory::all();
      $attributes = Attribute::all();
      $user = Auth::user();
      return view('admin.products.create', compact('user', 'categories', 'subcategories', 'attributes'));
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
      //Validating title and body field
      $this->validate($request, array(
          'title'=>'required|max:225',
          'slug' =>'required|max:255|unique:products',
          'user_id' =>'required|numeric',
          'image_one' =>'nullable|image',
          'image_two' =>'nullable|image',
          'image_three' =>'nullable|image',
          'image_four' =>'nullable|image',
          'short_description' => 'nullable|max:1000',
          'description' => 'required|max:100000',
          'subcategory_id' => 'required|numeric',
          'discount' => 'nullable|numeric',
          'discount_date' => 'nullable|date',
          'price' => 'required|numeric',
        ));

      $product = new Product;

      $product->title = $request->input('title');
      $product->slug = $request->input('slug');
      $product->user_id = $request->input('user_id');
      $product->description = $request->input('description');
      $product->short_description = $request->input('short_description');
      $product->subcategory_id = $request->input('subcategory_id');
      $product->discount = $request->input('discount');
      $product->discount_date = $request->input('discount_date');
      $product->price = $request->input('price');



      if ($request->hasFile('image_one')) {
        $image = $request->file('image_one');
        $filename = 'product' . '-' . time() . '.' . $image->getClientOriginalExtension();
        $location = public_path('images/');
        $request->file('image_one')->move($location, $filename);

        $product->image = $filename;
      }


      $product->save();

      $product->attributes()->sync($request->attributes, false);


      //Display a successful message upon save
      Session::flash('flash_message', 'Product, '. $product->title.' created');
      return redirect()->route('admin.products.index');
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
      $product = Product::find($id);
      $categories = Category::all();
      $users = User::all();
      $subcategories = Subcategory::all();
      $attributes = Attribute::all();
      $attributes2 = array();
      foreach($attributes as $attribute) {
        $attributes2[$attribute->id] = $attribute->title;
      }
      return view('admin.products.edit', compact('product', 'categories', 'users', 'subcategories', 'attributes2'));
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {

      $product = Product::find($id);
      if ($request->input('slug') == $product->slug) {
             $this->validate($request, array(
               'title'=>'required|max:225',
               'description' =>'required|min:4|max:100000',
               'short_description' =>'nullable|min:4|max:1000',
               'image_one' =>'sometimes|image',
               'image_two' =>'sometimes|image',
               'image_three' =>'sometimes|image',
               'image_four' =>'sometimes|image',
               'discount' =>'nullable|numeric',
               'discount_date' =>'nullable|date',
               'user_id' =>'required|numeric',
               'price' =>'required|numeric',
               'subcategory_id' => 'sometimes|integer',
             ));
         } else {
         // validate the date
         $this->validate($request, array(
           'slug' =>'required|max:225|unique:products',
           'title'=>'required|max:225',
           'description' =>'required|min:4|max:100000',
           'short_description' =>'nullable|min:4|max:1000',
           'image_one' =>'sometimes|image',
           'image_two' =>'sometimes|image',
           'image_three' =>'sometimes|image',
           'image_four' =>'sometimes|image',
           'discount' =>'nullable|numeric',
           'discount_date' =>'nullable|date',
           'user_id' =>'required|numeric',
           'price' =>'required|numeric',
           'subcategory_id' => 'sometimes|integer',
             ));
         }

      $product = Product::where('id',$id)->first();

      $product->title = $request->input('title');
      $product->description = $request->input('description');
      $product->slug = $request->input('slug');
      $product->user_id = $request->input('user_id');
      $product->short_description = $request->input('short_description');
      $product->image_one = $request->input('image_one');
      $product->image_two = $request->input('image_two');
      $product->image_three = $request->input('image_three');
      $product->image_four = $request->input('image_four');
      $product->discount = $request->input('discount');
      $product->discount_date = $request->input('discount_date');
      $product->price = $request->input('price');
      $product->subcategory_id = $request->input('subcategory_id');

      if ($request->hasFile('image_one')) {
        $image = $request->file('image_one');
        $filename = 'product' . '-' . time() . '.' . $image->getClientOriginalExtension();
        $location = public_path('images/');
        $request->file('image_one')->move($location, $filename);

        $oldFilename = $product->image_one;
        $product->image_one = $filename;
        Storage::delete($oldFilename);
      }

      $product->save();
      $product->attributes()->sync($request->attributes);

      return redirect()->route('products.index',
          $product->id)->with('flash_message',
          'Product, '. $product->title.' updated');
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
      $product = Product::findOrFail($id);
      $product->attributes()->detach();
      Storage::delete($product->image);
      $product->delete();

      return redirect()->route('products.index')
          ->with('flash_message',
           'Product successfully deleted');
    }
}

Update 2 Product model

    <?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Product extends Model
{

  protected $table = 'products';

  protected $fillable = [
      'title', 'slug', 'image_one', 'image_two', 'image_three', 'image_four', 'short_description', 'description', 'price', 'discount', 'discount_date',
  ];

  public function category(){
     return $this->belongsTo(Category::class);
  }
  public function subcategory(){
     return $this->belongsTo(Subcategory::class);
  }

  public function attributes()
  {
     return $this->belongsToMany(Attribute::class, 'product_attributes', 'product_id', 'attribute_id');
  }

  public function order(){
     return $this->hasMany(Order::class);
  }

  public function discounts(){
    return $this->hasMany(Discount::class, 'product_id', 'id');
  }


}

Update 3 base on Bagus Tesa answer:

Now i have discount table and everything is set, here is my discountcontroller

<?php

namespace App\Http\Controllers;

use Illuminate\Http\Request;
use App\Discount;
use App\Product;

class DiscountController extends Controller
{

    public function __construct() {
        $this->middleware(['auth', 'isAdmin']);
    }
    /**
     * Display a listing of the resource.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        $discounts = Discount::orderby('id', 'desc')->get();
        $products = Product::all();
        return view('admin.discounts.index', compact('discounts', 'products'));
    }

    /**
     * Store a newly created resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @return \Illuminate\Http\Response
     */
    public function store(Request $request)
    {
      //Validating title and body field
      $this->validate($request, array(
          'amount'=>'required|max:225',
          'valid_from' =>'required|date',
          'valid_to' =>'required|date',
          'product_id' => 'required|numeric',
        ));

      $discount = new Discount;

      $discount->amount = $request->input('amount');
      $discount->valid_from = $request->input('valid_from');
      $discount->valid_to = $request->input('valid_to');
      $discount->product_id = $request->input('product_id');


      $discount->save();
    }

    /**
     * Display the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function show($id)
    {
        //
    }

    /**
     * Show the form for editing the specified resource.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function edit($id)
    {
        //
    }

    /**
     * Update the specified resource in storage.
     *
     * @param  \Illuminate\Http\Request  $request
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function update(Request $request, $id)
    {
        //
    }

    /**
     * Remove the specified resource from storage.
     *
     * @param  int  $id
     * @return \Illuminate\Http\Response
     */
    public function destroy($id)
    {
        //
    }
}

This is my discount model

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Discount extends Model
{
    protected $table = 'discounts';

    protected $fillable = ['valid_from', 'valid_to', 'amount', 'product_id',];

    public function products(){
       return $this->belongsTo(Product::class, 'product_id', 'id');
   }

   public function scopeValid($query){
         return $query->whereDate('valid_from', '>=', Carbon::today()->toDateString())
                      ->whereDate('valid_to', '<=', Carbon::today()->toDateString());
   }
}

Update 4:

Now everything works except ending discount time which was my issue before all these changes.

As you can see in screenshot expire time still not comes but status of discount is expire.

Here is the code i use in my blade:

@if ($product->$discounts > $mytime)
 OK!
@else
 Expired!
@endif

And this is in my controller:

$count = $product->discounts()->valid()->get();
$discounts = Discount::where('product_id', '=', $product->id)->get();
$mytime = Carbon::now();

Answer 1

I suggest to extract discount and discount_date into their own table, like

discounts (
    datetime valid_from, 
    datetime valid_to, 
    int product_id, 
    decimal amount
) 

and then just lookup the discounts valid for a given date and product, e.g.

SELECT product_id, amount 
FROM discounts 
WHERE product_id = 42
AND (NOW() BETWEEN valid_from AND valid_to);

While this then requires an additional query or a join to fetch discounts, it is easier to maintain discounts in their own table. You don't need to remove expired discounts because the BETWEEN query makes sure you only get the discounts valid for the current time.

Also, this way you can keep the history of discounts. Something you'd lose when null'ing the columns. Even if you wanted to delete them, it would be easier because you can just delete all discounts where NOW() > valid_to.

Unfortunately, I do not know how to do this with Laravel/Eloquent. But this is much more about database modeling than it is about a specific framework. Your framework should just allow this. I am sure it does somehow. But I don't know how.

Note: I typed the code snippets from my head. You want to doublecheck their syntax

Answer 2

Original Question:

I'm working on e-commerce project with Laravel 5.5 where in my products table I have 2 columns named discount and discount_date, what I want is when the date of column discount_date comes both discount and discount_date columns become null automatically.

Answer:

There is several way to tackle this:

• One, stick on this abominable approach and use Laravel Task Scheduler to set null the columns.
• The second idea is Gordon's normalized table approach.
• Third is using MySQL Event Scheduler as suggested by Leonhard to set null the columns.
• Add a couple line of code that checks and nulls the discount's column whenever you fetch the Products.

I will go with expanding Gordon's approach into Laravel's way because it features the ability to retain older discounts.

Gordon explained to have a table that looks like the following below.

discounts (
    int id,
    datetime valid_from, 
    datetime valid_to, 
    int product_id, 
    decimal amount,
    datetime created_at,
    datetime updated_at
) 

Note: the table structure is slightly changed to follow Laravel's convention (the created_at and updated_at; also added the primary key for clarity.

To create such table, you could fire query by yourself or use Laravel's Migration. I will not cover the database creation as it is pretty unclear whether you used MySQL/MariaDB directly or helped PhpMyAdmin or perhaps you utilize Laravel migrations yourself. Assumed you have the table ready, named discounts, we will have to add a new model into Laravel:

<?php

namespace App;

use Carbon\Carbon;
use Illuminate\Database\Eloquent\Model;

class Discount extends Model
{
  protected $table = 'discounts';
  protected $dates = ['valid_from', 'valid_to'];

  public function products(){
        return $this->belongsTo(Product::class, 'product_id', 'id');
  }

  public function scopeValid($query){
        return $query->where('valid_from', '>=', Carbon::now()->toDateTimeString())
                     ->where('valid_to', '<', Carbon::now()->toDateTimeString());
  }
}

And you will need to add your Product model with the following relationship:

public function discounts(){
    return $this->hasMany(Discount::class, 'product_id', 'id');
}

Notice the scopeValid($query), its our shortcut to do filtering on valid discounts - you can check Laravel Scopes on the documentation. The ->whereDate is a specific function to query datetime field in Laravel and you can find some example in Laravel Diary. This way, you could filter the Product's discount using the following eloquent query to get the discounts:

Product::find(1)->discounts()->valid()->get();

The pro of this approach are:

• You can track the history of discounts
• Easier to maintain, less 'Task Scheduler' trouble.
• Readable for programmers.

The cons of this approach are:

• You might tumble if there are two active discounts. But i don't think we can use datetime for primary keys.. and what if they overlap in sense that one spans from 10 Oct to 15 Oct and the other spans 11 Oct to 14 Oct? Funny is not it. Perhaps DBAs out there can suggest less-code trouble approach? This issue can be countered by checking the discount table every time a new discount is about to be added inside a transaction.

Your next question:

OK! even more confusing now :))), I included my controller in question please now see what fields that i have and where should i put the code. appreciate it.

Next answer:

We don't know where this discounts going to be used.. I presume it will be shown in your show($slug) view (admin.products.show) or when calculating the total price.. something like that(?) We are programmers (and DBA, i suppose), not magicians..

EDIT: Silly me for forgetting that Carbon::today() only gives you the date for today and toDateString() will give you only the date part. Hence the time reference should be Carbon::now()->toDateTimeString().

EDIT: Wrong query, should be where to filter based on the date and time.

Answer 3

If you really want to set the values to null you can use the MySQL Event Scheduler

But I think it's an easier way to modify your select Statement to something like this:

select 
    id,
    IF(discount_date > now(), discount,null) as discount,
    IF(discount_date > now(), discount_date,null) as discount_date 
from product

You can test this on rextester.com

In laravel it should be something like:

 $products = Product::orderby('id', 'desc')->select('all','your','columns', 'IF(discount_date > now(), discount,null) as discount', 'IF(discount_date > now(), discount_date,null) as discount_date')->get();

and

$countdown = DB::table('products')->where('discount_date', '>', 'now()')->pluck('discount_date');