Problem with displaying multiple results from search form in Laravel

Question

I building an application for ads/properties in Laravel. I have a search form with filters that are checkboxes. I am having a problem when I select two or more options from the same request for example house, flat, room that are PropertyType I get error

Column not found: 1054 Unknown column 'category' in 'where clause' (SQL: select count(*) as aggregate from properties where category in (house, flat, room))

I have three tables.

properties (id, location, price)
properties_categories (id, property_id, category_id)
categories (id, category)

house, flat, room are values in the category column in the categories table. I would like when I click multiple checkboxes to get correct results from database. Any help is appreciated. Here is my code:

CategoryController.php

<?php
namespace App\Http\Controllers;

use App\Property;
use Illuminate\Http\Request;
use App\Http\Controllers\Controller;

class CategoryController extends Controller
{
    public function search(Request $request, Property $property)
    {
        $category = $property->category;

        $query = Property::query();

        //PROPERTY TYPE- HERE IS PROBLEM!!!
        if ($request->has('propertyType')) {
            $request->get('propertyType');
        }

        $propertyType = $request->input('propertyType');

        if (!empty($propertyType)) {
            $query->whereIn('category', $propertyType);

        }

        $results = $query->paginate(6);

        return view('startpage', compact('category', 'results', 'request', 'user.photo', 'photos', 'propertyBidAsk', 'propertyPayment', 'propertyType'));
     }
}

startpage.blade.php

  <form id="searchForm" method="GET" action="/search">
  <div class="col-md-2 mb-6">
                        <h5>Property type</h4>
                        <div class="d-block my-3 ">
                            <div class="custom-control custom-checkbox">
                                <input id="house" name="propertyType[]" value="house" type="checkbox" class="custom-control-input" @if (!empty($propertyType) && in_array('house', $propertyType)) checked="checked" @endif>
                                <label class="custom-control-label" for="house">house</label>
                            </div>
                            <div class="custom-control custom-checkbox">
                                <input id="flat" name="propertyType[]" value="flat" type="checkbox" class="custom-control-input" @if (!empty($propertyType) && in_array('flat', $propertyType)) checked="checked" @endif>
                                <label class="custom-control-label" for="flat">flat</label>
                            </div>
                            <div class="custom-control custom-checkbox">
                                <input id="room" name="propertyType[]" value="room" type="checkbox" class="custom-control-input" @if (!empty($propertyType) && in_array('room', $propertyType)) checked="checked" @endif>
                                <label class="custom-control-label" for="room">room</label>
                            </div>

                            </div>
                        </div>
                    </div>
      <button class="btn btn-primary btn-lg btn-block" type="submit">Search</button>
  </form>

Property.php

public function category()
{
    return $this->belongsToMany(Category::class, 'properties_categories')->orderBy('priority', 'asc');
}

Category.php

public function property()
{
    return $this->belongsToMany(Property::class);
}

mrmar mrmar · Accepted Answer · 2019-07-22T12:11:37

I added this code in controller and now it works. Hope it can help someone in the future.

//PROPERTY TYPE!!!
    if ($request->has('propertyType')) {
        $request->get('propertyType');
    }

    $propertyType = $request->input('propertyType');

    if (!empty($propertyType)) {
        $query->whereHas('category', function($query) use ($propertyType) {
            $query->whereIn('category', $propertyType);
        });
    }

Problem with displaying multiple results from search form in Laravel

1 Answers