PLT-7404 Return viewed at times in view channel API response (#7428)

* Return viewed at times in view channel API response

* Updated transaction to read and write once

* Remove transaction and only update if new value greater than older

2 files changed, 94 insertions, 30 deletions

diff --git a/store/sqlstore/channel_store.go b/store/sqlstore/channel_store.go
index ed001f2d2..c9a4f695e 100644
--- a/store/sqlstore/channel_store.go
+++ b/store/sqlstore/channel_store.go
@@ -1284,52 +1284,82 @@ func (s SqlChannelStore) UpdateLastViewedAt(channelIds []string, userId string)
 	go func() {
 		result := store.StoreResult{}
 
-		var query string
 		props := make(map[string]interface{})
 
-		idQuery := ""
+		updateIdQuery := ""
 		for index, channelId := range channelIds {
-			if len(idQuery) > 0 {
-				idQuery += " OR "
+			if len(updateIdQuery) > 0 {
+				updateIdQuery += " OR "
 			}
 
 			props["channelId"+strconv.Itoa(index)] = channelId
-			idQuery += "ChannelId = :channelId" + strconv.Itoa(index)
+			updateIdQuery += "ChannelId = :channelId" + strconv.Itoa(index)
+		}
+
+		selectIdQuery := strings.Replace(updateIdQuery, "ChannelId", "Id", -1)
+
+		var lastPostAtTimes []struct {
+			Id            string
+			LastPostAt    int64
+			TotalMsgCount int64
+		}
+
+		selectQuery := "SELECT Id, LastPostAt, TotalMsgCount FROM Channels WHERE (" + selectIdQuery + ")"
+
+		if _, err := s.GetMaster().Select(&lastPostAtTimes, selectQuery, props); err != nil {
+			result.Err = model.NewAppError("SqlChannelStore.UpdateLastViewedAt", "store.sql_channel.update_last_viewed_at.app_error", nil, "channel_ids="+strings.Join(channelIds, ",")+", user_id="+userId+", "+err.Error(), http.StatusInternalServerError)
+			storeChannel <- result
+			close(storeChannel)
+			return
+		}
+
+		times := map[string]int64{}
+		msgCountQuery := ""
+		lastViewedQuery := ""
+		for index, t := range lastPostAtTimes {
+			times[t.Id] = t.LastPostAt
+
+			props["msgCount"+strconv.Itoa(index)] = t.TotalMsgCount
+			msgCountQuery += fmt.Sprintf("WHEN :channelId%d THEN GREATEST(MsgCount, :msgCount%d) ", index, index)
+
+			props["lastViewed"+strconv.Itoa(index)] = t.LastPostAt
+			lastViewedQuery += fmt.Sprintf("WHEN :channelId%d THEN GREATEST(LastViewedAt, :lastViewed%d) ", index, index)
+
+			props["channelId"+strconv.Itoa(index)] = t.Id
 		}
 
+		var updateQuery string
+
 		if *utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_POSTGRES {
-			query = `UPDATE
+			updateQuery = `UPDATE
 				ChannelMembers
 			SET
 			    MentionCount = 0,
-			    MsgCount = Channels.TotalMsgCount,
-			    LastViewedAt = Channels.LastPostAt,
-			    LastUpdateAt = Channels.LastPostAt
-			FROM
-				Channels
+			    MsgCount = CAST(CASE ChannelId ` + msgCountQuery + ` END AS BIGINT),
+			    LastViewedAt = CAST(CASE ChannelId ` + lastViewedQuery + ` END AS BIGINT),
+			    LastUpdateAt = CAST(CASE ChannelId ` + lastViewedQuery + ` END AS BIGINT)
 			WHERE
-			    Channels.Id = ChannelMembers.ChannelId
-			        AND UserId = :UserId
-			        AND (` + idQuery + `)`
+			        UserId = :UserId
+			        AND (` + updateIdQuery + `)`
 		} else if *utils.Cfg.SqlSettings.DriverName == model.DATABASE_DRIVER_MYSQL {
-			query = `UPDATE
-				ChannelMembers, Channels
+			updateQuery = `UPDATE
+				ChannelMembers
 			SET
-			    ChannelMembers.MentionCount = 0,
-			    ChannelMembers.MsgCount = Channels.TotalMsgCount,
-			    ChannelMembers.LastViewedAt = Channels.LastPostAt,
-			    ChannelMembers.LastUpdateAt = Channels.LastPostAt
+			    MentionCount = 0,
+			    MsgCount = CASE ChannelId ` + msgCountQuery + ` END,
+			    LastViewedAt = CASE ChannelId ` + lastViewedQuery + ` END,
+			    LastUpdateAt = CASE ChannelId ` + lastViewedQuery + ` END
 			WHERE
-			    Channels.Id = ChannelMembers.ChannelId
-			        AND UserId = :UserId
-			        AND (` + idQuery + `)`
+			        UserId = :UserId
+			        AND (` + updateIdQuery + `)`
 		}
 
 		props["UserId"] = userId
 
-		_, err := s.GetMaster().Exec(query, props)
-		if err != nil {
+		if _, err := s.GetMaster().Exec(updateQuery, props); err != nil {
 			result.Err = model.NewAppError("SqlChannelStore.UpdateLastViewedAt", "store.sql_channel.update_last_viewed_at.app_error", nil, "channel_ids="+strings.Join(channelIds, ",")+", user_id="+userId+", "+err.Error(), http.StatusInternalServerError)
+		} else {
+			result.Data = times
 		}
 
 		storeChannel <- result
diff --git a/store/sqlstore/channel_store_test.go b/store/sqlstore/channel_store_test.go
index 490300f24..886852583 100644
--- a/store/sqlstore/channel_store_test.go
+++ b/store/sqlstore/channel_store_test.go
@@ -7,6 +7,8 @@ import (
 	"testing"
 	"time"
 
+	"github.com/stretchr/testify/assert"
+
 	"github.com/mattermost/mattermost-server/model"
 	"github.com/mattermost/mattermost-server/store"
 )
@@ -1249,6 +1251,7 @@ func TestChannelStoreUpdateLastViewedAt(t *testing.T) {
 	o1.Name = "zz" + model.NewId() + "b"
 	o1.Type = model.CHANNEL_OPEN
 	o1.TotalMsgCount = 25
+	o1.LastPostAt = 12345
 	store.Must(ss.Channel().Save(&o1))
 
 	m1 := model.ChannelMember{}
@@ -1257,13 +1260,44 @@ func TestChannelStoreUpdateLastViewedAt(t *testing.T) {
 	m1.NotifyProps = model.GetDefaultChannelNotifyProps()
 	store.Must(ss.Channel().SaveMember(&m1))
 
-	err := (<-ss.Channel().UpdateLastViewedAt([]string{m1.ChannelId}, m1.UserId)).Err
-	if err != nil {
-		t.Fatal("failed to update", err)
+	o2 := model.Channel{}
+	o2.TeamId = model.NewId()
+	o2.DisplayName = "Channel1"
+	o2.Name = "zz" + model.NewId() + "c"
+	o2.Type = model.CHANNEL_OPEN
+	o2.TotalMsgCount = 26
+	o2.LastPostAt = 123456
+	store.Must(ss.Channel().Save(&o2))
+
+	m2 := model.ChannelMember{}
+	m2.ChannelId = o2.Id
+	m2.UserId = m1.UserId
+	m2.NotifyProps = model.GetDefaultChannelNotifyProps()
+	store.Must(ss.Channel().SaveMember(&m2))
+
+	if result := <-ss.Channel().UpdateLastViewedAt([]string{m1.ChannelId}, m1.UserId); result.Err != nil {
+		t.Fatal("failed to update", result.Err)
+	} else if result.Data.(map[string]int64)[o1.Id] != o1.LastPostAt {
+		t.Fatal("last viewed at time incorrect")
 	}
 
-	err = (<-ss.Channel().UpdateLastViewedAt([]string{m1.ChannelId}, "missing id")).Err
-	if err != nil {
+	if result := <-ss.Channel().UpdateLastViewedAt([]string{m1.ChannelId, m2.ChannelId}, m1.UserId); result.Err != nil {
+		t.Fatal("failed to update", result.Err)
+	} else if result.Data.(map[string]int64)[o2.Id] != o2.LastPostAt {
+		t.Fatal("last viewed at time incorrect")
+	}
+
+	rm1 := store.Must(ss.Channel().GetMember(m1.ChannelId, m1.UserId)).(*model.ChannelMember)
+	assert.Equal(t, rm1.LastViewedAt, o1.LastPostAt)
+	assert.Equal(t, rm1.LastUpdateAt, o1.LastPostAt)
+	assert.Equal(t, rm1.MsgCount, o1.TotalMsgCount)
+
+	rm2 := store.Must(ss.Channel().GetMember(m2.ChannelId, m2.UserId)).(*model.ChannelMember)
+	assert.Equal(t, rm2.LastViewedAt, o2.LastPostAt)
+	assert.Equal(t, rm2.LastUpdateAt, o2.LastPostAt)
+	assert.Equal(t, rm2.MsgCount, o2.TotalMsgCount)
+
+	if result := <-ss.Channel().UpdateLastViewedAt([]string{m1.ChannelId}, "missing id"); result.Err != nil {
 		t.Fatal("failed to update")
 	}
 }