how to set extra field in sql server query

Question

I want to get the following model from dapper:

 public class CourseShowViewModel
    {
        public long Id { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
        public string ImageId { get; set; }
        public string DemoLink { get; set; }
        public long TotalDuration { get; set; }
        public TimeSpan TotalDurationTimeSpan { get; set; }
        public CourseStatusEnum CourseStatusEnum { get; set; }
        public string CourseStatusString { get; set; }
        public double Price { get; set; }
        public string AgePeriod { get; set; }
        public bool CanView { get; set; }

        public List CourseItemShowViewModels { get; set; }
    }

    public class CourseItemShowViewModel
    {
        public long Id { get; set; }
        public string Title { get; set; }
        public long Duration { get; set; }
        public TimeSpan DurationTimeSpan { get; set; }
        public DateTime CreatedDate { get; set; }
        public string FileId { get; set; }
    }

the sql query is:

select c.[Id]
      ,c.[Title]
      ,c.[Description]
      ,c.[CreatedDate]
      ,c.[ImageId]
      ,c.[CourseStatusEnum]
      ,c.[TotalDuration]
      ,c.[Price]
      ,c.[CategoryEnum]
      ,c.[AgePeriod]
      ,c.[DemoLink]
      , 0 as [CanView]

      ,cp.[Id]
      ,cp.[Title]
      ,cp.[CreatedDate]
      ,cp.[Duration]
      ,cp.[FileId]
from Course as c
left join CourseParts cp on cp.CourseId = c.Id

The problem is how to set [CanView]. if user bought the course the value must be 1 and else must be 0. also the [FileId] lookes like [CanView] and the difference is that if user bought the course the value must be File of the coursePart for download and else must be null.

Order table is contains user orders. check user bought query:

if exists (select Id from Order where UserId = @userId and CourseId = @courseId)

user_8258051 · Answer

I'm not sure how the dapper model is relevant to your question. It seems mostly focused how to fill in the [CanView] and how to hide the FileId based on a condition.

But based on your given query, giving input to the two fields isn't that hard.

select c.[Id]
      ,c.[Title]
      ,c.[Description]
      ,c.[CreatedDate]
      ,c.[ImageId]
      ,c.[CourseStatusEnum]
      ,c.[TotalDuration]
      ,c.[Price]
      ,c.[CategoryEnum]
      ,c.[AgePeriod]
      ,c.[DemoLink]
      , 
CASE WHEN EXISTS (select Id from Order where UserId = @userId and CourseId = @courseId) 
THEN 1 
ELSE 0
END as [CanView]
      ,cp.[Id]
      ,cp.[Title]
      ,cp.[CreatedDate]
      ,cp.[Duration]
      ,
CASE WHEN EXISTS (select Id from Order where UserId = @userId and CourseId = @courseId) 
THEN cp.[FileId]
ELSE null
END as [FileId]
from Course as c
left join CourseParts cp on cp.CourseId = c.Id

You can also do it by rewriting the query making use of joins to exclude the data. But considering you already have the subquery and the regular select. This should work as well.

W3schools case statement reference

how to set extra field in sql server query

1 Answers