I am using lumen framework for the api service. Now suppose, I have a select query where I am assigning an alias to a particular column and then in the order by clause I am using the same column name instead of alias one
So, after doing this why do I get the error as the column is not found. But if I use same alias as of original column name in order by clause for that column then it works fine or if I use table name prefix in the order by clause CASE statement then also works fine.
I am using query builder and getting this error as column not found but if I use the same things in SQL server then both things work fine
Update
public function getQuery(): Builder
{
$query = DB::table('view_all_user');
$query->select(
[
'view_all_user.first_name',
'view_all_user.last_name',
'view_all_user.email',
'view_all_user.status',
]
);
if ($this->hasCourseAssigned) {
$query->addSelect(
[
DB::raw(
"CASE
WHEN course_date IS NOT NULL AND course_date != ''
THEN 'Complete'
ELSE ''
END AS course_status"
),
DB::raw('DATE(course_date) AS course_datedone'),
]
);
}
$query->where([
['view_all_user.status', '!=', 'inactive'],
]);
return $query;
}
Above one is normal query, so now whenever I sort the column using course_status, my api would be handling the adddition of OrderBy clause to the particular permission block of the query so ultimately it would be using OrderBy clause for the course status like this
select 'view_all_user.first_name', 'view_all_user.last_name', 'view_all_user.email', 'view_all_user.status',
CASE
WHEN course_date IS NOT NULL AND course_date != ''
THEN 'Complete'
ELSE ''
END AS course_status, DATE(course_date) AS course_datedone
from 'view_all_user'
where ('view_all_user.status', '!=', 'inactive')
order by
CASE
WHEN course_date IS NOT NULL AND course_date != ''
THEN 'Complete'
ELSE ''
END asc limit 1000 offset 0
But I am getting the error
#53 {main} {"exception":"[object] (Illuminate\\Database\\QueryException(code: 42S22): SQLSTATE[42S22]: Column not found: 1054 Unknown column 'course_date' in 'order clause'
