Question 245098 in Laravel Question posted in
The official Laravel Documentation can be found here.

how do I get value of a column from join table when search item is sent laravel

I have two tables users and categories. users table has a foreign key linked to categories table. categories table has 2 columns id and name

I want to get the value of name when a search_item is sent

    if(!empty($request->get("search_item"))) {
        $users = User::join("categories", "users.business_category", "=", "categories.id")
            ->where($request->get("search_item"), function ($q) use ($request) {
                $q->where('users.full_name', 'like', '%' . $request->get("search_item") . '%')
                    ->orWhere('categories.business_category', 'like', '%' . $request->get("search_item") . '%')
                    ->orWhere('users.email', 'like', '%' . $request->get("search_item") . '%')
                    ->orWhere('users.phone', 'like', '%' . $request->get("search_item") . '%');
            })
            ->where($request->status, function ($q) use ($request) {
                $q->where('Role', "Artisan");
            })
            ->paginate(20);
    }else{

        $users = User::where('Role','=', "Artisan")->orderBy('id','DESC')->paginate(20);
    }
    return $users;

but I am getting this error

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'fashion' in 'where clause' (SQL: select count(*) as aggregate from `users` inner join `categories` on `users`.`business_category` = `categories`.`id` where `fashion` = (select * where `users`.`full_name` like %fashion% or `users`.`email` like %fashion% or `users`.`phone` like %fashion%) and `` = (select * where `Role` = Artisan))
Tags:

2

Answers


  1. 0

    as per @krisgjika answer

    your error is here ->where($request->get("search_item"), …), the name of the column can’t be the VALUE you are searching for

    Login or Signup to reply.
  2. 0

    The error is due to the way you’re constructing your dynamic query. You’re trying to use the value from $request->get("search_item") as the column name in your where clause, which is causing the Unknown column 'fashion' error.

    if (!empty($request->get("search_item"))) {
    $users = User::join("categories", "users.business_category", "=", "categories.id")
        ->where(function ($query) use ($request) {
            $searchItem = '%' . $request->get("search_item") . '%';
            $query->where('users.full_name', 'like', $searchItem)
                ->orWhere('categories.name', 'like', $searchItem)
                ->orWhere('users.email', 'like', $searchItem)
                ->orWhere('users.phone', 'like', $searchItem);
        })
        ->where('Role', '=', "Artisan")
        ->paginate(20);
} else {
    $users = User::where('Role', '=', "Artisan")->orderBy('id', 'DESC')->paginate(20);
}

return $users;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search