Hey Robert - I would consider creating a DynamicEntityCollectionProvider by either subclassing CachedEntityCollectionProviderBase<IInvoice> or CachedQueryableEntityCollectionProviderBase<T> (if you want to add additional filters.

The way they work is to query directly against the database to return a Page<Guid> that would match the invoice keys. So in your case you could right a query against the merchInvoiceItem table looking for your EntityTfKey that you defined for your custom line item and return the invoiceKey field for all invoices that contain that line item type.

If you do this, you could pretty easily wire up the dynamic collection to appear in the Merchello back office similar to the Unpaid, Paid, Partially Paid invoice collections (assuming you subclass the CachedQueryableEntityCollectionProviderBase<T>

Check out these providers to see if an implementation like this would work well for your client: https://github.com/Merchello/Merchello/tree/merchello-dev/src/Merchello.Core/EntityCollections/Providers

Utilizing the base classes, much of the work is already done - and once you get what is going on, they are actually fairly quick to write.

If you do decide you want the provider to show up in the back office, you would then add it to the Merchello.config file in the back office section:

 <backoffice>
  <!-- 1.11.0 adds default attributes localizeArea and localizeName with respective values of merchelloTree and the id att value e.g. products, sales, etc. -->
<tree id="products" title="Products" icon="icon-barcode" routePath="merchello/merchello/productlist/manage" visible="true" sortOrder="1" />
<tree id="sales" title="Sales" icon="icon-receipt-dollar" routePath="merchello/merchello/saleslist/manage" visible="true" sortOrder="2">
    <childSettings>
        <!--
            Sets the order where self managed entity collection providers (like Unpaid invoices) are listed in the tree.
            If set to true, the resolved collections will be listed before the user defined collections.  False will list them
            after.
        -->
        <setting alias="selfManagedProvidersBeforeStaticProviders" value="true" />
    </childSettings>
    <selfManagedEntityCollectionProviders>
        <!--
            attribute: key - the provider key defined in the EntityCollectionProviderAttribute decorating the EntityCollectionProvider
            attribute: icon - is the icon displayed in the tree
            attribute: visible - indicates whether or not the collection should provider should create a tree node
            attribute: ref - not actually used, it is only here so that they can be managed more easily
           -->
        <entityCollectionProvider key="A8120A01-E9BF-4204-ADDD-D9553F6F24FE" icon="icon-bills" visible="true" ref="Unpaid invoices" />
        <entityCollectionProvider key="82015B97-11E8-4E57-8258-A59E1D378E04" icon="icon-bills" visible="true" ref="Partially paid invoices" />
        <entityCollectionProvider key="072E0671-31BE-41E4-8CF9-4AEEC6CC5BC6" icon="icon-bills" visible="true" ref="Paid invoices" />
        <entityCollectionProvider key="5FD6E5EB-0B7C-41A4-B863-7AEC31BE84C0" icon="icon-truck" visible="true" ref="Unfulfilled orders" />
        <entityCollectionProvider key="A9A288F3-DA98-4712-9E90-F9F909F2C26A" icon="icon-truck" visible="true" ref="Open orders" />
        <entityCollectionProvider key="68B57648-7550-4702-8223-C5574B7C0604" icon="icon-truck" visible="true" ref="Fulfilled orders" />
    </selfManagedEntityCollectionProviders>
</tree>
<tree id="customers" title="Customers" icon="icon-user-glasses" routePath="merchello/merchello/customerlist/manage" visible="true" sortOrder="3" />
<tree id="marketing" title="Marketing" icon="icon-energy-saving-bulb" routePath="merchello/merchello/offerslist/manage" visible="true" sortOrder="4" />
<tree id="reports" title="Reports" icon="icon-file-cabinet" routePath="merchello/merchello/reportslist/manage" visible="false" sortOrder="5" />
<tree id="gateways" title="Gateway Providers" icon="icon-settings" routePath="merchello/merchello/gatewayproviderlist/manage" visible="true" sortOrder="6" />
</backoffice>

Hey Robert,

Ah, right - should have explained that a bit further. I would suggest just doing the actual database query directly via PetaPoco rather than going through the InvoiceService similar to the way you would do if you were to add a custom table to Umbraco. That way you don't have to break into the Merchello InvoiceRepository (which is internal) ...

1st, create a little poco:

[TableName("merchInvoice")]
[PrimaryKey("pk", autoIncrement = false)]
internal class KeyDto : IPageableDto
{
    [Column("pk")]
    public Guid Key { get; set; }
}

For the query with a search term, to save time, you could simply copy the build SQL method from the repository into a private method in your class (or a base class if you are going to do more than one).

https://github.com/Merchello/Merchello/blob/merchello-dev/src/Merchello.Core/Persistence/Repositories/InvoiceRepository.cs#L1146

Add a private method to do the actual query

    // valid orderExpression any field in merchInvoice table e.g.  "invoicenumber", "invoicedate", "billtoname", "billtoemail" 
    // sort direction is a public enum
    private Page<KeyDto> GetKeyDtoPage(long page, long itemsPerPage, Sql sql, string orderExpression, SortDirection sortDirection = SortDirection.Descending)
    {
        if (!string.IsNullOrEmpty(orderExpression))
        {
            sql.Append(sortDirection == SortDirection.Ascending
                ? string.Format("ORDER BY {0} ASC", orderExpression)
                : string.Format("ORDER BY {0} DESC", orderExpression));
        }

        var database = ApplicationContext.Current.Database;
        return database.Page<TDto>(page, itemsPerPage, sql);
    }

Then, in your PerformGetPagedEntityKeys method build your sql

  var sql = BuildInvoiceSearchSql(searchTerm);
        sql.Append("AND ([merchInvoice].[pk] IN (");
        sql.Append("SELECT DISTINCT(invoiceKey)");
        sql.Append("FROM [merchInvoiceItem]");
        sql.Append("WHERE [merchInvoiceItem].[lineItemTfKey] = @litf", new { @litf = [YOUR CUSTOM LineItemTfKey] });
        sql.Append(")");

and then call the GetKeyDtoPage.

You will get stuck again on internals with the GetPageFromKeyPage method (which I've now made public), but the method is pretty simple:

          public Page<IInvoice> GetPageFromKeyPage(Page<Guid> keyPage, Func<IEnumerable<IInvoice>> getter)
    {
        return new Page<IInvoice>()
        {
            Context = keyPage.Context,
            CurrentPage = keyPage.CurrentPage,
            ItemsPerPage = keyPage.ItemsPerPage,
            TotalItems = keyPage.TotalItems,
            TotalPages = keyPage.TotalPages,
            Items = getter.Invoke().ToList()
        };
    }

This method is really only used in the fallback in the cases the invoice does not exist in Merchello InvoiceIndex (Examine).

I'm really interested to hear how it goes. You've already pointed out a few things that would make these sorts of solutions easier =) Many thanks for that!

Oh - forgot to mention, if you only need this on the front end, you do not have to add it to the back office. Since the collection key is defined in the attribute, you can add it to your site constants and then query directly from the MerchelloHelper using the GetFromCollection method.

  var merchello = new MerchelloHelper();

  // method has a few overloads
  var results = merchello.Query.Invoice.GetFromCollection( ... );

This first queries the database for the page of keys only and then looks to Examine to fine matching invoices. If any of the invoices are not in Examine it will do the full graph query base on the missing Key as a fallback. This will be quicker for your front end.

is working on a reply...