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 }