github.com/xzl8028/xenia-server@v0.0.0-20190809101854-18450a97da63/store/sqlstore/group_store.go (about)

     1  // Copyright (c) 2018-present Xenia, Inc. All Rights Reserved.
     2  // See License.txt for license information.
     3  
     4  package sqlstore
     5  
     6  import (
     7  	"database/sql"
     8  	"fmt"
     9  	"net/http"
    10  	"strings"
    11  
    12  	sq "github.com/Masterminds/squirrel"
    13  
    14  	"github.com/xzl8028/xenia-server/model"
    15  	"github.com/xzl8028/xenia-server/store"
    16  )
    17  
    18  type selectType int
    19  
    20  const (
    21  	selectGroups selectType = iota
    22  	selectCountGroups
    23  )
    24  
    25  type groupTeam struct {
    26  	model.GroupSyncable
    27  	TeamId string `db:"TeamId"`
    28  }
    29  
    30  type groupChannel struct {
    31  	model.GroupSyncable
    32  	ChannelId string `db:"ChannelId"`
    33  }
    34  
    35  type groupTeamJoin struct {
    36  	groupTeam
    37  	TeamDisplayName string `db:"TeamDisplayName"`
    38  	TeamType        string `db:"TeamType"`
    39  }
    40  
    41  type groupChannelJoin struct {
    42  	groupChannel
    43  	ChannelDisplayName string `db:"ChannelDisplayName"`
    44  	TeamDisplayName    string `db:"TeamDisplayName"`
    45  	TeamType           string `db:"TeamType"`
    46  	ChannelType        string `db:"ChannelType"`
    47  	TeamID             string `db:"TeamId"`
    48  }
    49  
    50  type SqlGroupStore struct {
    51  	SqlStore
    52  }
    53  
    54  func NewSqlGroupStore(sqlStore SqlStore) store.GroupStore {
    55  	s := &SqlGroupStore{SqlStore: sqlStore}
    56  	for _, db := range sqlStore.GetAllConns() {
    57  		groups := db.AddTableWithName(model.Group{}, "UserGroups").SetKeys(false, "Id")
    58  		groups.ColMap("Id").SetMaxSize(26)
    59  		groups.ColMap("Name").SetMaxSize(model.GroupNameMaxLength).SetUnique(true)
    60  		groups.ColMap("DisplayName").SetMaxSize(model.GroupDisplayNameMaxLength)
    61  		groups.ColMap("Description").SetMaxSize(model.GroupDescriptionMaxLength)
    62  		groups.ColMap("Source").SetMaxSize(model.GroupSourceMaxLength)
    63  		groups.ColMap("RemoteId").SetMaxSize(model.GroupRemoteIDMaxLength)
    64  		groups.SetUniqueTogether("Source", "RemoteId")
    65  
    66  		groupMembers := db.AddTableWithName(model.GroupMember{}, "GroupMembers").SetKeys(false, "GroupId", "UserId")
    67  		groupMembers.ColMap("GroupId").SetMaxSize(26)
    68  		groupMembers.ColMap("UserId").SetMaxSize(26)
    69  
    70  		groupTeams := db.AddTableWithName(groupTeam{}, "GroupTeams").SetKeys(false, "GroupId", "TeamId")
    71  		groupTeams.ColMap("GroupId").SetMaxSize(26)
    72  		groupTeams.ColMap("TeamId").SetMaxSize(26)
    73  
    74  		groupChannels := db.AddTableWithName(groupChannel{}, "GroupChannels").SetKeys(false, "GroupId", "ChannelId")
    75  		groupChannels.ColMap("GroupId").SetMaxSize(26)
    76  		groupChannels.ColMap("ChannelId").SetMaxSize(26)
    77  	}
    78  	return s
    79  }
    80  
    81  func (s *SqlGroupStore) CreateIndexesIfNotExists() {
    82  	s.CreateIndexIfNotExists("idx_groupmembers_create_at", "GroupMembers", "CreateAt")
    83  	s.CreateIndexIfNotExists("idx_usergroups_remote_id", "UserGroups", "RemoteId")
    84  	s.CreateIndexIfNotExists("idx_usergroups_delete_at", "UserGroups", "DeleteAt")
    85  }
    86  
    87  func (s *SqlGroupStore) Create(group *model.Group) store.StoreChannel {
    88  	return store.Do(func(result *store.StoreResult) {
    89  
    90  		if len(group.Id) != 0 {
    91  			result.Err = model.NewAppError("SqlGroupStore.GroupCreate", "model.group.id.app_error", nil, "", http.StatusBadRequest)
    92  			return
    93  		}
    94  
    95  		if err := group.IsValidForCreate(); err != nil {
    96  			result.Err = err
    97  			return
    98  		}
    99  
   100  		group.Id = model.NewId()
   101  		group.CreateAt = model.GetMillis()
   102  		group.UpdateAt = group.CreateAt
   103  
   104  		if err := s.GetMaster().Insert(group); err != nil {
   105  			if IsUniqueConstraintError(err, []string{"Name", "groups_name_key"}) {
   106  				result.Err = model.NewAppError("SqlGroupStore.GroupCreate", "store.sql_group.unique_constraint", nil, err.Error(), http.StatusInternalServerError)
   107  			} else {
   108  				result.Err = model.NewAppError("SqlGroupStore.GroupCreate", "store.insert_error", nil, err.Error(), http.StatusInternalServerError)
   109  			}
   110  			return
   111  		}
   112  
   113  		result.Data = group
   114  		return
   115  	})
   116  }
   117  
   118  func (s *SqlGroupStore) Get(groupId string) store.StoreChannel {
   119  	return store.Do(func(result *store.StoreResult) {
   120  
   121  		var group *model.Group
   122  		if err := s.GetReplica().SelectOne(&group, "SELECT * from UserGroups WHERE Id = :Id", map[string]interface{}{"Id": groupId}); err != nil {
   123  			if err == sql.ErrNoRows {
   124  				result.Err = model.NewAppError("SqlGroupStore.GroupGet", "store.sql_group.no_rows", nil, err.Error(), http.StatusNotFound)
   125  			} else {
   126  				result.Err = model.NewAppError("SqlGroupStore.GroupGet", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
   127  			}
   128  			return
   129  		}
   130  
   131  		result.Data = group
   132  		return
   133  	})
   134  }
   135  
   136  func (s *SqlGroupStore) GetByIDs(groupIDs []string) ([]*model.Group, *model.AppError) {
   137  	var groups []*model.Group
   138  	query := s.getQueryBuilder().Select("*").From("UserGroups").Where(sq.Eq{"Id": groupIDs})
   139  	queryString, args, err := query.ToSql()
   140  	if err != nil {
   141  		return nil, model.NewAppError("SqlGroupStore.GetByIDs", "store.sql_group.app_error", nil, err.Error(), http.StatusInternalServerError)
   142  	}
   143  	if _, err := s.GetReplica().Select(&groups, queryString, args...); err != nil {
   144  		return nil, model.NewAppError("SqlGroupStore.GetByIDs", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
   145  	}
   146  	return groups, nil
   147  }
   148  
   149  func (s *SqlGroupStore) GetByRemoteID(remoteID string, groupSource model.GroupSource) store.StoreChannel {
   150  	return store.Do(func(result *store.StoreResult) {
   151  
   152  		var group *model.Group
   153  		if err := s.GetReplica().SelectOne(&group, "SELECT * from UserGroups WHERE RemoteId = :RemoteId AND Source = :Source", map[string]interface{}{"RemoteId": remoteID, "Source": groupSource}); err != nil {
   154  			if err == sql.ErrNoRows {
   155  				result.Err = model.NewAppError("SqlGroupStore.GroupGetByRemoteID", "store.sql_group.no_rows", nil, err.Error(), http.StatusNotFound)
   156  			} else {
   157  				result.Err = model.NewAppError("SqlGroupStore.GroupGetByRemoteID", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
   158  			}
   159  			return
   160  		}
   161  
   162  		result.Data = group
   163  		return
   164  	})
   165  }
   166  
   167  func (s *SqlGroupStore) GetAllBySource(groupSource model.GroupSource) store.StoreChannel {
   168  	return store.Do(func(result *store.StoreResult) {
   169  
   170  		var groups []*model.Group
   171  
   172  		if _, err := s.GetReplica().Select(&groups, "SELECT * from UserGroups WHERE DeleteAt = 0 AND Source = :Source", map[string]interface{}{"Source": groupSource}); err != nil {
   173  			result.Err = model.NewAppError("SqlGroupStore.GroupGetAllBySource", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
   174  			return
   175  		}
   176  
   177  		result.Data = groups
   178  
   179  		return
   180  	})
   181  }
   182  
   183  func (s *SqlGroupStore) Update(group *model.Group) store.StoreChannel {
   184  	return store.Do(func(result *store.StoreResult) {
   185  
   186  		var retrievedGroup *model.Group
   187  		if err := s.GetMaster().SelectOne(&retrievedGroup, "SELECT * FROM UserGroups WHERE Id = :Id", map[string]interface{}{"Id": group.Id}); err != nil {
   188  			if err == sql.ErrNoRows {
   189  				result.Err = model.NewAppError("SqlGroupStore.GroupUpdate", "store.sql_group.no_rows", nil, "id="+group.Id+","+err.Error(), http.StatusNotFound)
   190  			} else {
   191  				result.Err = model.NewAppError("SqlGroupStore.GroupUpdate", "store.select_error", nil, "id="+group.Id+","+err.Error(), http.StatusInternalServerError)
   192  			}
   193  			return
   194  		}
   195  
   196  		// If updating DeleteAt it can only be to 0
   197  		if group.DeleteAt != retrievedGroup.DeleteAt && group.DeleteAt != 0 {
   198  			result.Err = model.NewAppError("SqlGroupStore.GroupUpdate", "model.group.delete_at.app_error", nil, "", http.StatusInternalServerError)
   199  			return
   200  		}
   201  
   202  		// Reset these properties, don't update them based on input
   203  		group.CreateAt = retrievedGroup.CreateAt
   204  		group.UpdateAt = model.GetMillis()
   205  
   206  		if err := group.IsValidForUpdate(); err != nil {
   207  			result.Err = err
   208  			return
   209  		}
   210  
   211  		rowsChanged, err := s.GetMaster().Update(group)
   212  		if err != nil {
   213  			result.Err = model.NewAppError("SqlGroupStore.GroupUpdate", "store.update_error", nil, err.Error(), http.StatusInternalServerError)
   214  			return
   215  		}
   216  		if rowsChanged != 1 {
   217  			result.Err = model.NewAppError("SqlGroupStore.GroupUpdate", "store.sql_group.no_rows_changed", nil, "", http.StatusInternalServerError)
   218  			return
   219  		}
   220  
   221  		result.Data = group
   222  		return
   223  	})
   224  }
   225  
   226  func (s *SqlGroupStore) Delete(groupID string) store.StoreChannel {
   227  	return store.Do(func(result *store.StoreResult) {
   228  
   229  		var group *model.Group
   230  		if err := s.GetReplica().SelectOne(&group, "SELECT * from UserGroups WHERE Id = :Id AND DeleteAt = 0", map[string]interface{}{"Id": groupID}); err != nil {
   231  			if err == sql.ErrNoRows {
   232  				result.Err = model.NewAppError("SqlGroupStore.GroupDelete", "store.sql_group.no_rows", nil, "Id="+groupID+", "+err.Error(), http.StatusNotFound)
   233  			} else {
   234  				result.Err = model.NewAppError("SqlGroupStore.GroupDelete", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
   235  			}
   236  
   237  			return
   238  		}
   239  
   240  		time := model.GetMillis()
   241  		group.DeleteAt = time
   242  		group.UpdateAt = time
   243  
   244  		if _, err := s.GetMaster().Update(group); err != nil {
   245  			result.Err = model.NewAppError("SqlGroupStore.GroupDelete", "store.update_error", nil, err.Error(), http.StatusInternalServerError)
   246  		}
   247  
   248  		result.Data = group
   249  		return
   250  	})
   251  }
   252  
   253  func (s *SqlGroupStore) GetMemberUsers(groupID string) store.StoreChannel {
   254  	return store.Do(func(result *store.StoreResult) {
   255  
   256  		var groupMembers []*model.User
   257  
   258  		query := `
   259  		SELECT
   260  			Users.*
   261  		FROM
   262  			GroupMembers
   263  			JOIN Users ON Users.Id = GroupMembers.UserId
   264  		WHERE
   265  			GroupMembers.DeleteAt = 0
   266  			AND Users.DeleteAt = 0
   267  			AND GroupId = :GroupId`
   268  
   269  		if _, err := s.GetReplica().Select(&groupMembers, query, map[string]interface{}{"GroupId": groupID}); err != nil {
   270  			result.Err = model.NewAppError("SqlGroupStore.GroupGetAllBySource", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
   271  			return
   272  		}
   273  
   274  		result.Data = groupMembers
   275  
   276  		return
   277  	})
   278  }
   279  
   280  func (s *SqlGroupStore) GetMemberUsersPage(groupID string, offset int, limit int) store.StoreChannel {
   281  	return store.Do(func(result *store.StoreResult) {
   282  
   283  		var groupMembers []*model.User
   284  
   285  		query := `
   286  		SELECT
   287  			Users.*
   288  		FROM
   289  			GroupMembers
   290  			JOIN Users ON Users.Id = GroupMembers.UserId
   291  		WHERE
   292  			GroupMembers.DeleteAt = 0
   293  			AND Users.DeleteAt = 0
   294  			AND GroupId = :GroupId
   295  		ORDER BY
   296  			GroupMembers.CreateAt DESC
   297  		LIMIT
   298  			:Limit
   299  		OFFSET
   300  			:Offset`
   301  
   302  		if _, err := s.GetReplica().Select(&groupMembers, query, map[string]interface{}{"GroupId": groupID, "Limit": limit, "Offset": offset}); err != nil {
   303  			result.Err = model.NewAppError("SqlGroupStore.GroupGetMemberUsersPage", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
   304  			return
   305  		}
   306  
   307  		result.Data = groupMembers
   308  
   309  		return
   310  	})
   311  }
   312  
   313  func (s *SqlGroupStore) GetMemberCount(groupID string) store.StoreChannel {
   314  	return store.Do(func(result *store.StoreResult) {
   315  
   316  		var count int64
   317  		var err error
   318  
   319  		query := `
   320  		SELECT
   321  			count(*)
   322  		FROM
   323  			GroupMembers
   324  		WHERE
   325  			GroupMembers.GroupId = :GroupId`
   326  
   327  		if count, err = s.GetReplica().SelectInt(query, map[string]interface{}{"GroupId": groupID}); err != nil {
   328  			result.Err = model.NewAppError("SqlGroupStore.GroupGetMemberUsersPage", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
   329  			return
   330  		}
   331  
   332  		result.Data = count
   333  
   334  		return
   335  	})
   336  }
   337  
   338  func (s *SqlGroupStore) UpsertMember(groupID string, userID string) store.StoreChannel {
   339  	return store.Do(func(result *store.StoreResult) {
   340  
   341  		member := &model.GroupMember{
   342  			GroupId:  groupID,
   343  			UserId:   userID,
   344  			CreateAt: model.GetMillis(),
   345  		}
   346  
   347  		if result.Err = member.IsValid(); result.Err != nil {
   348  			return
   349  		}
   350  
   351  		var retrievedGroup *model.Group
   352  		if err := s.GetMaster().SelectOne(&retrievedGroup, "SELECT * FROM UserGroups WHERE Id = :Id", map[string]interface{}{"Id": groupID}); err != nil {
   353  			result.Err = model.NewAppError("SqlGroupStore.GroupCreateOrRestoreMember", "store.insert_error", nil, "group_id="+member.GroupId+"user_id="+member.UserId+","+err.Error(), http.StatusInternalServerError)
   354  			return
   355  		}
   356  
   357  		var retrievedMember *model.GroupMember
   358  		if err := s.GetMaster().SelectOne(&retrievedMember, "SELECT * FROM GroupMembers WHERE GroupId = :GroupId AND UserId = :UserId", map[string]interface{}{"GroupId": member.GroupId, "UserId": member.UserId}); err != nil {
   359  			if err != sql.ErrNoRows {
   360  				result.Err = model.NewAppError("SqlGroupStore.GroupCreateOrRestoreMember", "store.select_error", nil, "group_id="+member.GroupId+"user_id="+member.UserId+","+err.Error(), http.StatusInternalServerError)
   361  				return
   362  			}
   363  		}
   364  
   365  		if retrievedMember == nil {
   366  			if err := s.GetMaster().Insert(member); err != nil {
   367  				if IsUniqueConstraintError(err, []string{"GroupId", "UserId", "groupmembers_pkey", "PRIMARY"}) {
   368  					result.Err = model.NewAppError("SqlGroupStore.GroupCreateOrRestoreMember", "store.sql_group.uniqueness_error", nil, "group_id="+member.GroupId+", user_id="+member.UserId+", "+err.Error(), http.StatusBadRequest)
   369  					return
   370  				}
   371  				result.Err = model.NewAppError("SqlGroupStore.GroupCreateOrRestoreMember", "store.insert_error", nil, "group_id="+member.GroupId+", user_id="+member.UserId+", "+err.Error(), http.StatusInternalServerError)
   372  				return
   373  			}
   374  		} else {
   375  			member.DeleteAt = 0
   376  			var rowsChanged int64
   377  			var err error
   378  			if rowsChanged, err = s.GetMaster().Update(member); err != nil {
   379  				result.Err = model.NewAppError("SqlGroupStore.GroupCreateOrRestoreMember", "store.update_error", nil, "group_id="+member.GroupId+", user_id="+member.UserId+", "+err.Error(), http.StatusInternalServerError)
   380  				return
   381  			}
   382  			if rowsChanged != 1 {
   383  				result.Err = model.NewAppError("SqlGroupStore.GroupCreateOrRestoreMember", "store.sql_group.no_rows_changed", nil, "", http.StatusInternalServerError)
   384  				return
   385  			}
   386  		}
   387  
   388  		result.Data = member
   389  		return
   390  	})
   391  }
   392  
   393  func (s *SqlGroupStore) DeleteMember(groupID string, userID string) store.StoreChannel {
   394  	return store.Do(func(result *store.StoreResult) {
   395  
   396  		var retrievedMember *model.GroupMember
   397  		if err := s.GetMaster().SelectOne(&retrievedMember, "SELECT * FROM GroupMembers WHERE GroupId = :GroupId AND UserId = :UserId AND DeleteAt = 0", map[string]interface{}{"GroupId": groupID, "UserId": userID}); err != nil {
   398  			if err == sql.ErrNoRows {
   399  				result.Err = model.NewAppError("SqlGroupStore.GroupDeleteMember", "store.sql_group.no_rows", nil, "group_id="+groupID+"user_id="+userID+","+err.Error(), http.StatusNotFound)
   400  				return
   401  			}
   402  			result.Err = model.NewAppError("SqlGroupStore.GroupDeleteMember", "store.select_error", nil, "group_id="+groupID+"user_id="+userID+","+err.Error(), http.StatusInternalServerError)
   403  			return
   404  		}
   405  
   406  		retrievedMember.DeleteAt = model.GetMillis()
   407  
   408  		if _, err := s.GetMaster().Update(retrievedMember); err != nil {
   409  			result.Err = model.NewAppError("SqlGroupStore.GroupDeleteMember", "store.update_error", nil, err.Error(), http.StatusInternalServerError)
   410  			return
   411  		}
   412  
   413  		result.Data = retrievedMember
   414  		return
   415  	})
   416  }
   417  
   418  func (s *SqlGroupStore) CreateGroupSyncable(groupSyncable *model.GroupSyncable) (*model.GroupSyncable, *model.AppError) {
   419  	if err := groupSyncable.IsValid(); err != nil {
   420  		return nil, err
   421  	}
   422  
   423  	// Reset values that shouldn't be updatable by parameter
   424  	groupSyncable.DeleteAt = 0
   425  	groupSyncable.CreateAt = model.GetMillis()
   426  	groupSyncable.UpdateAt = groupSyncable.CreateAt
   427  
   428  	var insertErr error
   429  
   430  	switch groupSyncable.Type {
   431  	case model.GroupSyncableTypeTeam:
   432  		if _, err := s.Team().Get(groupSyncable.SyncableId); err != nil {
   433  			return nil, err
   434  		}
   435  
   436  		insertErr = s.GetMaster().Insert(groupSyncableToGroupTeam(groupSyncable))
   437  	case model.GroupSyncableTypeChannel:
   438  		if _, err := s.Channel().Get(groupSyncable.SyncableId, false); err != nil {
   439  			return nil, err
   440  		}
   441  
   442  		insertErr = s.GetMaster().Insert(groupSyncableToGroupChannel(groupSyncable))
   443  	default:
   444  		return nil, model.NewAppError("SqlGroupStore.GroupCreateGroupSyncable", "model.group_syncable.type.app_error", nil, "group_id="+groupSyncable.GroupId+", syncable_id="+groupSyncable.SyncableId, http.StatusInternalServerError)
   445  	}
   446  
   447  	if insertErr != nil {
   448  		return nil, model.NewAppError("SqlGroupStore.GroupCreateGroupSyncable", "store.insert_error", nil, "group_id="+groupSyncable.GroupId+", syncable_id="+groupSyncable.SyncableId+", "+insertErr.Error(), http.StatusInternalServerError)
   449  	}
   450  
   451  	return groupSyncable, nil
   452  }
   453  
   454  func (s *SqlGroupStore) GetGroupSyncable(groupID string, syncableID string, syncableType model.GroupSyncableType) (*model.GroupSyncable, *model.AppError) {
   455  	groupSyncable, err := s.getGroupSyncable(groupID, syncableID, syncableType)
   456  	if err != nil {
   457  		if err == sql.ErrNoRows {
   458  			return nil, model.NewAppError("SqlGroupStore.GroupGetGroupSyncable", "store.sql_group.no_rows", nil, err.Error(), http.StatusNotFound)
   459  		}
   460  		return nil, model.NewAppError("SqlGroupStore.GroupGetGroupSyncable", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
   461  	}
   462  
   463  	return groupSyncable, nil
   464  }
   465  
   466  func (s *SqlGroupStore) getGroupSyncable(groupID string, syncableID string, syncableType model.GroupSyncableType) (*model.GroupSyncable, error) {
   467  	var err error
   468  	var result interface{}
   469  
   470  	switch syncableType {
   471  	case model.GroupSyncableTypeTeam:
   472  		result, err = s.GetMaster().Get(groupTeam{}, groupID, syncableID)
   473  	case model.GroupSyncableTypeChannel:
   474  		result, err = s.GetMaster().Get(groupChannel{}, groupID, syncableID)
   475  	}
   476  
   477  	if err != nil {
   478  		return nil, err
   479  	}
   480  
   481  	if result == nil {
   482  		return nil, sql.ErrNoRows
   483  	}
   484  
   485  	groupSyncable := model.GroupSyncable{}
   486  	switch syncableType {
   487  	case model.GroupSyncableTypeTeam:
   488  		groupTeam := result.(*groupTeam)
   489  		groupSyncable.SyncableId = groupTeam.TeamId
   490  		groupSyncable.GroupId = groupTeam.GroupId
   491  		groupSyncable.AutoAdd = groupTeam.AutoAdd
   492  		groupSyncable.CreateAt = groupTeam.CreateAt
   493  		groupSyncable.DeleteAt = groupTeam.DeleteAt
   494  		groupSyncable.UpdateAt = groupTeam.UpdateAt
   495  		groupSyncable.Type = syncableType
   496  	case model.GroupSyncableTypeChannel:
   497  		groupChannel := result.(*groupChannel)
   498  		groupSyncable.SyncableId = groupChannel.ChannelId
   499  		groupSyncable.GroupId = groupChannel.GroupId
   500  		groupSyncable.AutoAdd = groupChannel.AutoAdd
   501  		groupSyncable.CreateAt = groupChannel.CreateAt
   502  		groupSyncable.DeleteAt = groupChannel.DeleteAt
   503  		groupSyncable.UpdateAt = groupChannel.UpdateAt
   504  		groupSyncable.Type = syncableType
   505  	default:
   506  		return nil, fmt.Errorf("unable to convert syncableType: %s", syncableType.String())
   507  	}
   508  
   509  	return &groupSyncable, nil
   510  }
   511  
   512  func (s *SqlGroupStore) GetAllGroupSyncablesByGroupId(groupID string, syncableType model.GroupSyncableType) ([]*model.GroupSyncable, *model.AppError) {
   513  	args := map[string]interface{}{"GroupId": groupID}
   514  
   515  	appErrF := func(msg string) *model.AppError {
   516  		return model.NewAppError("SqlGroupStore.GroupGetAllGroupSyncablesByGroup", "store.select_error", nil, msg, http.StatusInternalServerError)
   517  	}
   518  
   519  	groupSyncables := []*model.GroupSyncable{}
   520  
   521  	switch syncableType {
   522  	case model.GroupSyncableTypeTeam:
   523  		sqlQuery := `
   524  			SELECT
   525  				GroupTeams.*,
   526  				Teams.DisplayName AS TeamDisplayName,
   527  				Teams.Type AS TeamType
   528  			FROM
   529  				GroupTeams
   530  				JOIN Teams ON Teams.Id = GroupTeams.TeamId
   531  			WHERE
   532  				GroupId = :GroupId AND GroupTeams.DeleteAt = 0`
   533  
   534  		results := []*groupTeamJoin{}
   535  		_, err := s.GetMaster().Select(&results, sqlQuery, args)
   536  		if err != nil {
   537  			return nil, appErrF(err.Error())
   538  		}
   539  		for _, result := range results {
   540  			groupSyncable := &model.GroupSyncable{
   541  				SyncableId:      result.TeamId,
   542  				GroupId:         result.GroupId,
   543  				AutoAdd:         result.AutoAdd,
   544  				CreateAt:        result.CreateAt,
   545  				DeleteAt:        result.DeleteAt,
   546  				UpdateAt:        result.UpdateAt,
   547  				Type:            syncableType,
   548  				TeamDisplayName: result.TeamDisplayName,
   549  				TeamType:        result.TeamType,
   550  			}
   551  			groupSyncables = append(groupSyncables, groupSyncable)
   552  		}
   553  	case model.GroupSyncableTypeChannel:
   554  		sqlQuery := `
   555  			SELECT
   556  				GroupChannels.*,
   557  				Channels.DisplayName AS ChannelDisplayName,
   558  				Teams.DisplayName AS TeamDisplayName,
   559  				Channels.Type As ChannelType,
   560  				Teams.Type As TeamType,
   561  				Teams.Id AS TeamId
   562  			FROM
   563  				GroupChannels
   564  				JOIN Channels ON Channels.Id = GroupChannels.ChannelId
   565  				JOIN Teams ON Teams.Id = Channels.TeamId
   566  			WHERE
   567  				GroupId = :GroupId AND GroupChannels.DeleteAt = 0`
   568  
   569  		results := []*groupChannelJoin{}
   570  		_, err := s.GetMaster().Select(&results, sqlQuery, args)
   571  		if err != nil {
   572  			return nil, appErrF(err.Error())
   573  		}
   574  		for _, result := range results {
   575  			groupSyncable := &model.GroupSyncable{
   576  				SyncableId:         result.ChannelId,
   577  				GroupId:            result.GroupId,
   578  				AutoAdd:            result.AutoAdd,
   579  				CreateAt:           result.CreateAt,
   580  				DeleteAt:           result.DeleteAt,
   581  				UpdateAt:           result.UpdateAt,
   582  				Type:               syncableType,
   583  				ChannelDisplayName: result.ChannelDisplayName,
   584  				ChannelType:        result.ChannelType,
   585  				TeamDisplayName:    result.TeamDisplayName,
   586  				TeamType:           result.TeamType,
   587  				TeamID:             result.TeamID,
   588  			}
   589  			groupSyncables = append(groupSyncables, groupSyncable)
   590  		}
   591  	}
   592  
   593  	return groupSyncables, nil
   594  }
   595  
   596  func (s *SqlGroupStore) UpdateGroupSyncable(groupSyncable *model.GroupSyncable) (*model.GroupSyncable, *model.AppError) {
   597  	retrievedGroupSyncable, err := s.getGroupSyncable(groupSyncable.GroupId, groupSyncable.SyncableId, groupSyncable.Type)
   598  	if err != nil {
   599  		if err == sql.ErrNoRows {
   600  			return nil, model.NewAppError("SqlGroupStore.GroupUpdateGroupSyncable", "store.sql_group.no_rows", nil, err.Error(), http.StatusInternalServerError)
   601  		}
   602  		return nil, model.NewAppError("SqlGroupStore.GroupUpdateGroupSyncable", "store.select_error", nil, "GroupId="+groupSyncable.GroupId+", SyncableId="+groupSyncable.SyncableId+", SyncableType="+groupSyncable.Type.String()+", "+err.Error(), http.StatusInternalServerError)
   603  	}
   604  
   605  	if err := groupSyncable.IsValid(); err != nil {
   606  		return nil, err
   607  	}
   608  
   609  	// If updating DeleteAt it can only be to 0
   610  	if groupSyncable.DeleteAt != retrievedGroupSyncable.DeleteAt && groupSyncable.DeleteAt != 0 {
   611  		return nil, model.NewAppError("SqlGroupStore.GroupUpdateGroupSyncable", "model.group.delete_at.app_error", nil, "", http.StatusInternalServerError)
   612  	}
   613  
   614  	// Reset these properties, don't update them based on input
   615  	groupSyncable.CreateAt = retrievedGroupSyncable.CreateAt
   616  	groupSyncable.UpdateAt = model.GetMillis()
   617  
   618  	switch groupSyncable.Type {
   619  	case model.GroupSyncableTypeTeam:
   620  		_, err = s.GetMaster().Update(groupSyncableToGroupTeam(groupSyncable))
   621  	case model.GroupSyncableTypeChannel:
   622  		_, err = s.GetMaster().Update(groupSyncableToGroupChannel(groupSyncable))
   623  	default:
   624  		return nil, model.NewAppError("SqlGroupStore.GroupUpdateGroupSyncable", "model.group_syncable.type.app_error", nil, "group_id="+groupSyncable.GroupId+", syncable_id="+groupSyncable.SyncableId+", "+err.Error(), http.StatusInternalServerError)
   625  	}
   626  
   627  	if err != nil {
   628  		return nil, model.NewAppError("SqlGroupStore.GroupUpdateGroupSyncable", "store.update_error", nil, err.Error(), http.StatusInternalServerError)
   629  	}
   630  
   631  	return groupSyncable, nil
   632  }
   633  
   634  func (s *SqlGroupStore) DeleteGroupSyncable(groupID string, syncableID string, syncableType model.GroupSyncableType) (*model.GroupSyncable, *model.AppError) {
   635  	groupSyncable, err := s.getGroupSyncable(groupID, syncableID, syncableType)
   636  	if err != nil {
   637  		if err == sql.ErrNoRows {
   638  			return nil, model.NewAppError("SqlGroupStore.GroupDeleteGroupSyncable", "store.sql_group.no_rows", nil, "Id="+groupID+", "+err.Error(), http.StatusNotFound)
   639  		}
   640  		return nil, model.NewAppError("SqlGroupStore.GroupDeleteGroupSyncable", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
   641  	}
   642  
   643  	if groupSyncable.DeleteAt != 0 {
   644  		return nil, model.NewAppError("SqlGroupStore.GroupDeleteGroupSyncable", "store.sql_group.group_syncable_already_deleted", nil, "group_id="+groupID+"syncable_id="+syncableID, http.StatusBadRequest)
   645  	}
   646  
   647  	time := model.GetMillis()
   648  	groupSyncable.DeleteAt = time
   649  	groupSyncable.UpdateAt = time
   650  
   651  	switch groupSyncable.Type {
   652  	case model.GroupSyncableTypeTeam:
   653  		_, err = s.GetMaster().Update(groupSyncableToGroupTeam(groupSyncable))
   654  	case model.GroupSyncableTypeChannel:
   655  		_, err = s.GetMaster().Update(groupSyncableToGroupChannel(groupSyncable))
   656  	default:
   657  		return nil, model.NewAppError("SqlGroupStore.GroupDeleteGroupSyncable", "model.group_syncable.type.app_error", nil, "group_id="+groupSyncable.GroupId+", syncable_id="+groupSyncable.SyncableId+", "+err.Error(), http.StatusInternalServerError)
   658  	}
   659  
   660  	if err != nil {
   661  		return nil, model.NewAppError("SqlGroupStore.GroupDeleteGroupSyncable", "store.update_error", nil, err.Error(), http.StatusInternalServerError)
   662  	}
   663  
   664  	return groupSyncable, nil
   665  }
   666  
   667  // TeamMembersToAdd returns a slice of UserTeamIDPair that need newly created memberships
   668  // based on the groups configurations.
   669  //
   670  // Typically since will be the last successful group sync time.
   671  func (s *SqlGroupStore) TeamMembersToAdd(since int64) ([]*model.UserTeamIDPair, *model.AppError) {
   672  	sql := `
   673  		SELECT
   674  			GroupMembers.UserId, GroupTeams.TeamId
   675  		FROM
   676  			GroupMembers
   677  			JOIN GroupTeams
   678  			ON GroupTeams.GroupId = GroupMembers.GroupId
   679  			JOIN UserGroups ON UserGroups.Id = GroupMembers.GroupId
   680  			JOIN Teams ON Teams.Id = GroupTeams.TeamId
   681  			LEFT OUTER JOIN TeamMembers
   682  			ON
   683  				TeamMembers.TeamId = GroupTeams.TeamId
   684  				AND TeamMembers.UserId = GroupMembers.UserId
   685  		WHERE
   686  			TeamMembers.UserId IS NULL
   687  			AND UserGroups.DeleteAt = 0
   688  			AND GroupTeams.DeleteAt = 0
   689  			AND GroupTeams.AutoAdd = true
   690  			AND GroupMembers.DeleteAt = 0
   691  			AND Teams.DeleteAt = 0
   692  			AND (GroupMembers.CreateAt >= :Since
   693  			OR GroupTeams.UpdateAt >= :Since)`
   694  
   695  	var teamMembers []*model.UserTeamIDPair
   696  
   697  	_, err := s.GetReplica().Select(&teamMembers, sql, map[string]interface{}{"Since": since})
   698  	if err != nil {
   699  		return nil, model.NewAppError("SqlGroupStore.TeamMembersToAdd", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
   700  	}
   701  
   702  	return teamMembers, nil
   703  }
   704  
   705  // ChannelMembersToAdd returns a slice of UserChannelIDPair that need newly created memberships
   706  // based on the groups configurations.
   707  //
   708  // Typically since will be the last successful group sync time.
   709  func (s *SqlGroupStore) ChannelMembersToAdd(since int64) ([]*model.UserChannelIDPair, *model.AppError) {
   710  	sql := `
   711  		SELECT
   712  			GroupMembers.UserId, GroupChannels.ChannelId
   713  		FROM
   714  			GroupMembers
   715  			JOIN GroupChannels ON GroupChannels.GroupId = GroupMembers.GroupId
   716  			JOIN UserGroups ON UserGroups.Id = GroupMembers.GroupId
   717  			JOIN Channels ON Channels.Id = GroupChannels.ChannelId
   718  			LEFT OUTER JOIN ChannelMemberHistory
   719  			ON
   720  				ChannelMemberHistory.ChannelId = GroupChannels.ChannelId
   721  				AND ChannelMemberHistory.UserId = GroupMembers.UserId
   722  		WHERE
   723  			ChannelMemberHistory.UserId IS NULL
   724  			AND ChannelMemberHistory.LeaveTime IS NULL
   725  			AND UserGroups.DeleteAt = 0
   726  			AND GroupChannels.DeleteAt = 0
   727  			AND GroupChannels.AutoAdd = true
   728  			AND GroupMembers.DeleteAt = 0
   729  			AND Channels.DeleteAt = 0
   730  			AND (GroupMembers.CreateAt >= :Since
   731  			OR GroupChannels.UpdateAt >= :Since)`
   732  
   733  	var channelMembers []*model.UserChannelIDPair
   734  
   735  	_, err := s.GetReplica().Select(&channelMembers, sql, map[string]interface{}{"Since": since})
   736  	if err != nil {
   737  		return nil, model.NewAppError("SqlGroupStore.ChannelMembersToAdd", "store.select_error", nil, "", http.StatusInternalServerError)
   738  	}
   739  
   740  	return channelMembers, nil
   741  }
   742  
   743  func groupSyncableToGroupTeam(groupSyncable *model.GroupSyncable) *groupTeam {
   744  	return &groupTeam{
   745  		GroupSyncable: *groupSyncable,
   746  		TeamId:        groupSyncable.SyncableId,
   747  	}
   748  }
   749  
   750  func groupSyncableToGroupChannel(groupSyncable *model.GroupSyncable) *groupChannel {
   751  	return &groupChannel{
   752  		GroupSyncable: *groupSyncable,
   753  		ChannelId:     groupSyncable.SyncableId,
   754  	}
   755  }
   756  
   757  // TeamMembersToRemove returns all team members that should be removed based on group constraints.
   758  func (s *SqlGroupStore) TeamMembersToRemove() ([]*model.TeamMember, *model.AppError) {
   759  	sql := `
   760  		SELECT
   761  			TeamMembers.TeamId,
   762  			TeamMembers.UserId,
   763  			TeamMembers.Roles,
   764  			TeamMembers.DeleteAt,
   765  			TeamMembers.SchemeUser,
   766  			TeamMembers.SchemeAdmin,
   767  			(TeamMembers.SchemeGuest IS NOT NULL AND TeamMembers.SchemeGuest) as SchemeGuest
   768  		FROM
   769  			TeamMembers
   770  			JOIN Teams ON Teams.Id = TeamMembers.TeamId
   771  			LEFT JOIN Bots ON Bots.UserId = TeamMembers.UserId
   772  		WHERE
   773  			TeamMembers.DeleteAt = 0
   774  			AND Teams.DeleteAt = 0
   775  			AND Teams.GroupConstrained = TRUE
   776  			AND Bots.UserId IS NULL
   777  			AND (TeamMembers.TeamId, TeamMembers.UserId)
   778  			NOT IN (
   779  				SELECT
   780  					Teams.Id AS TeamId, GroupMembers.UserId
   781  				FROM
   782  					Teams
   783  					JOIN GroupTeams ON GroupTeams.TeamId = Teams.Id
   784  					JOIN UserGroups ON UserGroups.Id = GroupTeams.GroupId
   785  					JOIN GroupMembers ON GroupMembers.GroupId = UserGroups.Id
   786  				WHERE
   787  					Teams.GroupConstrained = TRUE
   788  					AND GroupTeams.DeleteAt = 0
   789  					AND UserGroups.DeleteAt = 0
   790  					AND Teams.DeleteAt = 0
   791  					AND GroupMembers.DeleteAt = 0
   792  				GROUP BY
   793  					Teams.Id,
   794  					GroupMembers.UserId)`
   795  
   796  	var teamMembers []*model.TeamMember
   797  
   798  	_, err := s.GetReplica().Select(&teamMembers, sql)
   799  	if err != nil {
   800  		return nil, model.NewAppError("SqlGroupStore.TeamMembersToRemove", "store.select_error", nil, "", http.StatusInternalServerError)
   801  	}
   802  
   803  	return teamMembers, nil
   804  }
   805  
   806  func (s *SqlGroupStore) CountGroupsByChannel(channelId string, opts model.GroupSearchOpts) (int64, *model.AppError) {
   807  	countQuery := s.groupsBySyncableBaseQuery(model.GroupSyncableTypeChannel, selectCountGroups, channelId, opts)
   808  
   809  	countQueryString, args, err := countQuery.ToSql()
   810  	if err != nil {
   811  		return int64(0), model.NewAppError("SqlGroupStore.CountGroupsByChannel", "store.sql_group.app_error", nil, err.Error(), http.StatusInternalServerError)
   812  	}
   813  
   814  	count, err := s.GetReplica().SelectInt(countQueryString, args...)
   815  	if err != nil {
   816  		return int64(0), model.NewAppError("SqlGroupStore.CountGroupsByChannel", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
   817  	}
   818  
   819  	return count, nil
   820  }
   821  
   822  func (s *SqlGroupStore) GetGroupsByChannel(channelId string, opts model.GroupSearchOpts) ([]*model.Group, *model.AppError) {
   823  	query := s.groupsBySyncableBaseQuery(model.GroupSyncableTypeChannel, selectGroups, channelId, opts)
   824  
   825  	if opts.PageOpts != nil {
   826  		offset := uint64(opts.PageOpts.Page * opts.PageOpts.PerPage)
   827  		query = query.OrderBy("ug.DisplayName").Limit(uint64(opts.PageOpts.PerPage)).Offset(offset)
   828  	}
   829  
   830  	queryString, args, err := query.ToSql()
   831  	if err != nil {
   832  		return nil, model.NewAppError("SqlGroupStore.GetGroupsByChannel", "store.sql_group.app_error", nil, err.Error(), http.StatusInternalServerError)
   833  	}
   834  
   835  	var groups []*model.Group
   836  
   837  	_, err = s.GetReplica().Select(&groups, queryString, args...)
   838  	if err != nil {
   839  		return nil, model.NewAppError("SqlGroupStore.GetGroupsByChannel", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
   840  	}
   841  
   842  	return groups, nil
   843  }
   844  
   845  // ChannelMembersToRemove returns all channel members that should be removed based on group constraints.
   846  func (s *SqlGroupStore) ChannelMembersToRemove() ([]*model.ChannelMember, *model.AppError) {
   847  	sql := `
   848  		SELECT
   849  			ChannelMembers.ChannelId,
   850  			ChannelMembers.UserId,
   851  			ChannelMembers.LastViewedAt,
   852  			ChannelMembers.MsgCount,
   853  			ChannelMembers.MentionCount,
   854  			ChannelMembers.NotifyProps,
   855  			ChannelMembers.LastUpdateAt,
   856  			ChannelMembers.LastUpdateAt,
   857  			ChannelMembers.SchemeUser,
   858  			ChannelMembers.SchemeAdmin,
   859  			(ChannelMembers.SchemeGuest IS NOT NULL AND ChannelMembers.SchemeGuest) as SchemeGuest
   860  		FROM
   861  			ChannelMembers
   862  			JOIN Channels ON Channels.Id = ChannelMembers.ChannelId
   863  			LEFT JOIN Bots ON Bots.UserId = ChannelMembers.UserId
   864  		WHERE
   865  			Channels.DeleteAt = 0
   866  			AND Channels.GroupConstrained = TRUE
   867  			AND Bots.UserId IS NULL
   868  			AND (ChannelMembers.ChannelId, ChannelMembers.UserId)
   869  			NOT IN (
   870  				SELECT
   871  					Channels.Id AS ChannelId, GroupMembers.UserId
   872  				FROM
   873  					Channels
   874  					JOIN GroupChannels ON GroupChannels.ChannelId = Channels.Id
   875  					JOIN UserGroups ON UserGroups.Id = GroupChannels.GroupId
   876  					JOIN GroupMembers ON GroupMembers.GroupId = UserGroups.Id
   877  				WHERE
   878  					Channels.GroupConstrained = TRUE
   879  					AND GroupChannels.DeleteAt = 0
   880  					AND UserGroups.DeleteAt = 0
   881  					AND Channels.DeleteAt = 0
   882  					AND GroupMembers.DeleteAt = 0
   883  				GROUP BY
   884  					Channels.Id,
   885  					GroupMembers.UserId)`
   886  
   887  	var channelMembers []*model.ChannelMember
   888  
   889  	_, err := s.GetReplica().Select(&channelMembers, sql)
   890  	if err != nil {
   891  		return nil, model.NewAppError("SqlGroupStore.ChannelMembersToRemove", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
   892  	}
   893  
   894  	return channelMembers, nil
   895  }
   896  
   897  func (s *SqlGroupStore) groupsBySyncableBaseQuery(st model.GroupSyncableType, t selectType, syncableID string, opts model.GroupSearchOpts) sq.SelectBuilder {
   898  	selectStrs := map[selectType]string{
   899  		selectGroups:      "ug.*",
   900  		selectCountGroups: "COUNT(*)",
   901  	}
   902  
   903  	var table string
   904  	var idCol string
   905  	if st == model.GroupSyncableTypeTeam {
   906  		table = "GroupTeams"
   907  		idCol = "TeamId"
   908  	} else {
   909  		table = "GroupChannels"
   910  		idCol = "ChannelId"
   911  	}
   912  
   913  	query := s.getQueryBuilder().
   914  		Select(selectStrs[t]).
   915  		From(fmt.Sprintf("%s gs", table)).
   916  		LeftJoin("UserGroups ug ON gs.GroupId = ug.Id").
   917  		Where(fmt.Sprintf("ug.DeleteAt = 0 AND gs.%s = ? AND gs.DeleteAt = 0", idCol), syncableID)
   918  
   919  	if opts.IncludeMemberCount && t == selectGroups {
   920  		query = s.getQueryBuilder().
   921  			Select("ug.*, coalesce(Members.MemberCount, 0) AS MemberCount").
   922  			From("UserGroups ug").
   923  			LeftJoin("(SELECT GroupMembers.GroupId, COUNT(*) AS MemberCount FROM GroupMembers WHERE GroupMembers.DeleteAt = 0 GROUP BY GroupId) AS Members ON Members.GroupId = ug.Id").
   924  			LeftJoin(fmt.Sprintf("%[1]s ON %[1]s.GroupId = ug.Id", table)).
   925  			Where(fmt.Sprintf("%[1]s.DeleteAt = 0 AND %[1]s.%[2]s = ?", table, idCol), syncableID).
   926  			OrderBy("ug.DisplayName")
   927  	}
   928  
   929  	if len(opts.Q) > 0 {
   930  		pattern := fmt.Sprintf("%%%s%%", opts.Q)
   931  		operatorKeyword := "ILIKE"
   932  		if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
   933  			operatorKeyword = "LIKE"
   934  		}
   935  		query = query.Where(fmt.Sprintf("(ug.Name %[1]s ? OR ug.DisplayName %[1]s ?)", operatorKeyword), pattern, pattern)
   936  	}
   937  
   938  	return query
   939  }
   940  
   941  func (s *SqlGroupStore) CountGroupsByTeam(teamId string, opts model.GroupSearchOpts) (int64, *model.AppError) {
   942  	countQuery := s.groupsBySyncableBaseQuery(model.GroupSyncableTypeTeam, selectCountGroups, teamId, opts)
   943  
   944  	countQueryString, args, err := countQuery.ToSql()
   945  	if err != nil {
   946  		return int64(0), model.NewAppError("SqlGroupStore.CountGroupsByTeam", "store.sql_group.app_error", nil, err.Error(), http.StatusInternalServerError)
   947  	}
   948  
   949  	count, err := s.GetReplica().SelectInt(countQueryString, args...)
   950  	if err != nil {
   951  		return int64(0), model.NewAppError("SqlGroupStore.CountGroupsByTeam", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
   952  	}
   953  
   954  	return count, nil
   955  }
   956  
   957  func (s *SqlGroupStore) GetGroupsByTeam(teamId string, opts model.GroupSearchOpts) ([]*model.Group, *model.AppError) {
   958  	query := s.groupsBySyncableBaseQuery(model.GroupSyncableTypeTeam, selectGroups, teamId, opts)
   959  
   960  	if opts.PageOpts != nil {
   961  		offset := uint64(opts.PageOpts.Page * opts.PageOpts.PerPage)
   962  		query = query.OrderBy("ug.DisplayName").Limit(uint64(opts.PageOpts.PerPage)).Offset(offset)
   963  	}
   964  
   965  	queryString, args, err := query.ToSql()
   966  	if err != nil {
   967  		return nil, model.NewAppError("SqlGroupStore.GetGroupsByTeam", "store.sql_group.app_error", nil, err.Error(), http.StatusInternalServerError)
   968  	}
   969  
   970  	var groups []*model.Group
   971  
   972  	_, err = s.GetReplica().Select(&groups, queryString, args...)
   973  	if err != nil {
   974  		return nil, model.NewAppError("SqlGroupStore.GetGroupsByTeam", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
   975  	}
   976  
   977  	return groups, nil
   978  }
   979  
   980  func (s *SqlGroupStore) GetGroups(page, perPage int, opts model.GroupSearchOpts) ([]*model.Group, *model.AppError) {
   981  	var groups []*model.Group
   982  
   983  	groupsQuery := s.getQueryBuilder().Select("g.*").From("UserGroups g").Limit(uint64(perPage)).Offset(uint64(page * perPage)).OrderBy("g.DisplayName")
   984  
   985  	if opts.IncludeMemberCount {
   986  		groupsQuery = s.getQueryBuilder().
   987  			Select("g.*, coalesce(Members.MemberCount, 0) AS MemberCount").
   988  			From("UserGroups g").
   989  			LeftJoin("(SELECT GroupMembers.GroupId, COUNT(*) AS MemberCount FROM GroupMembers WHERE GroupMembers.DeleteAt = 0 GROUP BY GroupId) AS Members ON Members.GroupId = g.Id").
   990  			Limit(uint64(perPage)).
   991  			Offset(uint64(page * perPage)).
   992  			OrderBy("g.DisplayName")
   993  	}
   994  
   995  	if len(opts.Q) > 0 {
   996  		pattern := fmt.Sprintf("%%%s%%", opts.Q)
   997  		operatorKeyword := "ILIKE"
   998  		if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
   999  			operatorKeyword = "LIKE"
  1000  		}
  1001  		groupsQuery = groupsQuery.Where(fmt.Sprintf("(g.Name %[1]s ? OR g.DisplayName %[1]s ?)", operatorKeyword), pattern, pattern)
  1002  	}
  1003  
  1004  	if len(opts.NotAssociatedToTeam) == 26 {
  1005  		groupsQuery = groupsQuery.Where(`
  1006  			g.Id NOT IN (
  1007  				SELECT 
  1008  					Id 
  1009  				FROM 
  1010  					UserGroups
  1011  					JOIN GroupTeams ON GroupTeams.GroupId = UserGroups.Id
  1012  				WHERE 
  1013  					GroupTeams.DeleteAt = 0
  1014  					AND UserGroups.DeleteAt = 0
  1015  					AND GroupTeams.TeamId = ?
  1016  			)
  1017  		`, opts.NotAssociatedToTeam)
  1018  	}
  1019  
  1020  	if len(opts.NotAssociatedToChannel) == 26 {
  1021  		groupsQuery = groupsQuery.Where(`
  1022  			g.Id NOT IN (
  1023  				SELECT 
  1024  					Id 
  1025  				FROM 
  1026  					UserGroups
  1027  					JOIN GroupChannels ON GroupChannels.GroupId = UserGroups.Id
  1028  				WHERE 
  1029  					GroupChannels.DeleteAt = 0
  1030  					AND UserGroups.DeleteAt = 0
  1031  					AND GroupChannels.ChannelId = ?
  1032  			)
  1033  		`, opts.NotAssociatedToChannel)
  1034  	}
  1035  
  1036  	queryString, args, err := groupsQuery.ToSql()
  1037  	if err != nil {
  1038  		return nil, model.NewAppError("SqlGroupStore.GetGroups", "store.sql_group.app_error", nil, err.Error(), http.StatusInternalServerError)
  1039  	}
  1040  
  1041  	if _, err = s.GetReplica().Select(&groups, queryString, args...); err != nil {
  1042  		return nil, model.NewAppError("SqlGroupStore.GetGroups", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
  1043  	}
  1044  
  1045  	return groups, nil
  1046  }
  1047  
  1048  func (s *SqlGroupStore) teamMembersMinusGroupMembersQuery(teamID string, groupIDs []string, isCount bool) sq.SelectBuilder {
  1049  	var selectStr string
  1050  
  1051  	if isCount {
  1052  		selectStr = "count(DISTINCT Users.Id)"
  1053  	} else {
  1054  		tmpl := "Users.*, TeamMembers.SchemeGuest, TeamMembers.SchemeAdmin, TeamMembers.SchemeUser, %s AS GroupIDs"
  1055  		if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
  1056  			selectStr = fmt.Sprintf(tmpl, "group_concat(UserGroups.Id)")
  1057  		} else {
  1058  			selectStr = fmt.Sprintf(tmpl, "string_agg(UserGroups.Id, ',')")
  1059  		}
  1060  	}
  1061  
  1062  	subQuery := s.getQueryBuilder().Select("GroupMembers.UserId").
  1063  		From("GroupMembers").
  1064  		Join("UserGroups ON UserGroups.Id = GroupMembers.GroupId").
  1065  		Where("GroupMembers.DeleteAt = 0").
  1066  		Where(fmt.Sprintf("GroupMembers.GroupId IN ('%s')", strings.Join(groupIDs, "', '")))
  1067  
  1068  	sql, _ := subQuery.MustSql()
  1069  
  1070  	query := s.getQueryBuilder().Select(selectStr).
  1071  		From("TeamMembers").
  1072  		Join("Teams ON Teams.Id = TeamMembers.TeamId").
  1073  		Join("Users ON Users.Id = TeamMembers.UserId").
  1074  		LeftJoin("Bots ON Bots.UserId = TeamMembers.UserId").
  1075  		Join("GroupMembers ON GroupMembers.UserId = Users.Id").
  1076  		Join("UserGroups ON UserGroups.Id = GroupMembers.GroupId").
  1077  		Where("TeamMembers.DeleteAt = 0").
  1078  		Where("Teams.DeleteAt = 0").
  1079  		Where("Users.DeleteAt = 0").
  1080  		Where("Bots.UserId IS NULL").
  1081  		Where("Teams.Id = ?", teamID).
  1082  		Where(fmt.Sprintf("Users.Id NOT IN (%s)", sql))
  1083  
  1084  	if !isCount {
  1085  		query = query.GroupBy("Users.Id, TeamMembers.SchemeGuest, TeamMembers.SchemeAdmin, TeamMembers.SchemeUser")
  1086  	}
  1087  
  1088  	return query
  1089  }
  1090  
  1091  // TeamMembersMinusGroupMembers returns the set of users on the given team minus the set of users in the given
  1092  // groups.
  1093  func (s *SqlGroupStore) TeamMembersMinusGroupMembers(teamID string, groupIDs []string, page, perPage int) ([]*model.UserWithGroups, *model.AppError) {
  1094  	query := s.teamMembersMinusGroupMembersQuery(teamID, groupIDs, false)
  1095  	query = query.OrderBy("Users.Id").Limit(uint64(perPage)).Offset(uint64(page * perPage))
  1096  
  1097  	queryString, args, err := query.ToSql()
  1098  	if err != nil {
  1099  		return nil, model.NewAppError("SqlGroupStore.TeamMembersMinusGroupMembers", "store.sql_group.app_error", nil, err.Error(), http.StatusInternalServerError)
  1100  	}
  1101  
  1102  	var users []*model.UserWithGroups
  1103  	if _, err = s.GetReplica().Select(&users, queryString, args...); err != nil {
  1104  		return nil, model.NewAppError("SqlGroupStore.TeamMembersMinusGroupMembers", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
  1105  	}
  1106  
  1107  	return users, nil
  1108  }
  1109  
  1110  // CountTeamMembersMinusGroupMembers returns the count of the set of users on the given team minus the set of users
  1111  // in the given groups.
  1112  func (s *SqlGroupStore) CountTeamMembersMinusGroupMembers(teamID string, groupIDs []string) (int64, *model.AppError) {
  1113  	queryString, args, err := s.teamMembersMinusGroupMembersQuery(teamID, groupIDs, true).ToSql()
  1114  	if err != nil {
  1115  		return 0, model.NewAppError("SqlGroupStore.CountTeamMembersMinusGroupMembers", "store.sql_group.app_error", nil, err.Error(), http.StatusInternalServerError)
  1116  	}
  1117  
  1118  	var count int64
  1119  	if count, err = s.GetReplica().SelectInt(queryString, args...); err != nil {
  1120  		return 0, model.NewAppError("SqlGroupStore.CountTeamMembersMinusGroupMembers", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
  1121  	}
  1122  
  1123  	return count, nil
  1124  }
  1125  
  1126  func (s *SqlGroupStore) channelMembersMinusGroupMembersQuery(channelID string, groupIDs []string, isCount bool) sq.SelectBuilder {
  1127  	var selectStr string
  1128  
  1129  	if isCount {
  1130  		selectStr = "count(DISTINCT Users.Id)"
  1131  	} else {
  1132  		tmpl := "Users.*, ChannelMembers.SchemeGuest, ChannelMembers.SchemeAdmin, ChannelMembers.SchemeUser, %s AS GroupIDs"
  1133  		if s.DriverName() == model.DATABASE_DRIVER_MYSQL {
  1134  			selectStr = fmt.Sprintf(tmpl, "group_concat(UserGroups.Id)")
  1135  		} else {
  1136  			selectStr = fmt.Sprintf(tmpl, "string_agg(UserGroups.Id, ',')")
  1137  		}
  1138  	}
  1139  
  1140  	subQuery := s.getQueryBuilder().Select("GroupMembers.UserId").
  1141  		From("GroupMembers").
  1142  		Join("UserGroups ON UserGroups.Id = GroupMembers.GroupId").
  1143  		Where("GroupMembers.DeleteAt = 0").
  1144  		Where(fmt.Sprintf("GroupMembers.GroupId IN ('%s')", strings.Join(groupIDs, "', '")))
  1145  
  1146  	sql, _ := subQuery.MustSql()
  1147  
  1148  	query := s.getQueryBuilder().Select(selectStr).
  1149  		From("ChannelMembers").
  1150  		Join("Channels ON Channels.Id = ChannelMembers.ChannelId").
  1151  		Join("Users ON Users.Id = ChannelMembers.UserId").
  1152  		LeftJoin("Bots ON Bots.UserId = ChannelMembers.UserId").
  1153  		Join("GroupMembers ON GroupMembers.UserId = Users.Id").
  1154  		Join("UserGroups ON UserGroups.Id = GroupMembers.GroupId").
  1155  		Where("Channels.DeleteAt = 0").
  1156  		Where("Users.DeleteAt = 0").
  1157  		Where("Bots.UserId IS NULL").
  1158  		Where("Channels.Id = ?", channelID).
  1159  		Where(fmt.Sprintf("Users.Id NOT IN (%s)", sql))
  1160  
  1161  	if !isCount {
  1162  		query = query.GroupBy("Users.Id, ChannelMembers.SchemeGuest, ChannelMembers.SchemeAdmin, ChannelMembers.SchemeUser")
  1163  	}
  1164  
  1165  	return query
  1166  }
  1167  
  1168  // ChannelMembersMinusGroupMembers returns the set of users in the given channel minus the set of users in the given
  1169  // groups.
  1170  func (s *SqlGroupStore) ChannelMembersMinusGroupMembers(channelID string, groupIDs []string, page, perPage int) ([]*model.UserWithGroups, *model.AppError) {
  1171  	query := s.channelMembersMinusGroupMembersQuery(channelID, groupIDs, false)
  1172  	query = query.OrderBy("Users.Id").Limit(uint64(perPage)).Offset(uint64(page * perPage))
  1173  
  1174  	queryString, args, err := query.ToSql()
  1175  	if err != nil {
  1176  		return nil, model.NewAppError("SqlGroupStore.ChannelMembersMinusGroupMembers", "store.sql_group.app_error", nil, err.Error(), http.StatusInternalServerError)
  1177  	}
  1178  
  1179  	var users []*model.UserWithGroups
  1180  	if _, err = s.GetReplica().Select(&users, queryString, args...); err != nil {
  1181  		return nil, model.NewAppError("SqlGroupStore.ChannelMembersMinusGroupMembers", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
  1182  	}
  1183  
  1184  	return users, nil
  1185  }
  1186  
  1187  // CountChannelMembersMinusGroupMembers returns the count of the set of users in the given channel minus the set of users
  1188  // in the given groups.
  1189  func (s *SqlGroupStore) CountChannelMembersMinusGroupMembers(channelID string, groupIDs []string) (int64, *model.AppError) {
  1190  	queryString, args, err := s.channelMembersMinusGroupMembersQuery(channelID, groupIDs, true).ToSql()
  1191  	if err != nil {
  1192  		return 0, model.NewAppError("SqlGroupStore.CountChannelMembersMinusGroupMembers", "store.sql_group.app_error", nil, err.Error(), http.StatusInternalServerError)
  1193  	}
  1194  
  1195  	var count int64
  1196  	if count, err = s.GetReplica().SelectInt(queryString, args...); err != nil {
  1197  		return 0, model.NewAppError("SqlGroupStore.CountChannelMembersMinusGroupMembers", "store.select_error", nil, err.Error(), http.StatusInternalServerError)
  1198  	}
  1199  
  1200  	return count, nil
  1201  }