user3844579 user3844579 - 9 days ago 6
PHP Question

Laravel search query

I'm trying to do a search query, that searches in services titles, descriptions and companies names (company has services, if company name matches, it returns the services).

I have a search field, that gets passed to my controller.
I have tried it like this:

$query = Service::select('id','company_id','title','description',price);
$search = $request->input('search',null);
$query = is_null($search) ? $query : $query->where('title','LIKE','%'.$search.'%')->orWhere('description','LIKE','%'.$search.'%')->orWhereHas('company', function ($q) use ($search)
{
$q->where('name','LIKE','%'.$search.'%')->get();
});


$services= $query->paginate(5);


But i get an error that Unknown column 'services.company_id' in 'where clause' (SQL: select * from
companies
where
services
.
company_id
=
companies
.
id
and
name
LIKE %xx% and
companies
.
deleted_at
is null)

How should I do this search?

Thanks!

Update:

class Service extends Model
{
use SoftDeletes;

protected $dates = ['deleted_at'];

public function company () {

return $this->belongsTo('Company');

}
}

class Company extends Model
{
use SoftDeletes;

protected $dates = ['deleted_at'];

public function services() {
return $this->hasMany('Service');
}
}

Schema::create('services', function (Blueprint $table) {
$table->increments('id');
$table->integer('company_id');
$table->integer('service_category_id');
$table->integer('server_id');
$table->string('title');
$table->string('description');
$table->string('icon');
$table->boolean('accepts_swaps');
$table->integer('qty_available');
$table->double('price_usd', 10, 6);
$table->timestamps();
$table->softDeletes();
});

Schema::create('companies', function (Blueprint $table) {
$table->increments('id');
$table->integer('owner_id');
$table->string('name');
$table->string('email');
$table->string('paypal_email')->nullable();
$table->string('skrill_email')->nullable();
$table->string('contact_email')->nullable();
$table->string('phone')->nullable();
$table->integer('city_id')->nullable();
$table->string('short_description')->nullable();
$table->text('description')->nullable();
$table->integer('subscription_id')->nullable();
$table->timestamp('subscription_end_date')->nullable();
$table->string('avatar')->default("img/default/user-avatar-128.min.png");
$table->integer('highlighted_game_id')->nullable()->default(null);
$table->timestamps();
$table->softDeletes();
});

Answer

The get function in where clause is causing the problem. Try by removing the get.

So your code will look as:

$query = Service::select('id','company_id','title','description',price);
$search = $request->input('search',null);
$query = is_null($search)  ? $query : $query->where('title','LIKE','%'.$search.'%')->orWhere('description','LIKE','%'.$search.'%')->orWhereHas('company', function ($q) use ($search)
    {
        $q->where('name','LIKE','%'.$search.'%');
    });


$services= $query->paginate(5);