public List <Provider> SuggestProviders(string query)
{
var providers = Providers.FromSql(@"
SELECT * FROM (
SELECT ""provider"".*, COUNT(*) AS cnt
FROM ""provider""
JOIN ""course"" ON ""course"".""ProviderId"" = ""provider"".""Id"" OR ""course"".""AccreditingProviderId"" = ""provider"".""Id""
WHERE (to_tsvector('english', ""provider"".""Name"") @@ to_tsquery('english', quote_literal(@query) || ':*')) IS TRUE
OR (to_tsvector('english', ""provider"".""ProviderCode"") @@ to_tsquery('english', quote_literal(@query) || ':*')) IS TRUE
GROUP BY ""provider"".""Id"") AS sub
ORDER BY ""cnt"" DESC",
new NpgsqlParameter("@query", query.Replace(@"\", "")))
.ToList();
/*
* When there are multiple providers with common words in, the provider a user is searching for might
* not appear on the list.
*
* An example of this was "Teach North", where it would match on things like "Teaching Alliance (North)"
*
* The below prioritises providers whose name starts with the given search query
*/
var providersStartingWithQuery = providers.Where(p => p.Name.ToLower().StartsWith(query.ToLower()));
var providersNotStartingWithquery = providers.Where(p => !p.Name.ToLower().StartsWith(query.ToLower()));
return(providersStartingWithQuery.Concat(providersNotStartingWithquery).Take(5).ToList());
}
public Provider GetProvider(string name, string providerCode)
{
return(Providers.FromSql(@"
SELECT i.* from provider i
JOIN organisation_provider oi on i.id = oi.provider_id
JOIN organisation o on oi.organisation_id = o.id
JOIN organisation_user ou on o.id = ou.organisation_id
JOIN ""user"" u on ou.user_id = u.id
WHERE lower(u.email) = lower(@email)
AND lower(i.provider_code) = lower(@providercode)",
new NpgsqlParameter("email", name),
new NpgsqlParameter("providercode", providerCode))
.Where(p => p.RecruitmentCycle.Year == RecruitmentCycle.CurrentYear)
.SingleOrDefault());
}
