To get first row from left join using LLBL

Posts   
 
    
Viresh
User
Posts: 31
Joined: 28-Jan-2008
# 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.

Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# 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?

Viresh
User
Posts: 31
Joined: 28-Jan-2008
# Posted on: 06-Jun-2008 10:52:18   

I want top 1 with group by. Thanks Walaa.

Viresh
User
Posts: 31
Joined: 28-Jan-2008
# Posted on: 06-Jun-2008 10:54:56   

I've attached a screen shot. If you look at row 4 and 5. I want only first row from those two records.

Attachments
Filename File size Added on Approval
output.JPG 26,541 06-Jun-2008 10:58.24 Approved
Walaa avatar
Walaa
Support Team
Posts: 14946
Joined: 21-Aug-2005
# 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:

        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]);
Viresh
User
Posts: 31
Joined: 28-Jan-2008
# Posted on: 06-Jun-2008 11:15:14   

Thanks Walaa. It worked. simple_smile