select name,
sum(rating),
group_concat(salary order by salary desc)
from keywords
join salaries on salaries.keywordId = keywords.id
where profession = "backend developer"
group by name
order by sum(rating) DESC
limit 10;
1. git, [ {100k: 10}, {"120k": 16}, {"150k": 5}, {"200k": 2} ]
2. mysql ...
3. ...
CREATE TABLE vacancies
(
id BIGSERIAL PRIMARY KEY,
name TEXT,
description TEXT,
search_vector tsvector GENERATED ALWAYS AS (to_tsvector('russian', name || ' ' || description)) stored
);
CREATE INDEX vacancies_search_vector_idx ON vacancies USING gin(search_vector);
explain (analyze, timing off, summary off) SELECT * FROM vacancies WHERE search_vector @@ to_tsquery('russian', 'программист');
$users = User::query()->whereHas('keywords', function($query) use($vacancy){
return $query->whereIn('keyword', $vacancy->keywords->pluck('keyword'));
})->get();