Issue
I am trying to filter the results of my query to get only the results with RANK <=5;
I have this Laravel statement
$data = DB::table('n2ns')->select('first_name', 'last_name','name_abb_mappings.team', 'name_abb_mappings.cc','project_id',
DB::raw('projects.request_name as project_name'),DB::raw('projects.request_from as end_date'),
DB::raw('projects.request_to as start_date'), DB::raw('sum(hit) as points'),
DB::raw("GROUP_CONCAT(concat(word, '->'), concat(hit, ' ') separator ' ' ) as word"),
DB::raw("row_number() OVER (partition by project_id order by sum(hit) desc) AS 'RANK'"))
->join('projects','n2ns.project_id','projects.request_id')
->leftJoin('name_abb_mappings','n2ns.abbreviation','name_abb_mappings.id')
->groupBy('abbreviation','project_id')
//->having('RANK','<=','5')
->get();
Notice the commented having clause.
This statement produces me this sql query:
select `first_name`, `last_name`, `name_abb_mappings`.`team`,
`name_abb_mappings`.`cc`, `project_id`,
projects.request_name as project_name,
projects.request_from as end_date, projects.request_to as start_date,
sum(hit) as points, GROUP_CONCAT(concat(word, '->'), concat(hit, ' ') separator ' ' ) as word, row_number() OVER (partition by project_id order by sum(hit) desc) AS 'RANK'
from `n2ns` inner join `projects` on `n2ns`.`project_id` = `projects`.`request_id`
left join `name_abb_mappings` on `n2ns`.`abbreviation` = `name_abb_mappings`.`id`
group by `abbreviation`, `project_id`
When i use the having clause it throws me this error:
You cannot use the alias ‘RANK’ of an expression containing a window function in this context
So the solution is simply to put the whole query in a subquery and filter like:
select * from (query) as data
where data.RANK <=5;
But how can i do that in laravel? I tried diffrent ways but none is working. Or better said i am not getting them right. Can someone give me an advice? Appreciate your help.
Solution
This might be not the best solution but it solved my problem.
It’s possible to write complete raw queries without using the in build functions of query builder. So i did it like this.
$data=DB::select(DB::raw('select* from(select
`first_name`, `last_name`, `name_abb_mappings`.`team`,
`name_abb_mappings`.`cc`, `project_id`, projects.request_name as project_name,
projects.request_from as end_date, projects.request_to as start_date, sum(hit) as points,
GROUP_CONCAT(concat(word, "->"), concat(hit, " ") separator " " ) as "word",
row_number() OVER (partition by project_id order by sum(hit) desc) AS "RANK"
from n2ns inner join projects on `n2ns`.`project_id` = `projects`.`request_id`
left join `name_abb_mappings` on `n2ns`.`abbreviation` = `name_abb_mappings`.`id`
group by `abbreviation`, `project_id`
order by projects.request_name) as d
where d.RANK <= 5'));
Answered By – Yassine
This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0