Problem with getting results from database in my search form in laravel

Question

I have search form to list properties/ads through certain criteria (city, price, quadrature, property type, etc). I am having trouble when I try to put some values in form to fetch those items that correspond in the database, I get empty table with no results. I have three tables.

properties (id, city, price, quadrature, property_type)

categories (id, category, priority)

category_property (id, property_id, category_id)

This relationship is many-to-many and it works, I checked that. And this categories table it has default values. In category row it has offer, demand, buy, rent, house, flat, where offer and demand have priority 0, buy and rent priority 1, house and flat priority 2. In my url when I click on submit button it should get something like this

project/search/offer/buy/house/Madrid/min_price=10000-max_price=15000/min_quadrature=20-max_quadrature=30

where offer, buy, house are those values from category row. I am stuck from there how to solve this, so I could display appropriate results. Any help is greatly appreciated. Here is my code:

search.blade.php

<div class="row justify-content-md-center">

  <div class="col-md-8 order-md-1">
    <div>

      @if(isset($results))
        <table class="table">
          <thead>
            <th>City</th>
            <th>Price</th>
            <th>Quadrature</th>
          </thead>
          <tbody>
            @foreach ($results as $result)
              <tr>
                <th>{{ $result->city }}</th>
                <td>{{ $result->price }}</td>
                <td>{{ $result->quadrature }}</td>
              </tr>
            @endforeach
            @else
              @php
                {{echo "not set";}}
              @endphp
          </tbody>
        </table>
      @endif

    </div>
    <form id="searchForm" method="GET" action="/search">

      <div class="row">
        <div class="col-md-5 mb-3">
          <label>City</label>
          <input name="city" list="result" id="input" class="form-control">
          <datalist id="result"></datalist>
        </div>
      </div>

      <div class="row">
        <div class="col-md-6 mb-6">
          <label>Price</label>
          <input type="number" name="min_price" class="form-control" placeholder="Min Price">
          <input type="number" name="max_price" class="form-control" placeholder="Max Price">
        </div>
        <div class="col-md-6 mb-6">
          <label>Quadrature</label>
          <input type="number" name="min_quadrature" class="form-control" placeholder="Min quadrature">
          <input type="number" name="max_quadrature" class="form-control" placeholder="Max quadrature">
        </div>
      </div>

      <hr class="mb-4">

      <div class="row">

        <div class="col-md-4 mb-6">
          <h5>Payment</h4>
          <div class="d-block my-3">
            <div class="custom-control custom-radio">
              <input id="offer" name="propertyBidAsk" value="offer" type="radio" class="custom-control-input">
              <label class="custom-control-label" for="offer">Offer</label>
            </div>
            <div class="custom-control custom-radio">
              <input id="demand" name="propertyBidAsk" value="demand" type="radio" class="custom-control-input">
              <label class="custom-control-label" for="demand">Demand</label>
            </div>
          </div>
        </div>

        <div class="col-md-3 mb-6">
        <h5>Property payment</h4>
          <div class="d-block my-3">
            <div class="custom-control custom-radio">
               <input id="buy" name="propertyPayment" value="buy" type="radio" class="custom-control-input">
              <label class="custom-control-label" for="buy">Buy</label>
            </div>
            <div class="custom-control custom-radio">
              <input id="rent" name="propertyPayment" value="rent" type="radio" class="custom-control-input">
              <label class="custom-control-label" for="rent">rent</label>
            </div>
          </div>
        </div>

        <div class="col-md-5 mb-6">
          <h5>Property type</h4>
            <div class="d-block my-3 ">
              <div class="custom-control custom-radio">
                <input id="house" name="propertyType" value="house" type="radio" class="custom-control-input">
                <label class="custom-control-label" for="house">House</label>
              </div>
              <div class="custom-control custom-radio">
                <input id="flat" name="propertyType" value="flat" type="radio" class="custom-control-input">
                <label class="custom-control-label" for="flat">Flat</label>
              </div>

    <hr class="mb-4">

    <button class="btn btn-primary btn-lg btn-block">Search</button>

  </form>

  <script>
    var onSubmitFunc = function(e){
      e.preventDefault();
      e.stopPropagation();
      if( e.stopImmediatePropagation ){
        e.stopImmediatePropagation();
      }

      var propertyBidAsk = this["propertyBidAsk"].value.trim();
      var propertyPayment = this["propertyPayment"].value.trim();
      var propertyType = this["propertyType"].value.trim();
      var city = this['city'].value.trim();
      var min_price = this["min_price"].value.trim();
      var max_price = this["max_price"].value.trim();
      var price = min_price + "_" + max_price;
      var min_quadrature = this["min_quadrature"].value.trim();
      var max_quadrature = this["max_quadrature"].value.trim();
      var quadrature = min_quadrature + "_" + max_quadrature;

      url = propertyBidAsk.length === 0 ? '' : ( '/' + encodeURIComponent(propertyBidAsk) );
      url += propertyPayment.length === 0 ? '' : ( '/' +  encodeURIComponent(propertyPayment) );
      url += propertyType.length === 0 ? '' : ( '/' + encodeURIComponent(propertyType) );
      url += city.length === 0 ? '' : ( '/' + encodeURIComponent(city) );
      url += min_price.length === 0 ? '' : ( '/min_price' + "=" + encodeURIComponent(min_price) );
      url += max_price.length === 0 ? '' : ( '-max_price' + "=" + encodeURIComponent(max_price) );
      url += min_quadrature.length === 0 ? '' : ( '/min_quadrature' + "=" + encodeURIComponent(min_quadrature) );
      url += max_quadrature.length === 0 ? '' : ( '-max_quadrature' + "=" + encodeURIComponent(max_quadrature) );

      window.location.href = this.action + url;

    }

    document.addEventListener( 'DOMContentLoaded', function(){
    var srch = document.getElementById("searchForm");
    srch.addEventListener('submit', onSubmitFunc, false);
    }, false );

  </script>

