Forum

Thread tagged as: Runway

Collection archive...

Is there a way I might be able to mimic something like perch_blog_date_archive_months() with collections?

Phil Smith

Phil Smith 0 points

  • 6 years ago
Drew McLellan

Drew McLellan 2638 points
Perch Support

It would certainly be possible to do. Given a Collection and a date field, it should be possible to query the collection_index fairly efficiently.

I'll see if I can create an example.

That would be great. It feels like being able to archive collections would be a good feature.

Drew McLellan

Drew McLellan 2638 points
Perch Support

It would be a good feature.

Getting down to the metal, something like:

SELECT *, COUNT(*) AS qty FROM ( 
    SELECT idx.itemID, idx.indexValue, ci.collectionID, idx2.indexValue AS sortval
    FROM perch2_collection_index idx 
        JOIN perch2_collection_items ci ON idx.itemID=ci.itemID AND idx.itemRev=ci.itemRev AND idx.collectionID=ci.collectionID 
        JOIN perch2_collection_revisions cr ON idx.itemID=cr.itemID AND idx.itemRev=cr.itemRev AND idx.collectionID=ci.collectionID 
        JOIN perch2_collection_index idx2 ON idx.itemID=idx2.itemID AND idx.itemRev=idx2.itemRev AND idx2.indexKey='date' 
    WHERE (idx.collectionID=2) 
        AND idx.itemID=idx2.itemID 
        AND idx.itemRev=idx2.itemRev 
        AND idx.indexKey='date'
) AS tbl 
GROUP BY indexValue
ORDER BY sortval DESC

Where collectionID=2 is your collection and date is the ID of the date field.

Great. Thanks