Get info by javascript in laravel

Question

I'm trying to get specific column from second table by javascript and return data of it to my view but i'm not sure how it can be done.

Logic

1. Products table has price column
2. Discount table has product_id, min, max and amount columns
3. I input number as quantity, if have product id in my discount table base on min and max return the amount as new price

Code

so far this is my codes (I am aware that specially my controller method has identifying issue to find te right data)

JavaScript

<script>
  $(document).ready(function() {
    // if quantity is not selected (default 1)
    $('#newprice').empty();
    var quantity =  parseInt(document.getElementById("quantity").value);
    var shipingcost = parseFloat(quantity);
    var shipingcostnumber = shipingcost;
    var nf = new Intl.NumberFormat('en-US', {
        maximumFractionDigits:0, 
        minimumFractionDigits:0
    });
    $('#newprice').append('Rp '+nf.format(shipingcostnumber)+'');

    // if quantity is changed
    $('#quantity').on('change', function() {
      var quantity = parseInt(document.getElementById("quantity").value);
      var qtyminmax = $(this).val();
      if(qtyminmax) {
        $.ajax({
          url: '{{ url('admin/qtydisc') }}/'+encodeURI(qtyminmax),
          type: "GET",
          dataType: "json",
          success:function(data) {
            $('#totalPriceInTotal').empty();
            var shipingcost = parseFloat(data)+parseFloat(quantity);
            var shipingcostnumber = shipingcost;
            var nf = new Intl.NumberFormat('en-US', {
                maximumFractionDigits:0, 
                minimumFractionDigits:0
            });
            $('#totalPriceInTotal').append('Rp '+nf.format(shipingcostnumber)+'');
          }
        });
      }else{
        //when quantity backs to default (1)
        $('#newprice').empty();
        var quantity = parseInt(document.getElementById("quantity").value);
        var shipingcost = parseFloat(quantity);
        var shipingcostnumber = shipingcost;
        var nf = new Intl.NumberFormat('en-US', {
            maximumFractionDigits:0, 
            minimumFractionDigits:0
        });
        $('#newprice').append('Rp '+nf.format(shipingcostnumber)+'');
      }
    });
  });
</script>

Route

Route::get('qtydisc/{id}', 'ProductController@qtydisc');

Controller

public function qtydisc($id){
      return response()->json(QtyDiscount::where('min', '>=', $id)->orWhere('max', '<=', $id)->pluck('min'));
    }

Question

1. What should I change in my controller method to get the right data?
2. What should I change in my JavaScript code? should I add product ID in my route as well or...?

UPDATE

I'm trying some changes in my code but I can't get right amount

controller

public function qtydisc($id, $qty){
      $price = DB::table('qty_discounts')
              ->where('product_id', $id)
              ->where([
                  ['min', '>=', $qty],
                  ['max', '<=', $qty],
              ])
              // ->where('min', '>=', $qty)
              // ->where('max', '<=', $qty)
              ->select('amount')
              ->first();
      return response()->json($price);
    }

route

Route::get('qtydisc/{id}/{qty}', 'ProductController@qtydisc');

javascript

//as before...

$('#quantity').on('change', function() {
      var idofproduct = ["{{$product->id}}"]; //added
      var quantity = parseInt(document.getElementById("quantity").value);
      var qtyminmax = $(this).val();
      if(qtyminmax) {
        $.ajax({
          url: '{{ url('admin/qtydisc') }}/'+idofproduct+'/'+encodeURI(qtyminmax), //changed
          type: "GET",
          dataType: "json",
          success:function(data) {
            $('#totalPriceInTotal').empty();
            var shipingcost = parseFloat(data)+parseFloat(quantity);
            var shipingcostnumber = shipingcost;
            var nf = new Intl.NumberFormat('en-US', {
                maximumFractionDigits:0, 
                minimumFractionDigits:0
            });
            $('#totalPriceInTotal').append('Rp '+nf.format(shipingcostnumber)+'');
          }
        });
      }else{
//rest of it as before

Screenshot

that's how my database look like and as results for quantity between 2 to 6 i get 7000 while i have to get 5000 from 2 to 5.

From number 7 to 9 i get no results at all.

From number 10 to up all i get is 7000

Any idea?

Answer 1

The main problem comes from your eloquent statement, you should use the >= in both conditions with OR operator using the orWhere helper, so it should be like :

$price = QtyDiscounts::where('product_id', $id)
    ->where('min', '>=', $qty)
    ->orWhere('max', '>=', $qty)
    ->pluck('amount')
    ->first();

But you need really to take a look to your JS structure, I suggest to split your code to function for the DRY concept, first the event listener like :

$('body').off('input', '#quantity').on('input', '#quantity', function () {
    var qty = parseInt( $(this).val() );

    if(qty) {
        getAmount(qty);
    }else{
        getAmount(1);
    }
});

Then the helper functions to call :

function setValue(quantity, amount)
{
    var newPrice = $('#newprice');
    var totalPrice = $('#totalPriceInTotal');

    newPrice.empty();
    totalPrice.empty();

    var nf = new Intl.NumberFormat('en-US', {
        maximumFractionDigits:0,
        minimumFractionDigits:0
    });

    newPrice.val('Rp '+nf.format(amount)+'');
    totalPrice.val('Rp '+nf.format(parseFloat(amount)*parseFloat(quantity))+'');
};

function getAmount(quantity)
{
    var url = $('#qty_url').val();
    var productId = $('#product_id').val();

    $.ajax({
        url: url+'/'+productId+'/'+encodeURI(quantity),
        type: "GET",
        dataType: "json",
        success:function(amount) {
            setValue(quantity, amount);
        }
    });
};

I guess you've a simple HTML structure, so it should work this way, why I suggest here if never using the php variable like url & product_id directly inside your JS code instead attach the values you want to hidden inputs and get the value of these input's using the JS :

<input type="hidden" id="qty_url" value="{{ url('admin/qtydisc') }}" />
<input type="hidden" id="product_id" value="{{ $product->id }}"/>