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()); }