Ticket (Solved)

Help with returning Pages with a Taxonomy Term [HqlQuery]?

Hi All, I would like Dashboard -> Content to return a list of all Pages with a Taxonomy field attached to them equalling a certain term. Note: I asked about this sometime ago here:

http://stackoverflow.com/questions/35192793/orchard-cms-how-to-sort-order-the-manage-content-list-in-the-dashboard

and have been using the Admin Search feature very painfully ever since, but enough is enough, I have to do it properly.

Moving on, so I have narrowed down:

…src\Orchard.Web\Core\Contents\Controllers\AdminController.cs

private IEnumerable<ContentTypeDefinition> GetListableTypes(bool andContainable) 

What I'm worried about is, even if I can return all Pages that contains a TaxonomyField, I can't see how I would go about returning just the Pages that have a specific Taxonomy Term selected? For looking through VS Debugger, I believe this is related to handling ContentDefinitions and not actual ContentItems?

So I have tried the following in a custom module which returns all Page items:

    //Return All Pages
    public ActionResult Items(string taxTerm)
    {
        var list = Shape.List();

        var PageItemsQuery = _contentManager.HqlQuery()
            .ForType("Page").List();
            //.Select(p=> p.Parts.Cast<TermsPart>().

        list.AddRange(PageItemsQuery.Select(fi => _contentManager.BuildDisplay(fi, "SummaryAdmin")));

        dynamic viewModel = Shape.ViewModel();
        viewModel.ContentItems(list);
        viewModel.NumberOfItems(PageItemsQuery.Count());

        return View(viewModel);
    }

Can anyone help me with the query to select pages with a specific term [I really get muddled with this] here is a VS debug shot of where it's contained: Please see Attachment. Thank you for your support, PP

Re: Help with returning Pages with a Taxonomy Term [HqlQuery]?

This may give you a good starting point. It's not using taxonomies, but shouldn't be too far off: https://weblogs.asp.net/bleroy/querying-orchard-fields-with-hql

This may also help: https://weblogs.asp.net/bleroy/getting-orchard-content-items-out-of-hql

Thursday, August 4, 2016 11:12:46 PM bybleroy
  • bleroy
  • Lv. 08 Rookie
  • Total EXP: 527

Re: Help with returning Pages with a Taxonomy Term [HqlQuery]?

Thanks bleroy…Hmm, that's appears to be why I have held off from this in the first place, seems very advanced for this padawan - I note that your concept seems to pivot from projections. Therefore, would it be possible to create a query from the GUI [hasTerms etc.] then simply call that query from my code to list the contentItems? Hql/sql confuses me and my custom-code skills are weak at the best of times. Regards, PP

Thursday, August 4, 2016 11:53:16 PM bypigpen
  • pigpen
  • Lv. 02 Rookie
  • Total EXP: 58

Re: Help with returning Pages with a Taxonomy Term [HqlQuery]?

just for infos see this ticket. I think my 1st proposition with an HqlQuery() (not the one marked as answered) could fit your need. If you use the last proposition with a QueryOver(), you may have to change _sessionLocator.For(typeof(TermsPartRecord)) with _transactionManager.GetSession().

Best.

Friday, August 5, 2016 1:17:35 AM byjtkech
  • jtkech
  • Lv. 13 Rookie
  • Total EXP: 1028

Re: Help with returning Pages with a Taxonomy Term [HqlQuery]?

Geez jtkeck, I wouldn’t say 'just for infos', as that appears to be the solution. I changed term to:

var term = _taxonomyService.GetTermByName(_taxonomyService.GetTaxonomyByName("MyTaxonomy").Id, "MyTerm")

As "_taxonomyService.GetTermByPath" Appears to be obsolete.

var PageItemsQuery = _contentManager.HqlQuery().ForType("Page")
    .ForVersion(VersionOptions.Published)
    .Where(x => x.ContentPartRecord<TermsPartRecord>().Property("Terms", "terms"),
        x => x.And(a => a.Eq("Field", "ListingTaxonomy"), b => b.Eq("TermRecord.Id", term.Id)))
    .OrderBy(…)
    .List();

Though I'm struggling to get the query to work when sorting by a Numericfield I've added to 'Page' types 'SortOrder'?

I looked through the DB and found a record within ContentPartFieldDefinitionRecord, but I'm unsure if this is the table I should join to? I know it's a bit cheeky, but could you make any suggestions. Very appreciative of your solution. Regards, PP

Friday, August 5, 2016 5:02:22 AM bypigpen
  • pigpen
  • Lv. 02 Rookie
  • Total EXP: 58

Re: Help with returning Pages with a Taxonomy Term [HqlQuery]?

Oh yes. Need to leave so not fully tested but maybe add something like the following. I use DecimalFieldIndexRecords because as i remember, even with a 0 scale, decimal records are used. Notice that you need a dot at the end of Page.SortOrder..

    ...
    .Join(x => x.ContentPartRecord<FieldIndexPartRecord>()
    .Property("DecimalFieldIndexRecords", "fieldRecord"))
    .Where(x => x.Named("fieldRecord"), x => x.Eq("PropertyName", "Page.SortOrder."))
    .OrderBy(x => x.Named("fieldRecord"), x => x.Desc("Value"))
    .List();

Best.

Friday, August 5, 2016 8:05:06 AM byjtkech
  • jtkech
  • Lv. 13 Rookie
  • Total EXP: 1028

Re: Help with returning Pages with a Taxonomy Term [HqlQuery]?

Great work jtkech! To think of all the hours I've wasted Searching Taxonomy terms to edit content items makes me quite ill, but with your help, Orchard's dashboard will be a lot more friendlier. Thanks a million, PP

Friday, August 5, 2016 9:07:08 AM bypigpen
  • pigpen
  • Lv. 02 Rookie
  • Total EXP: 58

Re: Help with returning Pages with a Taxonomy Term [HqlQuery]?

Ooh, I forgot to mention that my "The Tree" module provides dashboard navigation through taxonomy terms. Might work for you, maybe.

Friday, August 5, 2016 7:38:31 PM bybleroy
  • bleroy
  • Lv. 08 Rookie
  • Total EXP: 527

Post a reply

You need to be signed in to post a reply.

Sign In