locked
how to use group by(p => ....) with two fields RRS feed

  • Question

  • User692055318 posted

    Hello,

    My boss has a request to present the data like so:

    6/01/15 --- {top level grouping by date}

    {sub level group by thread id = 1}        time: jsmith: chat message             
    time: jdoe: chat message
    time: jsmith: chat message

    ________________________________________

    {sub level group by thread id = 2}

             time: jsmith: chat message
             time: mikeSmith: chat message
             time: jsmith: chat message

    So what he wants is each chat thread to be separated by a horizontal line. I have the query to group it by date right now. What he wants is the top level sorted by date and then sorted within it by thread_id.

    sql = @"SELECT * 
                    from vwGetData 
                    where (from_acct = @0
                    or to_acct = @0)
                    and thread_id <> ''
                    group by to_acct, from_acct, sent, sent_time,body_string,thread_id
                    order by sent, sent_time, thread_id";
    
    var ExecSql = db.Query(sql,from_acct).GroupBy(p => p.sent );

    So I tried this:

    sql = @"SELECT * 
                    from vwGetData 
                    where (from_acct = @0
                    or to_acct = @0)
                    and thread_id <> ''
                    group by to_acct, from_acct, sent, sent_time,body_string,thread_id
                    order by sent, sent_time, thread_id";
    
    var ExecSql = db.Query(sql,from_acct).GroupBy(p => new {p.sent,p.thread_id});

    But that gives me:

    { sent = 2015-03-03, thread_id = connect23127 }
    
    { sent = 2015-03-03, thread_id = connect24796 }
    
    { sent = 2015-03-03, thread_id = connect16060 }
    
    { sent = 2015-03-03, thread_id = connect16011 }
    
    { sent = 2015-03-03, thread_id = connect16572 }
    
    { sent = 2015-03-03, thread_id = connect17170 }

    So now I have two foreachs:

    foreach (var DateSentGrp in ExecSql) 
                       {
                         <h3>@DateSentGrp.Key</h3>
                          int i = 0;
                            foreach (var JabberData in DateSentGrp.Where(p => p.sent == DateSentGrp.Key)) 
                               {
                                           
                               <div class="row @(i%2==0?"even":"odd")">@JabberData.Sent_Time: @JabberData.from_acct :@JabberData.body_string</div>
                                 i++;
                               }
                       
                       }

    He also wants each line separated by a color that's what the <div class="row @(i%2==0?"even":"odd")"> is for.

    Is there a way to do this?

    Thanks for any ideas

    Laura

    Monday, June 1, 2015 9:40 AM

Answers

  • User692055318 posted

    I was going to deleted the post since I figured it out, but I thought maybe it would help someone else.

    I eventually got this, due to mostly this page: https://msdn.microsoft.com/en-us/library/bb545974%28v=vs.100%29.aspx

    You have to remove the groupby in the query itself then reference the query in another variable like so:

    var ExecSql = db.Query(sql,from_acct);

    Then add the nested loop and the foreach:

    var queryNestedGroups =
                    from jdata in ExecSql
                    group jdata by jdata.sent into newGroup1
                        from newGroup2 in
                            (from jdata in newGroup1
                             group jdata by jdata.thread_id)
                    group newGroup2 by newGroup1.Key;
            
         
                foreach (var outerGroup in queryNestedGroups)
                       {
                           <h3>@outerGroup.Key</h3>
                          
                            foreach (var innerGroup in outerGroup)
                               {    
    
                                   int i = 0;
                                  foreach (var innerGroupElement in innerGroup)
                                   {
                                     <div class="row @(i%2==0?"even":"odd")">@innerGroupElement.Sent_Time: @innerGroupElement.from_acct :@innerGroupElement.body_string</div>
                                  i++;
                                   }
    
                                    <hr></hr>
                               }
                       
                       }   

    Worked like a charm. Thanks for looking.

    Laura

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, June 2, 2015 3:02 PM