</div>

</div>

CategoryController.php

<?php
namespace App\Http\Controllers;

use App\Http\Controllers\Controller;
use App\Property;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Redirect;

class CategoryController extends Controller
{
    public function index()
    {
        $data = \DB::table('properties');
        return view('categories.search', compact('data'));
    }

    public function search($propertyBidAsk, $propertyType, $propertyPayment, $city, $price, $quadrature, Request $request, Property $property)
    {
        $category = $property->category;

        $results = null;

        if (!empty($request->city)) {
        $results = Property::all()->where('city', 'LIKE', "%" . $request->city . "%");
        }

        if (!empty($request->min_price) && !empty($request->max_price )) {
        $results = Property::all()->where('price', '>=', $request->min_price)->where('price', '<=', $request->max_price);
        }

        if (!empty($request->min_quadrature) && !empty($request->max_quadrature )) {
        $results = Property::all()->where('quadrature', '>=', $request->min_quadrature)->where('price', '<=', $request->max_quadrature);
        }

        return view('categories.search', compact('category', 'results'));
    }

}

web.php

<?php

//search

Route::get('/search', 'CategoryController@index');

Route::get('/search/{propertyBidAsk}/{propertyPayment}/{propertyType}/
{city}/{price}/{quadrature}', 'CategoryController@search');

Answer 1

Please replace your search action in CategoryController with the following code:

public function search($propertyBidAsk, $propertyType, $propertyPayment, $city, $price, $quadrature, Request $request, Property $property)
{
    $category = $property->category;

    $property_obj = Property::query();
    if (!empty($request->city)) {
        $property_obj->where('city', 'LIKE', "%" . $request->city . "%");
    }

    if (!empty($request->min_price) && !empty($request->max_price)) {
        $property_obj->where('price', '>=', $request->min_price)->where('price', '<=', $request->max_price);
    }

    if (!empty($request->min_quadrature) && !empty($request->max_quadrature)) {
        $property_obj->where('quadrature', '>=', $request->min_quadrature)->where('price', '<=', $request->max_quadrature);
    }

    $results =  $property_obj->get();

    return view('categories.search', compact('category', 'results'));
}