Home
Help
Register
Log in

Search

 
   Active Threads  

You are here: Home > LLBLGen Pro > LLBLGen Pro Runtime Framework> Filter on m:n relation
 

Pages: 1
LLBLGen Pro Runtime Framework
Filter on m:n relation
Page:1/1 

  Print all messages in this thread  
Poster Message
JanRHansen
User



Location:

Joined on:
02-Jan-2019 16:48:19
Posted:
3 posts
# Posted on: 03-Jan-2019 14:40:21.  
Hi,

Complete llblgen rookie here Regular Smiley I'm currently evaluating llblgen on a not-so-large customer project.

I use llblgen pro 5.5, mysql 5.5, asp.net website - fw 4.7.2

I have a documentversion entity (think documents with titles and other properties), a tag entity (think... tags... with a string value) and a TagsOnVersion table which combines these in an m:n relationship.

I can query for documentversions like this:

Code:
var documentVersions = new EntityCollection<DocumentversionEntity>();

using (var adapter = new DataAccessAdapter())
{
    var qf = new QueryFactory();

    var q = qf.Documentversion;

    // Text search (search in title)
    if (!string.IsNullOrWhiteSpace(uiTextSearchInput.Text))
    {
        q = q.AndWhere(DocumentversionFields.Title.Contains(uiTextSearchInput.Text) );
    }

    // Tags search
    //if (!string.IsNullOrWhiteSpace(uiTagsSearchInput.Text))
    //{
    //    var stringTags = uiTagsSearchInput.Text.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries);
    //    foreach (var stringTag in stringTags)
    //    {
    //        // Old Telerik code:
    //        //query = query.Where(dv => dv.Tags.Contains(tag));
    //    }
    //}

    q = q.WithPath(DocumentversionEntity.PrefetchPathTagCollectionViaTagsonversion);

    adapter.FetchQuery(q, documentVersions);
}

// binding documentVersions to grid here


I have included some of the old Telerik ORM code which would lazy-load the Tags collection and hence I was able to pick those
documentversions which had a Tags list that contained one of the specified tags in the search box. Not a very way, but performance is not
really a consideration here.

However, I have figured out how to load the tags with the PrefetchPath - but I'm unable to add a filter, so I get the existing behaviour.

I would imagine something like
Code:
q = q.AndWhere(DocumentversionFields.TagsCollectionViaTagsonversion.Contains(...))

but thats not possible.

Any help would be appreciated.

BR, Jan
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14447 posts
# Posted on: 03-Jan-2019 22:35:11.  
To filter on a related entity you need to use joins.

Example:
Code:
var qf = new QueryFactory();
var query = qf.Order
     .From(QueryTarget.InnerJoin(qf.OrderDetail)
    .On(OrderFields.OrderId == OrderDetailFields.OrderId))
    .From(QueryTarget.InnerJoin(qf.Product)
    .On(ProductFields.ProductId == OrderDetailFields.ProductId)).Where(ProductFields.CategoryId == 2)
    .Select(OrderFields.OrderId);

var list = adapter.FetchQuery(query);


  Top
JanRHansen
User



Location:

Joined on:
02-Jan-2019 16:48:19
Posted:
3 posts
# Posted on: 04-Jan-2019 12:46:30.  
Thanks. I've added the many-to-many tables with joins now:

Code:
var documentVersions = new EntityCollection<DocumentversionEntity>();
var stringTags = new List<string>();

