From 49e0473753c2e4e2e02e30c17a7793657b71363f Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jes=C3=BAs=20Espino?= Date: Thu, 27 Sep 2018 16:15:41 +0200 Subject: MM-11567: Autocomplete search in: for DMs and GMs (#9430) * MM-11567: Autocomplete search in: for DMs and GMs * Adding unit tests * Allowing to search Direct Messages in the autocompletion * Fix it in TE --- store/sqlstore/channel_store.go | 71 ++++++++++++++++++++++++++++++++++------- 1 file changed, 59 insertions(+), 12 deletions(-) (limited to 'store/sqlstore/channel_store.go') diff --git a/store/sqlstore/channel_store.go b/store/sqlstore/channel_store.go index b4d3bd8fa..7a9e4e687 100644 --- a/store/sqlstore/channel_store.go +++ b/store/sqlstore/channel_store.go @@ -1691,14 +1691,14 @@ func (s SqlChannelStore) AutocompleteInTeam(teamId string, term string, includeD var channels model.ChannelList - if likeClause, likeTerm := s.buildLIKEClause(term); likeClause == "" { + if likeClause, likeTerm := s.buildLIKEClause(term, "Name, DisplayName, Purpose"); likeClause == "" { if _, err := s.GetReplica().Select(&channels, fmt.Sprintf(queryFormat, ""), map[string]interface{}{"TeamId": teamId}); err != nil { result.Err = model.NewAppError("SqlChannelStore.AutocompleteInTeam", "store.sql_channel.search.app_error", nil, "term="+term+", "+", "+err.Error(), http.StatusInternalServerError) } } else { // Using a UNION results in index_merge and fulltext queries and is much faster than the ref // query you would get using an OR of the LIKE and full-text clauses. - fulltextClause, fulltextTerm := s.buildFulltextClause(term) + fulltextClause, fulltextTerm := s.buildFulltextClause(term, "Name, DisplayName, Purpose") likeQuery := fmt.Sprintf(queryFormat, "AND "+likeClause) fulltextQuery := fmt.Sprintf(queryFormat, "AND "+fulltextClause) query := fmt.Sprintf("(%v) UNION (%v) LIMIT 50", likeQuery, fulltextQuery) @@ -1730,7 +1730,7 @@ func (s SqlChannelStore) AutocompleteInTeamForSearch(teamId string, userId strin JOIN ChannelMembers AS CM ON CM.ChannelId = C.Id WHERE - C.TeamId = :TeamId + (C.TeamId = :TeamId OR (C.TeamId = '' AND C.Type = 'G')) AND CM.UserId = :UserId ` + deleteFilter + ` %v @@ -1738,14 +1738,14 @@ func (s SqlChannelStore) AutocompleteInTeamForSearch(teamId string, userId strin var channels model.ChannelList - if likeClause, likeTerm := s.buildLIKEClause(term); likeClause == "" { + if likeClause, likeTerm := s.buildLIKEClause(term, "Name, DisplayName, Purpose"); likeClause == "" { if _, err := s.GetReplica().Select(&channels, fmt.Sprintf(queryFormat, ""), map[string]interface{}{"TeamId": teamId, "UserId": userId}); err != nil { result.Err = model.NewAppError("SqlChannelStore.AutocompleteInTeamForSearch", "store.sql_channel.search.app_error", nil, "term="+term+", "+", "+err.Error(), http.StatusInternalServerError) } } else { // Using a UNION results in index_merge and fulltext queries and is much faster than the ref // query you would get using an OR of the LIKE and full-text clauses. - fulltextClause, fulltextTerm := s.buildFulltextClause(term) + fulltextClause, fulltextTerm := s.buildFulltextClause(term, "Name, DisplayName, Purpose") likeQuery := fmt.Sprintf(queryFormat, "AND "+likeClause) fulltextQuery := fmt.Sprintf(queryFormat, "AND "+fulltextClause) query := fmt.Sprintf("(%v) UNION (%v) LIMIT 50", likeQuery, fulltextQuery) @@ -1755,6 +1755,14 @@ func (s SqlChannelStore) AutocompleteInTeamForSearch(teamId string, userId strin } } + directChannels, err := s.autocompleteInTeamForSearchDirectMessages(userId, term) + if err != nil { + result.Err = err + return + } + + channels = append(channels, directChannels...) + sort.Slice(channels, func(a, b int) bool { return strings.ToLower(channels[a].DisplayName) < strings.ToLower(channels[b].DisplayName) }) @@ -1762,6 +1770,48 @@ func (s SqlChannelStore) AutocompleteInTeamForSearch(teamId string, userId strin }) } +func (s SqlChannelStore) autocompleteInTeamForSearchDirectMessages(userId string, term string) ([]*model.Channel, *model.AppError) { + queryFormat := ` + SELECT + C.*, + OtherUsers.Username as DisplayName + FROM + Channels AS C + JOIN + ChannelMembers AS CM ON CM.ChannelId = C.Id + INNER JOIN ( + SELECT + ICM.ChannelId AS ChannelId, IU.Username AS Username + FROM + Users as IU + JOIN + ChannelMembers AS ICM ON ICM.UserId = IU.Id + WHERE + IU.Id != :UserId + %v + ) AS OtherUsers ON OtherUsers.ChannelId = C.Id + WHERE + C.Type = 'D' + AND CM.UserId = :UserId + LIMIT 50` + + var channels model.ChannelList + + if likeClause, likeTerm := s.buildLIKEClause(term, "IU.Username, IU.Nickname"); likeClause == "" { + if _, err := s.GetReplica().Select(&channels, fmt.Sprintf(queryFormat, ""), map[string]interface{}{"UserId": userId}); err != nil { + return nil, model.NewAppError("SqlChannelStore.AutocompleteInTeamForSearch", "store.sql_channel.search.app_error", nil, "term="+term+", "+", "+err.Error(), http.StatusInternalServerError) + } + } else { + query := fmt.Sprintf(queryFormat, "AND "+likeClause) + + if _, err := s.GetReplica().Select(&channels, query, map[string]interface{}{"UserId": userId, "LikeTerm": likeTerm}); err != nil { + return nil, model.NewAppError("SqlChannelStore.AutocompleteInTeamForSearch", "store.sql_channel.search.app_error", nil, "term="+term+", "+", "+err.Error(), http.StatusInternalServerError) + } + } + + return channels, nil +} + func (s SqlChannelStore) SearchInTeam(teamId string, term string, includeDeleted bool) store.StoreChannel { return store.Do(func(result *store.StoreResult) { deleteFilter := "AND DeleteAt = 0" @@ -1814,9 +1864,8 @@ func (s SqlChannelStore) SearchMore(userId string, teamId string, term string) s }) } -func (s SqlChannelStore) buildLIKEClause(term string) (likeClause, likeTerm string) { +func (s SqlChannelStore) buildLIKEClause(term string, searchColumns string) (likeClause, likeTerm string) { likeTerm = term - searchColumns := "Name, DisplayName, Purpose" // These chars must be removed from the like query. for _, c := range ignoreLikeSearchChar { @@ -1847,12 +1896,10 @@ func (s SqlChannelStore) buildLIKEClause(term string) (likeClause, likeTerm stri return } -func (s SqlChannelStore) buildFulltextClause(term string) (fulltextClause, fulltextTerm string) { +func (s SqlChannelStore) buildFulltextClause(term string, searchColumns string) (fulltextClause, fulltextTerm string) { // Copy the terms as we will need to prepare them differently for each search type. fulltextTerm = term - searchColumns := "Name, DisplayName, Purpose" - // These chars must be treated as spaces in the fulltext query. for _, c := range spaceFulltextSearchChar { fulltextTerm = strings.Replace(fulltextTerm, c, " ", -1) @@ -1891,13 +1938,13 @@ func (s SqlChannelStore) buildFulltextClause(term string) (fulltextClause, fullt func (s SqlChannelStore) performSearch(searchQuery string, term string, parameters map[string]interface{}) store.StoreResult { result := store.StoreResult{} - likeClause, likeTerm := s.buildLIKEClause(term) + likeClause, likeTerm := s.buildLIKEClause(term, "Name, DisplayName, Purpose") if likeTerm == "" { // If the likeTerm is empty after preparing, then don't bother searching. searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", "", 1) } else { parameters["LikeTerm"] = likeTerm - fulltextClause, fulltextTerm := s.buildFulltextClause(term) + fulltextClause, fulltextTerm := s.buildFulltextClause(term, "Name, DisplayName, Purpose") parameters["FulltextTerm"] = fulltextTerm searchQuery = strings.Replace(searchQuery, "SEARCH_CLAUSE", "AND ("+likeClause+" OR "+fulltextClause+")", 1) } -- cgit v1.2.3-1-g7c22