Display no result after merging 2 table and do a where statement in Laravel

Question

I trying to merge 2 table of the same database as follow

$this->make_model_list = DB::table('car__models')->select(
            "car__makes.id",
            "car__makes.name AS make_name",
            "car__models.id AS model_id",
            "car__models.name AS model_name",
            "car__models.car__make_id",
            DB::raw("CONCAT(`car__makes`.`name`,' ',`car__models`.`name`) AS make_model")
        )->leftJoin('car__makes', 'car__makes.id', '=', 'car__models.car__make_id')
        ->get();

And then I do a where statement as follow

$this->makemodels = $this->make_model_list->where('make_model', 'like', '%' . $this->makemodel . '%');

But it return [].

Your advices to this is greatly appreciated. Thanks

Answer 1

It looks like you're adding a where() after your get()

The get() executes your query and returns a collection of objects.

It's okay to call a where() on a collection but the parameters you've given your where() are more appropriate for a DB query.

I would suggest you drop the get() from your query. (For the sake of good variable naming you should then call make_model_list make_model_list_query)

$this->make_model_list_query = DB::table('car__models')->select(
        "car__makes.id",
        "car__makes.name AS make_name",
        "car__models.id AS model_id",
        "car__models.name AS model_name",
        "car__models.car__make_id",
        DB::raw("CONCAT(`car__makes`.`name`,' ',`car__models`.`name`) AS make_model")
    )->leftJoin('car__makes', 'car__makes.id', '=', 'car__models.car__make_id');

And then call the get() after you've added your where()

$this->make_model_list_query->where('make_model', 'like', '%' . $this->makemodel . '%')->get();

It would probably make everything much easier for you if you created eloquent models for you tables and relationships between the models so you can take advantage of the relationship and scopes that can make your filtering much cleaner. Models handle a huge amount of the query writing for you.

EDIT:

Having not noticed that your where was on your concatenated value at first I missed that your query would throw a SQL error. You can't run your where on the aliased output of your concat. So you would need to put the concat in the where clause too. Like so:

$this->make_model_list_query->where(DB::raw("CONCAT(`car__makes`.`name`,' ',`car__models`.`name`)"), 'like', '%' . $this->makemodel . '%')->get();

Be sure to check out the links I added in the comments for Eloquent Models.