Music store - need to sort by release date

Michael_King3
Tourist
24 0 2

Hello all. We need to sort our products by the release date of an item for the customer. Currently, we add items to Shopify based on when we enter them into our system, which is oldest to newest. That way, the "Sort By: Newest To Oldest" drop-down sorts by "creation_date". However, this sorting causes problems with many collections we have (e.g. Pre-orders, Back In Stock, etc.). We store the release date for every item in our local database.

Does anyone have any ideas on how to do this? (I know how to edit fields on the Shopify API)

In summary, we want our "Sort By: Newest to Oldest" and "Sort By: Oldest To Newest" to sort based on this Release Date field. I was thinking to edit the "updated-at" field on Shopify, but that field gets updated everytime we make a change to a product, so that won't work. Ideally, we could edit the "created_at" field, but I read everywhere this is not possible (I'm still not sure why but I'm sure Shopify has their reasoning).

Any help would be greatly appreciated as it's something we have been trying to do for months.

0 Likes
PaulNewton
Shopify Partner
2581 136 471

There is no native way to do this, i recommend a feature request to support@shopify.com for some sort of editable "manufacturing date", "inception date", etc the problem is of course naming the bloody thing because of the ambiguity with "created_at" being for the admin but not for the product. The argument will of course be "just use metafields", but you want native sorting and the problems that avoids.

  1. HACKS repurposing native sorting(allowable values) by some string parsing|splitting
    • Possible SEO problems
  2. Javascript, do all sorting on the client-side(customer)
    1. DO NO do this for large(multipage) collections or "heavy" pages without professional guidance.
  3. AJAX, place data in rendered output, a json object, or a callable page
    • Possible client-side(customer) performance problems
    • Permalink issues: how do you share the link to this sort?
  4. APPS, like query params, so you can filter via url.
    • This is generally still needing ajax

Data can stored in:

  • titles
  • tags
  • metafields
  • product names|handles
  • third party DB 

 

Problem Solved? ✔️Accept and ? Like the solution so you can help others.
Buy me a coffee ☕ paypal.me/paulnewton or donate to eff.org
Confused? Busy? Buy a custom solution paull.newton+shopifyforum@gmail.com
0 Likes
Michael_King3
Tourist
24 0 2

Paul,

I have revised this post since I have finally figured out how to sort by "Release Date" of a product. To those reading - his reply below this one does not refer to this post unless he modifies it.

For all reading, here are the instructions after finding no answer for months of searching these forums and online:

We will use the "published_at" field available for every product via the API. I used this date field because it is not being used for anything else that I'm aware of, unless you want an item to go live at a certain date. I had originally used the API to update the "Manual Sort" values, which worked for small collections but once a collection had a few hundred items, I ran into issues with the querystring size being to large that was being sent to the API

