Subsonic 2.2 does anyone know how I can reproduce this SQL as a SubSonic Query?

Question

Does anyone know how I can reproduce this SQL Query as a SubSonic Query?

SELECT PollID, AddedDate, AddedBy, QuestionText, IsCurrent, IsArchived, ArchivedDate,
   (SELECT SUM(Votes) FROM sqsc_PollOptions WHERE PollID = P.PollID) AS Votes
   FROM sqsc_Polls P
   WHERE IsArchived = @IsArchived1 OR IsArchived = @IsArchived2
   ORDER BY AddedDate DESC

I have tried using a View and a aggregate query but it returns no rows when there are no records in the sqsc_PollOptions table which will be the case when creating a new Poll record.

This is how I was querying the view:

return new Select(Aggregate.GroupBy(VwSdPollOption.Columns.PollID, "PollID"), Aggregate.GroupBy(VwSdPollOption.Columns.QuestionText, "QuestionText"), Aggregate.GroupBy(VwSdPollOption.Columns.IsCurrent, "IsCurrent"), Aggregate.Sum(VwSdPollOption.Columns.Votes, "Votes")).From(Views.VwSdPollOption).Where(VwSdPollOption.Columns.CentreName).IsEqualTo(centreName).ExecuteDataSet();

Any help would be greatly appreciated!

Answer 1

Change your view to this:

   SELECT P.PollID, P.AddedDate, P.AddedBy, P.QuestionText, 
          P.IsCurrent, P.IsArchived, P.ArchivedDate,
          v.Votes
   FROM  sqsc_Polls P left outer join 
         ( SELECT SUM(Votes) as Votes, PollID 
           FROM sqsc_PollOptions group by PollID ) V 
         on P.PollID = V.PollID        
   ORDER BY P.AddedDate DESC

You need to do an outer join to the vote table so that your base poll table fields are still present. Note that Votes will be nullable int. You can fix that by just placing a case statement in the view to return 0 instead of null, or just code around it in your code.

Also, note that I didn't test this and the SQL is from memory, so there might be a typo there. You should be able to figure that out from here.