HasTerms Filter Issues

Mar 10, 2012 at 3:58 PM
Edited Mar 10, 2012 at 3:59 PM

Having a play with Queries and Projectors in particular the HasTerms Filter.  I have a few issues, here is my setup:

I have a number of custom Course Content Items, each course has a number of Taxonomies associated with it, Category, Delivery Method, Sponsor. Some of these taxonomies can have more than one term selected for any given course.

I am now trying to create a Projection that shows all courses that are in the Vet category. I have setup the Query using the Content Type filter (selecting Course) and the HasTerms filter (selecting Vet).

I want to take this further and have a filter widget (dropdown) on the page which allows the users to filter further by choosing a child term of Vet (Diagnostic Imaging, Emergency & Anaesthesia, Equine & Farm etc).  So I have edited the HasTerm Filter to take in a number of terms passed in through the Filter (using the querystring via the Token).

I have hit the following issues whilst trying to accomplish the above:

  1. The filter is not distinct. If the Content Item fits into a number of child terms and the filter is on the parent term then the content item is repeated within the list.
  2. The ApplyFilter() code seems to have a bug in it:
    foreach(var term in terms) {
       var localTerm = term;
       Action<IHqlExpressionFactory> ors = x => x.Or(a => a.Eq("Id", ids.First()), b => b.Like("Path", localTerm.FullPath + "/", HqlMatchMode.Start));
       predicates.Add(ors);
    }
    Shouldn't ids.First() be term.id?  It currently seems to be using the first term id in the list for every conjunction.
  3. I believe that because of the above selecting a parent and child term in the filter doesn't seem to work
  4. I can't seem to get the filter to work so that it works to select all distinct content items that have more than one term from different taxonomies, even after applying the above fix. 

Anyone else been able to get this working or am I not using this correctly?  I have been playing around with the sql that is queried, but can't seem to get it to return the correct records.

Thanks

Mar 22, 2012 at 8:26 PM

Well this one has been fixed for some time already. Sorry I forgot to mention. It's on the source code. I was waiting to add a  all/any switch before pushing a new version on the gallery.

Mar 23, 2012 at 12:22 AM

I took a look at the new source and 2 & 3 seem to be fixed, but I still get duplicate content items that have more than one term associated with it.

I have two problems

1. Producing distinct results, how I would go about adding a DISTINCT to the query?

2. How would you go about getting all the content items that have all the selected content items?

I have taken a look at the sql produced by the query to try and work backwards to achieve point 2....

Currently a query that asks for all items with a Content Type of 'Course'  that has 2 different terms (or any of the first terms 2 children) produces the following sql:

 

select contentite0_.Id as Id79_,
contentite0_.Number as Number79_, 
contentite0_.Published as Published79_, 
contentite0_.Latest as Latest79_, 
contentite0_.Data as Data79_, 
contentite0_.ContentItemRecord_id as ContentI6_79_ 
from cpd_Orchard_Framework_ContentItemVersionRecord contentite0_ 
inner join cpd_Orchard_Framework_ContentItemRecord contentite1_ on contentite0_.ContentItemRecord_id=contentite1_.Id 
inner join cpd_Orchard_Framework_ContentTypeRecord contenttyp2_ on contentite1_.ContentType_id=contenttyp2_.Id 
inner join cpd_Contrib_Taxonomies_TermsPartRecord termspartr3_ on contentite1_.Id=termspartr3_.Id 
inner join cpd_Contrib_Taxonomies_TermContentItem terms4_ on termspartr3_.Id=terms4_.TermsPartRecord_Id 
inner join cpd_Contrib_Taxonomies_TermPartRecord termpartre5_ on terms4_.TermRecord_id=termpartre5_.Id 
where (contenttyp2_.Name in ('Course')) 
and (termpartre5_.Id=36 or termpartre5_.Id=34 or termpartre5_.Id=37 or termpartre5_.Id=40) -- Parent term (with 2 children) and term from another taxonomy 
and contentite0_.Published=1

 

After playing around a bit, what we actually need is the following sql to be produced (I think!).  I have added a distinct and separate joins to TermContentItem and TermPartRecord for each separate term (children terms still use the disjunction).  How would you go about building this query with Hql?

 

SELECT	DISTINCT
		ContentItemVersionRecord.Id	AS ContentItemVersionRecord_Id, 
		ContentItemVersionRecord.Number	AS ContentItemVersionRecord_Number, 
		ContentItemVersionRecord.Published	AS ContentItemVersionRecord_Published, 
		ContentItemVersionRecord.Latest	AS ContentItemVersionRecord_Latest,
		ContentItemVersionRecord.Data	AS ContentItemVersionRecord_Data, 
		ContentItemVersionRecord.ContentItemRecord_id	AS ContentItemVersionRecord_ContentItemRecord_id  
FROM cpd_Orchard_Framework_ContentItemVersionRecord AS ContentItemVersionRecord 
INNER JOIN cpd_Orchard_Framework_ContentItemRecord ContentItemRecord 
	ON ContentItemVersionRecord.ContentItemRecord_id=ContentItemRecord.Id 