First, make sure you have an API key/password set up in order to use the Shopify API (Shopify Admin page > Apps > click "Private Apps" button in the upper-right > click "Create Private App" in upper-right. Give it a title (e.g. (YourStoreName) API and click "Save". It will then give you an API Key and password (you are now in business to use the Shopify API which will give you full control over your store - highly recommended everyone who has a store to set this up.)

Second, make sure every item in your database has the "ShopifyID" value so add this column to whatever table you store (no pun intended) your products locally. You will also need to have the Shopify Handle stored in your database.

Third, we will update every Shopify product via the API with a "published_at" value. This value will be the release date of the item, formatted in ISO format. Also note - published_at will not take a future date so we subtract one year from every release date in order to update all items, including pre-orders. Please note - the actual values of the release date do not matter for sorting (hence why I can subtract one year) - all that matters is that we are sorting our products, newest to oldest. This published_at date will not be viewable to anyone - we are strictly using it for sorting.

Fourth, we update our Shopify .liquid code to sort the collection by "published_at" (as opposed to "created_at") when displaying "Sort by: Date, newest to oldest"

And now the hard part - the coding! This coding is written in classic .asp - use your own programming language to modify the code. However, the logic is all right here.

I hope the below helps many people since this is a topic that has been going on for years and has never had a good answer... until now! :)

--------------------- Begin Get and Update ShopifyID Code - I run this on a nightly schedule in case we have added new items ---------------

apikey = (Your API Key)

pass = (Your API Password)

Set ServerXmlHttp = Server.CreateObject("MSXML2.ServerXMLHTTP.6.0")

Set Conn = Server.CreateObject("ADODB.Connection")

Conn.Open (Your Database Name)

' Populate ShopifyID column - one time run
sql = "SELECT ShopifyHandles.Handle FROM ShopifyHandles WHERE  ShopifyHandles.ShopifyID = '0' ORDER BY z_ShopifyHandles.ID"
Set rs = Conn.Execute(sql)

Do Until rs.EOF
    Handle = rs("Handle")
   
    If Handle <> HandleOld Then
        ' Pause for 1 second since Shopify API only allows on API call per second
        If intTurn = 1 Then
            intTurn = 0
            dtCurrent = Now
            Response.Flush
            Do Until Abs(DateDiff("s", dtCurrent, Now)) >= 1
                dummy = dummy & "a"
            Loop
        End If
        intTurn = intTurn + 1
        
        ' Get Shopify Product ID
        ServerXmlHttp.open "GET", "https://(YourStoreName).myshopify.com/admin/products.json?handle="; & Handle, false, apikey, pass
        ServerXmlHttp.send
       
        ResponseText = ServerXmlHttp.responsetext
        If InStr(ResponseText, "id"":") Then
            IDSplit = Split(ServerXmlHttp.responsetext, "id"":")
            IDSplit2 = Split(IDSplit(1), ",")
            ShopifyID = IDSplit2(0)
        
            sql2 = "UPDATE z_ShopifyHandles SET ShopifyID = '" & Int(ShopifyID) & "' WHERE Handle='" & Handle & "'"
            Set rs2 = Conn.Execute(sql2)
        Else
            Response.Write Handle & " = Not found! Missing item on Shopify<br>"
            Response.Flush
        End If
    End If
    
    HandleOld = Handle
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
Conn.Close
Set Conn = Nothing

Set ServerXmlHttp = Nothing

--------------------- End Get ShopifyID Code ---------------

 

 

--------------------- Begin Update Shopify "published_at" Code - I ran this one time for our entire database (depending on how many products you have, this could take a few minutes or an entire day since we can only update one product per second due to Shopify API limitations). It will be run for all future products added to our system ---------------------

 

apikey = (Your API Key)

pass = (Your API Password)

Set ServerXmlHttp = Server.CreateObject("MSXML2.ServerXMLHTTP.6.0")

Set Conn = Server.CreateObject("ADODB.Connection")

'54000 = 15 hours (This is timeout set for first time run due to large amount of products)
Conn.ConnectionTimeout = 54000
Conn.CommandTimeout = 54000
Server.ScriptTimeOut = 54000

Conn.Open "UGHHStore"

sql = "SELECT z_ShopifyHandles.Handle, z_ShopifyHandles.ShopifyID, Items.ID, Items.ReleaseDate FROM (z_ShopifyHandles INNER JOIN Items ON z_ShopifyHandles.UPC = Items.UPC) ORDER BY Items.ID"
Set rs = Conn.Execute(sql)

Do Until rs.EOF
    ShopifyID = rs("ShopifyID")
    
    If ShopifyID <> ShopifyIDOld Then
        Count = Count + 1
        Handle = rs("Handle")
        ItemID = rs("ID")
        ReleaseDate = rs(3)
       
        ReleaseDateSplit = Split(ReleaseDate, "/")
        ReleaseDateMonth = ReleaseDateSplit(0)
        ReleaseDateDay = ReleaseDateSplit(1)
        ReleaseDateYear = ReleaseDateSplit(2)

        If Len(ReleaseDateMonth) = 1 Then
            ReleaseDateMonth = "0" & ReleaseDateMonth
        End If
        
        If Len(ReleaseDateDay) = 1 Then
            ReleaseDateDay = "0" & ReleaseDateDay
        End If
       
        ReleaseDate = (ReleaseDateYear - 1) & "-" & ReleaseDateMonth & "-" & ReleaseDateDay & "T12:00:00-04:00"
        
        ' Pause for 1 second since Shopify API only allows on API call per second
        If intTurn = 1 Then
            intTurn = 0
            dtCurrent = Now
            Response.Flush
            Do Until Abs(DateDiff("s", dtCurrent, Now)) >= 1
                dummy = dummy & "a"
            Loop
        End If
        intTurn = intTurn + 1
            
        ' Update Product > "published_at" value
        ServerXmlHttp.open "PUT", "https://(YourStoreName).myshopify.com/admin/products/"; & ShopifyID & ".xml", false, apikey, pass
        ServerXmlHttp.setRequestHeader "Content-type","application/xml"
        ServerXmlHttp.send "<product><published_at>" & ReleaseDate & "</published_at></product>"

        If ServerXmlHttp.status <> 200 AND ServerXmlHttp.status <> 201 Then
            Response.Write "Error updating ShopifyID: " & ShopifyID & " (UPC: " & UPC & ") on Shopify! (" & ServerXmlHttp.status & ") (" & ServerXmlHttp.responsetext & ")<br>"
            Set ServerXmlHttp = Nothing
            Response.End
        End If

       ' List out each item getting updated so we can see progress      

        response.write ItemID & " | " & ReleaseDate & "<br>"
        response.flush
    End If
    
    ShopifyIDOld = ShopifyID
rs.MoveNext
Loop
rs.Close

Set rs = Nothing
Conn.Close
Set Conn = Nothing
Set ServerXmlHttp = Nothing

----------- End Code -----------

 

--------------------- Begin Shopify Liquid Sort Order ---------------

Update "collection-data.liquid". Since I do not know how your code looks, here is how mine does and you can modify as needed. The main thing I'm doing is checking if the sort being requested by either default or by the "sort by" dropdown is to sort by newest to oldest or oldest to newest. Depending on which is chosen, I use different product sorting loops (three different possibilities - newest to oldest, oldest to newest, or all others):

{% if collection.sort_by contains "-" and collection.sort_by <> "created-descending" and collection.sort_by <> "created-ascending" %}
    {% for product in collection.products %}
      {% include 'product-unpack' %}
          {% if product.available %}
          {% else %}
              {% if product.tags contains "dt:product" and current_tags contains "In Stock" %}
                  {% continue %}
              {% endif %}
          {% endif %}
          {% include 'product-grid-item' %}

        {% else %}
          <!-- COLLECTION_EMPTY -->
          {% if collection.handle == 'all' %}

            {% comment %}
              Add default products to help with onboarding for collections/all only
            {% endcomment %}
            {% unless emptyState %}
              {{ 'theme-onboarding.css' | global_asset_url | stylesheet_tag }}
              {% assign emptyState = true %}
            {% endunless %}
            {% include 'onboarding-empty-collection' %}

          {% else %}

            {% comment %}
              If collection exists but is empty, display message
            {% endcomment %}
            <div class="grid-item no-results">
              <p>{% if template == "collection.data" %}*EMPTY*{% endif %}{{ 'collections.general.no_matches' | t }}</p>
            </div>

          {% endif %}
        {% endfor %}
{% elsif collection.sort_by != "created-ascending" %}
    {% assign sorted_products = collection.products | sort:'published_at' %}
    {% for product in sorted_products reversed %}

      {% include 'product-unpack' %}
          {% if product.available %}
          {% else %}
              {% if product.tags contains "dt:product" and current_tags contains "In Stock" %}
                  {% continue %}
              {% endif %}
          {% endif %}
          {% include 'product-grid-item' %}

        {% else %}
          <!-- COLLECTION_EMPTY -->
          {% if collection.handle == 'all' %}

            {% comment %}
              Add default products to help with onboarding for collections/all only
            {% endcomment %}
            {% unless emptyState %}
              {{ 'theme-onboarding.css' | global_asset_url | stylesheet_tag }}
              {% assign emptyState = true %}
            {% endunless %}
            {% include 'onboarding-empty-collection' %}

          {% else %}

            {% comment %}
              If collection exists but is empty, display message
            {% endcomment %}
            <div class="grid-item no-results">
              <p>{% if template == "collection.data" %}*EMPTY*{% endif %}{{ 'collections.general.no_matches' | t }}</p>
            </div>

          {% endif %}
        {% endfor %}
{% elsif collection.sort_by == "created-ascending" %}
    {% assign sorted_products = collection.products | sort:'published_at' %}
    {% for product in sorted_products %}

      {% include 'product-unpack' %}
          {% if product.available %}
          {% else %}
              {% if product.tags contains "dt:product" and current_tags contains "In Stock" %}
                  {% continue %}
              {% endif %}
          {% endif %}
          {% include 'product-grid-item' %}

        {% else %}
          <!-- COLLECTION_EMPTY -->
          {% if collection.handle == 'all' %}

            {% comment %}
              Add default products to help with onboarding for collections/all only
            {% endcomment %}
            {% unless emptyState %}
              {{ 'theme-onboarding.css' | global_asset_url | stylesheet_tag }}
              {% assign emptyState = true %}
            {% endunless %}
            {% include 'onboarding-empty-collection' %}

          {% else %}

            {% comment %}
              If collection exists but is empty, display message
            {% endcomment %}
            <div class="grid-item no-results">
              <p>{% if template == "collection.data" %}*EMPTY*{% endif %}{{ 'collections.general.no_matches' | t }}</p>
            </div>

          {% endif %}
        {% endfor %}
{% endif %}

--------------------- End Code ---------------

0 Likes
PaulNewton
Shopify Partner
2581 136 471

Format source code is button on the far right of the post edit.

Provide all relevant code, that segment is useless without the forloop it should be output in.

Problem Solved? ✔️Accept and ? Like the solution so you can help others.
Buy me a coffee ☕ paypal.me/paulnewton or donate to eff.org
Confused? Busy? Buy a custom solution paull.newton+shopifyforum@gmail.com
0 Likes
jeremy16
Tourist
9 0 1

great solution. it's a shame you can only get the top 50 products this way.

https://help.shopify.com/themes/liquid/tags/iteration-tags#for

for loops can output a maximum of 50 results per page. In cases where there are more than 50 results, use the paginate tag to split them across multiple pages.

 you can't paginate on arrays either.

0 Likes