Creating a chronological list from 2 channels and sorting by 2 different fields

Question

I'm building a loyalty scheme system in which customers log purchases they have made and when approved they are allocated points. These points are used to 'purchase' rewards.

I have most of the system working but I need to create a transaction history and this is proving to be tricky.

Basically, I have 2 channels;

Invoices - in which the entries represent customer invoices that are awarded points.

Rewards - in which the order for items they claim are recorded

The invoices channel contains a date field to record the date of the invoice (not the entry date since this is different). I need to retain the entry date as the date the entry was made for admin purposes.

The rewards channel uses the standard entry date to record when the purchase is made.

The transaction history needs to show a mixed list pulling entries from both channels - easy enough. BUT I need to sequence them chronologically using the invoice date and the reward entry date.

I'm using this in my entries tag at the moment;

{exp:channel:entries channel="invoices|rewards" dynamic="off" status="approved|open|closed|pending|declined" show_future_entries="yes" show_expired="yes" orderby="invoice-date|entry_date" sort="asc"}

As you can imagine, this isn't working as it groups each channel's entries together.

As I've been writing this it has occurred to me that the simplest solution would be to add another date field to the purchases which replicates the entry date then just sort on the 2 custom fields. I'm not sure this will work but might be worth a try though I can see other problems manifesting if I do that.

A better option might simply be to use the query module now I think of it. But again I fear I may run into a similar issue.

If anyone has an idea on this I'd be grateful!

Answer 1

I think I would go with adding a reward_date field to your other channel, this will also make things more obvious for any other devs picking up the project later on.

You should be able to write a simple extension to do this using the publish-form-entry-data hook:

http://ellislab.com/expressionengine/user-guide/development/extension_hooks/cp/content_publish/index.html#publish-form-entry-data

All you need to do is mirror your entry date 'if' then entry belongs to the relevant channel to the 'reward_date' field. :)

Answer 2

Stash has multisort, and sublists.
Just add both to a stash list (or one of the two to a sublist)

Depending if you want both in one list mixed up together,
then fill a stash list {stash:varname} with the first datefield,
then append the list with the other channel, and output the whole list,
ordered by said varname (datefield).

If you want to keep the rewards organised together with the invoices
then you can work with sublists. e.g. fetch all invoices,
and in each loop, fetch the rewards where the joining key matches.

Stash behaves just like a multi-dimensional array,
and also supports array_multisort.

https://github.com/croxton/Stash/wiki/Lists