Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> To get first row from left join using LLBL
 

Pages: 1
LLBLGen Pro Runtime Framework
To get first row from left join using LLBL
Page:1/1 

  Print all messages in this thread  
Poster Message
Viresh
User



Location:
Ahmedabad, India
Joined on:
28-Jan-2008 08:52:54
Posted:
31 posts
# Posted on: 06-Jun-2008 10:43:14.  
Hi All,

I'm novice to LLBL. I'm facing one problem while getting first row from multiple rows of same record using LLBL. Following is the code.

     DataAccessAdapter adapter = new DataAccessAdapter();
        ResultsetFields fields = new ResultsetFields(7);
        fields.DefineField(AlbumFields.AlbumId, 0);
        fields.DefineField(AlbumFields.Name, 1);
        fields.DefineField(AlbumFields.Comment, 2);
        fields.DefineField(AlbumFields.CoverArtImageId, 3);
        fields.DefineField(ArtistFields.Name, 4, "ArtistName");
        fields.DefineField(PublisherFields.Name, 5, "Publisher");
        fields.DefineField(AlbumFields.AlbumId, 6, "Total", AggregateFunction.Count);

        IRelationPredicateBucket bucket = new RelationPredicateBucket();
        bucket.Relations.ObeyWeakRelations = true;
        bucket.Relations.Add(AlbumEntity.Relations.AuditAlbumEntityUsingAlbumId, JoinHint.Inner);
        bucket.Relations.Add(AlbumEntity.Relations.PublisherEntityUsingPublisherId, JoinHint.Left);
        bucket.Relations.Add(AlbumEntity.Relations.ArtistAlbumEntityUsingAlbumId, JoinHint.Left);
        bucket.Relations.Add(ArtistAlbumEntity.Relations.ArtistEntityUsingArtistId);
        bucket.PredicateExpression.Add(AlbumFields.IsApproved == true);
        bucket.PredicateExpression.AddWithAnd(AlbumFields.IsActive == true);
        bucket.PredicateExpression.AddWithAnd(AlbumFields.AlbumId != albumId);
        bucket.PredicateExpression.AddWithAnd(AlbumFields.MediaCategoryId == categoryId);
        bucket.PredicateExpression.AddWithAnd(AuditAlbumFields.EventOccurred == ToxicBeatsValues.AuditEventOccured.VIEWED);


        IGroupByCollection groupByClause = new GroupByCollection();
        groupByClause.Add(fields[0]);
        groupByClause.Add(fields[1]);
        groupByClause.Add(fields[2]);
        groupByClause.Add(fields[3]);
        groupByClause.Add(fields[4]);
        groupByClause.Add(fields[5]);

        groupByClause.HavingClause = new PredicateExpression(fields[4].SetExpression(ArtistFields.Name).SetAggregateFunction(AggregateFunction.Count) == 1);
        ISortClause sortClause = new SortClause(fields[6], null, SortOperator.Descending);
        ISortExpression sortExpression = new SortExpression(sortClause);

        DataTable dynamicList = new DataTable();
        adapter.FetchTypedList(fields, dynamicList, bucket, NumberOfTopItems, sortExpression, false, groupByClause);



Above code returns muliple artists with same album id. Let's say it's returning 3 rows with different artistnames for same album id. Now I want to pickup first artist from these 3 rows. Can you please help me how to achieve that using LLBL? Thanks in advance.



Regards,

Viresh Shah
 
Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14637 posts
# Posted on: 06-Jun-2008 10:47:57.  
Your question is not clear. Do You want a Top 1 or a Grooup By?

Would you please post the SQL query you want to execute with the related tables DDL?


  Top
Viresh
User



Location:
Ahmedabad, India
Joined on:
28-Jan-2008 08:52:54
Posted:
31 posts
# Posted on: 06-Jun-2008 10:52:18.  
I want top 1 with group by. Thanks Walaa.
Regards,

Viresh Shah
 
Top
Viresh
User



Location:
Ahmedabad, India
Joined on:
28-Jan-2008 08:52:54
Posted:
31 posts
# Posted on: 06-Jun-2008 10:54:56. Goto attachments  
I've attached a screen shot. If you look at row 4 and 5. I want only first row from those two records.

Regards,

Viresh Shah
 
Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14637 posts
# Posted on: 06-Jun-2008 11:05:22.  
Then you shouldn't add the Artist.Name to the group by collection.

Trye the following istead:
Code:
        fields.DefineField(ArtistFields.Name, 4, "ArtistName", AggregateFunction.MAX);
        ...
        IGroupByCollection groupByClause = new GroupByCollection();
        groupByClause.Add(fields[0]);
        groupByClause.Add(fields[1]);
        groupByClause.Add(fields[2]);
        groupByClause.Add(fields[3]);
        groupByClause.Add(fields[5]);
  Top
Viresh
User



Location:
Ahmedabad, India
Joined on:
28-Jan-2008 08:52:54
Posted:
31 posts
# Posted on: 06-Jun-2008 11:15:14.  
Thanks Walaa. It worked. Regular Smiley

Regards,

Viresh Shah
 
Top
Pages: 1  


Powered by HnD ©2002-2007 Solutions Design
HnD uses LLBLGen Pro

Version: 2.1.12172008 Final.