Level 48
You need to add the table name before the column name in the select.
$query3 = DB::table('rbac_modules')->select('rbac_access_rights.mr_role_id as 0', 'rbac_access_rights.mr_role_nm as NULL'.......,
Dec 6, 2017
13
Level 1
Eloquent query in laravel 5.5
I have written the following query to fetch records from database
$query4 = DB::table('rbac_access_rights')->select('rbac_roles.mr_role_id', 'rbac_roles.mr_role_nm',
'rbac_roles.mr_user','rbac_forms.fm_form_id','rbac_forms.fm_form_nm','rbac_forms.fm_form_type',
'rbac_modules.mm_module_id','rbac_modules.mm_module_nm',
'rbac_access_rights.mar_read','rbac_access_rights.mar_write',
'rbac_access_rights.mar_modify','rbac_access_rights.mar_delete',
'rbac_access_rights.mar_show_on_menu')
->join('rbac_roles', 'rbac_access_rights.mar_role_id', '=', 'rbac_roles.mr_role_id')
->join('rbac_modules as md','rbac_modules.mm_module_id', '=', 'frm.fm_form_module_id')
->join('rbac_forms as frm','md.mm_module_id', '=', 'rbac_forms.fm_form_module_id')
->where([
['rbac_roles.mr_status', '=', '1'],
['rbac_forms.fm_status', '=', '1'],
['rbac_modules.mm_status', '=','1'],
['rbac_roles.mr_role_id', '=', $_POST['rolename']]
]);
$query3 = DB::table('rbac_modules')->select('rbac_roles.mr_role_id as 0', 'rbac_roles.mr_role_nm as NULL',
'rbac.roles.mr_user as null','rbac_forms.fm_form_id','rbac_forms.fm_form_nm','
rbac_forms.fm_form_type','rbac_modules.mm_module_id','rbac_modules.mm_module_nm',
'rbac_access_rights.mar_read as 0','rbac_access_rights.mar_write as 0','rbac_access_rights.mar_modify as 0','rbac_access_rights.mar_delete as 0','rbac_access_rights.mar_show_on_menu as 0')
->join('rbac_forms', 'rbac_modules.mm_module_id', '=', 'rbac_forms.fm_form_module_id')
->where([
['rbac_forms.fm_status', '=', '1'],
['rbac_modules.mm_status', '=', '1'] ])
->whereNotIn('fm_form_id', function($query)
{
$query->select('rbac_forms.fm_form_id')
->from('rbac_access_rights')
->join('rbac_roles', 'rbac_access_rights.mar_role_id', '=', 'rbac_roles.mr_role_id')
->join('rbac_modules as rbmd','rbac_modules.mm_module_id', '=', 'rbfm.fm_form_module_id')
->join('rbac_forms as rbfm', 'rbmd.mm_module_id', '=', 'rbac_forms.fm_form_module_id')
->where([
['rbac_forms.fm_status', '=', '1'],
['rbac_modules.mm_status', '=', '1'],
['rbac_roles.mr_role_id', '=', $_POST['rolename']]
]);
}) ->union ($query4)
->get();
Now It is giving me error
Column not found: 1054 Unknown column 'mr_role_id' in 'field list' What is wrong with this query
Level 1
I have added the table name, but the same error
Level 104
Your problem is you have aliased mr_role_id AS 0
Level 1
I have updated the query
Level 104
'rbac_roles.mr_role_id as 0'
Level 48
@tykus i think thats not a problem, only the field name will be 0 instead of mr_role_id
Edit:
['mr_role_id', '=', $_POST['rolename']]
Missing your table prefix
['rbac_roles.mr_role_id', '=', $_POST['rolename']]
Level 1
I have again updated the query , still the same error
Level 104
If you want 0 as the value for mr_role_id then the SQL syntax should be
SELECT 0 AS mr_role_id ...
not
SELECT mr_role_id AS 0 ...
This applies throughout $query3
Level 48
Do you have a column with the name 'mr_role_id' in your database table 'rbac_roles' ?
Level 1
@mvd , yes this column is available in the database table
Level 1
any body has the solution for this error
Level 14
You're aliasing several columns all with a zero or null in some cases, but provided that worked, in query3 for example:
$query3 = DB::table('rbac_modules')->select('rbac_roles.mr_role_id as 0',
You don't specify the rbac_roles table in the joins, so it can't find any associated fields with it, the roles table is only being specified in a subquery
Level 104
Did you try this:
If you want 0 as the value for mr_role_id then the SQL syntax should be
SELECT 0 AS mr_role_idinstead ofSELECT mr_role_id AS 0
Please or to participate in this conversation.