Laravel Query builder: left join, select, and where not working

Question

Unfortunately the Laravel query builder seems not in a good mood!

Here is my table structure:

Table user structure

ID, USERNAME, NAME, USER_ROLE_MODULE_ID

Table user_role_module structure

ID, NAME 

And this is my query builder statement

DB::table('user')
->leftJoin('user_role_module', 'user.user_role_module_id', '=', 'user_role_module.id')
->select('user.*', 'user_role_module.name as user_role')
->where("name LIKE '%Jhon%' ") "or other example" ->where("user_role LIKE '%admin%' ")
->paginate(10);

Goal:

I just want to get all data from user + user role name from user_role_module AND also applies in WHERE statement so I can use it for search feature.

I am getting result from WHERE statement without specifying table name. because it already stated which table and column to select from SELECT statement.

The problem:

1. if I search for name, it return error ambiguous column //Laravel is confusing whether taking name from user table or user_role_module table!

2. if I search for user_role, then the column doesn't exist

Why is it? What is the solution?

Answer 1

Change this:

DB::table('user')
->leftJoin('user_role_module', 'user.user_role_module_id', '=', 'user_role_module.id')
->select('user.*', 'user_role_module.name as user_role')
->where("name LIKE '%Jhon%' ") "or other example" ->where("user_role LIKE '%admin%' ")
->paginate(10);

with this:

DB::table('user')
->leftJoin('user_role_module', 'user.user_role_module_id', '=', 'user_role_module.id')
->where('user.name', 'like', '%Jhon%')
->select('user.*', 'user_role_module.name as user_role')
->get();

and make a custom pagination because select is not working with paginate.

Answer 2

The problem:

user.NAME                 <-- both these columns are called NAME
user_role_module.NAME     <--

You have multiple problems with your Laravel query. As @Viktor correctly pointed out, your join condition is wrong. You should be joining user.USER_ROLE_MODULE_ID to user_role_module.id. But the immediate cause of your error I believe is that both tables have a NAME column.

Just specify the columns you want using both table and column name. This way, no column name would be ambiguous.

DB::table('user')
->leftJoin('user_role_module', 'user.USER_ROLE_MODULE_ID', '=', 'user_role_module.id')
->select('user.ID', 'user.USERNAME', 'user.NAME AS userName',
         'user_role_module.name as user_role')
->where("name LIKE '%John%'")
->paginate(10);