INNER JOIN cpd_Orchard_Framework_ContentTypeRecord ContentTypeRecord 
	ON ContentItemRecord.ContentType_id=ContentTypeRecord.Id 
INNER JOIN cpd_Contrib_Taxonomies_TermsPartRecord AS TermsPartRecord
	ON ContentItemRecord.Id=TermsPartRecord.Id 
-- First join for terms	
INNER JOIN cpd_Contrib_Taxonomies_TermContentItem AS TermContentItem1
	ON TermsPartRecord.Id=TermContentItem1.TermsPartRecord_Id 
INNER JOIN cpd_Contrib_Taxonomies_TermPartRecord AS TermPartRecord1
	ON TermContentItem1.TermRecord_id = TermPartRecord1.Id
-- Second join for terms	
INNER JOIN cpd_Contrib_Taxonomies_TermContentItem AS TermContentItem2
	ON TermsPartRecord.Id=TermContentItem2.TermsPartRecord_Id 
INNER JOIN cpd_Contrib_Taxonomies_TermPartRecord AS TermPartRecord2
	ON TermContentItem2.TermRecord_id = TermPartRecord2.Id
WHERE (ContentTypeRecord.Name IN ('Course')) 
AND (TermPartRecord1.Id=36 OR TermPartRecord1.Id=34 OR TermPartRecord1.Id=37) -- Parent term with children
AND TermPartRecord2.Id=40 -- Sibling term or term in different taxonomy
AND ContentItemVersionRecord.Published=1

Cheers for your help,

Jeff 

 

Mar 23, 2012 at 12:28 AM

There must be an example in the Orchard.Projection project because I had the same issue with the fields. You could look at what happens when two projection filters are added to two fields of the same type. Because it is on the same table if will need two joins, and I remember I had to fix it. This should be the same here.

Mar 27, 2012 at 10:12 AM

Cheers for that, I have taken a look and have come up with the following change for ApplyFilter in TermsFilter:

	public void ApplyFilter(dynamic context) {
            string termIds = Convert.ToString(context.State.TermIds);
            if (!String.IsNullOrEmpty(termIds)) {
                var ids = termIds.Split(new[] { ',' }).Select(Int32.Parse).ToArray();

                if (ids.Length == 0) {
                    return;
                }

                var parentTerms = ids.Select(_taxonomyService.GetTerm).ToList();
                var allTerms = new List<List<TermPart>>();
                foreach (var term in parentTerms)
                {
                    var allChildren = new List<TermPart>();
                    allChildren.AddRange(_taxonomyService.GetChildren(term));
                    allChildren.Add(term);
                    allTerms.Add(allChildren);
                }
                
                foreach (var terms in allTerms) {
                    var predicates = new List<Action<IHqlExpressionFactory>>();
                    foreach (var childTerm in terms)
                    {
                        var localTerm = childTerm;
                        Action<IHqlExpressionFactory> ors = a => a.Eq("Id", localTerm.Id);
                        predicates.Add(ors);
                    }

                    Action<IAliasFactory> selector = alias => alias.ContentPartRecord<TermsPartRecord>().Property("Terms", String.Format("Terms_{0}", terms.Last().Id)).Property("TermRecord", String.Format("TermRecord_{0}", terms.Last().Id));
                    Action<IHqlExpressionFactory> filter = x => x.Disjunction(predicates.Take(1).Single(), predicates.Skip(1).ToArray());
                    context.Query.Where(selector, filter);
                }
            }
        }

This generates the correct query using a separate join for each term.  It is correctly returning all items that have all terms selected, however this still  produces duplicates. Do you know how I alter the select to use a DISTINCT?

Thanks.

Apr 14, 2012 at 9:52 AM

Sébastien, any ideas on how I would enforce a DISTINCT on the query?
Cheers 

Sep 30, 2012 at 3:12 PM

Hey j3ffb did you end up figuring out getting distinct queries? Also seb when will you releasing a new version with the all/any flag?

 

Cheers guys

Oct 2, 2012 at 6:42 PM

Yeah I ended up editing the Projection module to force it to produce distinct results.  I think Sébastien has recently fixed this issue for us :) http://orchard.codeplex.com/workitem/18966 

Oct 2, 2012 at 11:56 PM

Just pushed an update with the One Of and Any Of filters. Copy/Pasted the implementation I had to make for Tags.

Time will be taken soon to fix outstanding bugs that have been reported, Taxonomies should be included in Core in Orchard 1.7.

Oct 3, 2012 at 2:02 AM
Edited Oct 3, 2012 at 2:04 AM


Thanks heaps guys. Just one problem I have with using "All of" on a parent term, it doesnt seem to be returning any results. As an example:

Make -> Ford

         -> Audi


Size -> Small -> Sedan
                    -> Coupe

        -> Big   -> Van
                    -> SUV
       

If I create a query with "All of" Ford and Small, im getting no results, but Ford and Sedan will work. My guess is the code is expanding the query to become "All of" Ford, Sedan and Coupe.

Oct 3, 2012 at 2:05 AM

I'll fix it then. Must be an issue when crawling children ...