using (var adapter = new DataAccessAdapter())
{
    var qf = new QueryFactory();

    var q = qf.Documentversion
        .From(QueryTarget.InnerJoin(DocumentversionEntity.Relations.TagsonversionEntityUsingDocumentVersionId)
            .InnerJoin(TagsonversionEntity.Relations.TagEntityUsingTagId)
            );

    // Text search (search in title)
    if (!string.IsNullOrWhiteSpace(uiTextSearchInput.Text))
    {
        q = q.AndWhere(DocumentversionFields.Title.Contains(uiTextSearchInput.Text) );
    }

    // Tags search
    if (!string.IsNullOrWhiteSpace(uiTagsSearchInput.Text))
    {
        stringTags.AddRange(uiTagsSearchInput.Text.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries));
        foreach (var stringTag in stringTags)
        {
            // Old Telerik code:
            //query = query.Where(dv => dv.Tags.Contains(tag));
            
            // LLBLGen code here 1...
        }

        // LLBLGen code here 2...

        //q = q.AndWhere(TagFields.Value.In(stringTags));
        // this corresponds to SQL ...WHERE tag.value IN ('t1', 't2')
        // but returns all documentversions with tag t1 OR t2, which is too many
        // what we want, is all documentversions with tags t1 AND t2
    }

    q = q.WithPath(DocumentversionEntity.PrefetchPathTagCollectionViaTagsonversion);

    adapter.FetchQuery(q, documentVersions);
}

var documentVersionsFiltered = new EntityCollection<DocumentversionEntity>();

foreach (var documentversionEntity in documentVersions)
{

    var allTagsValues = documentversionEntity.TagCollectionViaTagsonversion.Select(t => t.Value);

    if (allTagsValues.Intersect(stringTags).Count() == stringTags.Count)
    {
        documentVersionsFiltered.Add(documentversionEntity);

    }
}

uiDocumentVersions.DataSource = documentVersionsFiltered;


And I'm now able to do some filtering on the tag values. However, it still doesn't quite behave as desired.

Consider this example:

document A has tags x and y
document B has tags x and z
document C has tags y and z

if I uncomment the line

//q = q.AndWhere(TagFields.Value.In(stringTags));

and search for x I get document A and B. If I search for x and y (stringTags list has two entries) I get document A, B and C, where I would like to get only A, but because the generated query is something like WHERE tag.value IN ('x', 'y') all three documentversions will be found.

Any advice on how to correctly construct a QuerySpec to overcome this?

I would imagine adding something for each tag where I've added // LLBLGen code here 1.... or something for the collection of tags being searched for where I've added // LLBLGen code here 2...

BR, Jan
  Top
Walaa
Support Team



Location:

Joined on:
21-Aug-2005 16:03:48
Posted:
14447 posts
# Posted on: 04-Jan-2019 16:46:37.  
The kind of filtering used, is not suitable for your case.

This is a SQL problem See these stack overflow posts: https://stackoverflow.com/questions/13889547/select-all-rows-that-have-at-least-a-list-of-features

https://stackoverflow.com/questions/17278695/sql-query-selecting-a-row-if-it-has-all-related-options-in-a-related-table

So the way to go is usually to use a groupby + a having clause which does a count on the grouped set so that the count is equal to the number of elements (tags in your case) in the IN clause. A parent row that matches a subset of the set of tags specified has less elements in the group so the count will be less than the value checked in the having clause.

With Queryspec you have to use a correlated subquery here which contains the joins.

I'll try to work out an example for you...

(Edit)
Example based on Northwind...

Trying to find Orders, having all of the following products (ProductID: 44, 59 & 73).
Code:
var qf = new QueryFactory();

var query = qf.OrderDetail
    .Select(OrderDetailFields.OrderId, Functions.CountRow().As("NumberOfProductsFound"))
     .From(QueryTarget.InnerJoin(qf.Product)
    .On(ProductFields.ProductId == OrderDetailFields.ProductId))
    .Where(ProductFields.ProductId == 44).OrWhere(ProductFields.ProductId == 59).OrWhere(ProductFields.ProductId == 73)
    .GroupBy(OrderDetailFields.OrderId);

var query2 = qf.Create()
    .From(query.As("p"))
    .Where(qf.Field("p", "NumberOfProductsFound").Equal(3))
    .Select(() => qf.Order
        .CorrelatedOver(OrderFields.OrderId == OrderDetailFields.OrderId.Source("p"))
        .ToResultset()
    );

var list = adapter.FetchQuery(query2);


  Top
Pages: 1  


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

Version: 2.1.12172008 Final.