XEP-0413: Order-By

Abstract
This specification allows to change order of items retrieval in a Pubsub or MAM query
Author
Jérôme Poisson
Copyright
© 1999 – 2021 XMPP Standards Foundation. SEE LEGAL NOTICES.
Status

Experimental

WARNING: This Standards-Track document is Experimental. Publication as an XMPP Extension Protocol does not imply approval of this proposal by the XMPP Standards Foundation. Implementation of the protocol described herein is encouraged in exploratory implementations, but production systems are advised to carefully consider whether it is appropriate to deploy implementations of this protocol before it advances to a status of Draft.
Type
Standards Track
Version
0.2 (2021-08-21)
Document Lifecycle
  1. Experimental
  2. Proposed
  3. Draft
  4. Final

1. Introduction

Publish-Subscribe (XEP-0060) [1] §6.5.7 allows to retrieve the "most recent items" and Message Archive Management (XEP-0313) [2] state in §3.1 that archives are ordered in "chronological order". While this order is straightforward in general use cases, it is sometimes desirable to use a different order, for instance while using Microblogging Over XMPP (XEP-0277) [3]: a spelling mistake correction should not bring an old blog post to the top of retrieved items.

This specification allows to explicitly change business logic to retrieve the items in a different order.

2. Requirements

3. Glossary

In XEP-0060, there is no such thing as "updated item". This XEP changes the business logic as follow:

4. Use Cases

4.1 Retrieve Items By Date of Creation

Juliet wants to retrieve plays of her favorite writer, William Shakespeare. She wants to retrieve the 3 most recent ones by date of creation.

To do so, her client do a regular Pubsub request, but adds the <order> element as a children of the <pubsub> element with the "urn:xmpp:order-by:1" namespace, a by attribute equal to creation and a desc attribute equal to true.

Example 1. Retrieving items ordered by date of creation
<iq type='get'
    from='juliet@capulet.lit/balcony'
    to='pubsub.shakespeare.lit'
    id=''>
  <pubsub xmlns='http://jabber.org/protocol/pubsub'>
    <items node='plays' max_items='3'/>
    <order xmlns='urn:xmpp:order-by:1' by='creation' desc='true'/>
  </pubsub>
</iq>
  

The Pubsub service then returns the 3 most recently created plays, first one being the most recent.

Example 2. Service returns all items
<iq type='result'
    from='pubsub.shakespeare.lit'
    to='juliet@capulet.lit/balcony'
    id=''>
  <pubsub xmlns='http://jabber.org/protocol/pubsub'>
    <items node='plays'>
      <item id='153214214'>
        <entry xmlns='http://www.w3.org/2005/Atom'>
          <title>Henry VIII</title>
        </entry>
      </item>
      <item id='623423544'>
        <entry xmlns='http://www.w3.org/2005/Atom'>
          <title>Tempest</title>
        </entry>
      </item>
      <item id='452432423'>
        <entry xmlns='http://www.w3.org/2005/Atom'>
          <title>Wintter's Tale</title>
        </entry>
      </item>
    </items>
  </pubsub>
</iq>

4.2 Retrieve Items By Date of Modification

Juliet realizes that there is a spelling mistake, it's "Winter's Tale" and not "Wintter's Tale". She fixes it by overwritting the item:

Example 3. Juliet Overwritte the Item to Fix It
<iq type='set'
    from='juliet@capulet.lit/balcony'
    to='pubsub.shakespeare.lit'
    id='orderby2'>
  <pubsub xmlns='http://jabber.org/protocol/pubsub'>
    <publish node='plays'>
      <item id='452432423'>
        <entry xmlns='http://www.w3.org/2005/Atom'>
          <title>Winter's Tale</title>
        </entry>
      </item>
    </publish>
  </pubsub>
</iq>

To check that everything is alright, she requests again the last 3 items, but this time by date of modification. To do so, the client proceeds the same way as for date of creation, except that it uses the value modification for the by attribute.

Example 4. Retrieving items ordered by date of modification
<iq type='get'
    from='juliet@capulet.lit/balcony'
    to='pubsub.shakespeare.lit'
    id='orderby3'>
  <pubsub xmlns='http://jabber.org/protocol/pubsub'>
    <items node='plays' max_items='3'/>
    <order xmlns='urn:xmpp:order-by:1' by='modification' desc='true'/>
  </pubsub>
</iq>
  

The Pubsub service returns again the 3 plays but the "Winter Tales" item has been overwritten recently, while the 2 others have never been overwritten, so it returns the items in the following order, with the most recently modified item on top:

Example 5. Service returns all items
<iq type='result'
    from='pubsub.shakespeare.lit'
    to='juliet@capulet.lit/balcony'
    id='orderby3'>
  <pubsub xmlns='http://jabber.org/protocol/pubsub'>
    <items node='plays'>
      <item id='452432423'>
        <entry xmlns='http://www.w3.org/2005/Atom'>
          <title>Winter's Tale</title>
        </entry>
      </item>
      <item id='153214214'>
        <entry xmlns='http://www.w3.org/2005/Atom'>
          <title>Henry VIII</title>
        </entry>
      </item>
      <item id='623423544'>
        <entry xmlns='http://www.w3.org/2005/Atom'>
          <title>Tempest</title>
        </entry>
      </item>
    </items>
  </pubsub>
</iq>

4.3 Use with MAM

With Message Archive Management (XEP-0313) [2] the logic is the same, but the <order> element is added as a child of the <query> element:

Example 6. MAM Pubsub Query with Ordering
<iq to='pubsub.shakespeare.lit' type='set' id='orderby4'>
  <query xmlns='urn:xmpp:mam:2' queryid='123' node='plays'>
    <order xmlns='urn:xmpp:order-by:1' by='creation'/>
  </query>
</iq>

This way, filters can be used with a specific ordering.

4.4 Reversing the Order

By default, ordering MUST be done in ascending order. This can be reversed by using the desc boolean attribute, which MAY have a value of either true or 1.

4.5 Using Order-By with RSM

This section provides a full example of using Order-By with Pubsub and RSM. For readability, we'll use a node with 4 items that will have following IDs (in order of their creation) A, B, C and D. Items C has been overwritten after D creation, and item A has been overwritten even later. Thus, when ascending creation order is requested, items are in order A, B, C, D. When ascending modification order is requested, items are in order B, D, C, A.
Let's see how this work when Juliet wants to retrieve all items in ascending modification order with RSM using a page size of 2 items:

Example 7. Juliet Retrieves First Page of Items with RSM
<iq id="rsm_1" type="get" from="juliet@capulet.lit/123">
    <pubsub xmlns="http://jabber.org/protocol/pubsub">
        <items node="balcony"/>
        <order xmlns="urn:xmpp:order-by:0" by="modification"/>
        <set xmlns="http://jabber.org/protocol/rsm">
            <max>2</max>
        </set>
    </pubsub>
</iq>
Example 8. Pubsub Returns First Page
<iq from="ordered_pubsub@capulet.lit" id="rsm_1" to="juliet@capulet.lit/123" type="result">
    <pubsub xmlns="http://jabber.org/protocol/pubsub">
        <items node="balcony">
            <item id="B" publisher="romeo@montaigu.lit/456">
                <payload xmlns="http://somenamespace.example.com">
                        item B
                </payload>
            </item>
            <item id="D" publisher="romeo@montaigu.lit/456">
                <payload xmlns="http://somenamespace.example.com">
                        item D
                </payload>
            </item>
        </items>
        <set xmlns="http://jabber.org/protocol/rsm">
            <first index="0">B</first>
            <last>D</last>
            <count>4</count>
        </set>
    </pubsub>
</iq>

Now Juliet wants to get the second and last page to complete her collection. She does this as usual with RSM, by using the value advertised in <last> element in a <after> element.

NOTE: in this example the value used in <last> element is the item ID, but as specified in Result Set Management (XEP-0059) [4], an implementation MAY use whatever makes sense to it, the requesting client MUST treat this as an opaque value.

Example 9. Juliet Retrieves the Second (and Last) Page of Items
<iq id="rsm_2" type="get" from="juliet@capulet.lit/123">
    <pubsub xmlns="http://jabber.org/protocol/pubsub">
        <items node="balcony"/>
        <order xmlns="urn:xmpp:order-by:0" by="modification"/>
        <set xmlns="http://jabber.org/protocol/rsm">
            <max>2</max>
            <after>D</after>
        </set>
    </pubsub>
</iq>
Example 10. Pubsub Service Returns Second Page
<iq from="ordered_pubsub@capulet.lit" id="rsm_2" to="juliet@capulet.lit/123" type="result">
    <pubsub xmlns="http://jabber.org/protocol/pubsub">
        <items node="balcony">
            <item id="C" publisher="romeo@montaigu.lit/456">
                <payload xmlns="http://somenamespace.example.com">
                        item C
                </payload>
            </item>
            <item id="A" publisher="romeo@montaigu.lit/456">
                <payload xmlns="http://somenamespace.example.com">
                        item A
                </payload>
            </item>
        </items>
        <set xmlns="http://jabber.org/protocol/rsm">
            <first index="2">C</first>
            <last>A</last>
            <count>4</count>
        </set>
    </pubsub>
</iq>

Juliets wonders which are the 2 last items created. To discover this, she request again the node, but this time with a creation order field, and in descending order:

Example 11. Juliet Retrieves Last Created Items
<iq id="rsm_3" type="get" from="juliet@capulet.lit/123">
    <pubsub xmlns="http://jabber.org/protocol/pubsub">
        <items node="balcony"/>
        <order xmlns="urn:xmpp:order-by:0" by="creation" desc='true'/>
        <set xmlns="http://jabber.org/protocol/rsm">
            <max>2</max>
        </set>
    </pubsub>
</iq>
Example 12. Pubsub Service Returns Last Created Items
<iq from="ordered_pubsub@capulet.lit" id="rsm_3" to="juliet@capulet.lit/123" type="result">
    <pubsub xmlns="http://jabber.org/protocol/pubsub">
        <items node="balcony">
            <item id="D" publisher="romeo@montaigu.lit/456">
                <payload xmlns="http://somenamespace.example.com">
                        item D
                </payload>
            </item>
            <item id="C" publisher="romeo@montaigu.lit/456">
                <payload xmlns="http://somenamespace.example.com">
                        item C
                </payload>
            </item>
        </items>
        <set xmlns="http://jabber.org/protocol/rsm">
            <first index="0">D</first>
            <last>C</last>
            <count>4</count>
        </set>
    </pubsub>
</iq>

Now she knows that last created item is D, and the one created before is C.

Please note that items are in descending order in the whole result set but also inside the RSM page (thus the first item here is D), and that in this order, this request returns the first page, so index is 0 here.

If Juliet wanted to retrieve the second page of items by descending order of creation, she would do like this:

Example 13. Juliet Retrieves Second Page of Last Created Items
<iq id="rsm_4" type="get" from="juliet@capulet.lit/123">
    <pubsub xmlns="http://jabber.org/protocol/pubsub">
        <items node="balcony"/>
        <order xmlns="urn:xmpp:order-by:0" by="creation" desc="true"/>
        <set xmlns="http://jabber.org/protocol/rsm">
            <max>2</max>
            <after>C</after>
        </set>
    </pubsub>
</iq>
Example 14. Pubsub Service Returns Second Page of Items Orderded by Descending Creation Date
<iq from="ordered_pubsub@capulet.lit" id="rsm_4" to="juliet@capulet.lit/123" type="result">
    <pubsub xmlns="http://jabber.org/protocol/pubsub">
        <items node="balcony">
            <item id="B" publisher="romeo@montaigu.lit/456">
                <payload xmlns="http://somenamespace.example.com">
                        item B
                </payload>
            </item>
            <item id="A" publisher="romeo@montaigu.lit/456">
                <payload xmlns="http://somenamespace.example.com">
                        item A
                </payload>
            </item>
        </items>
        <set xmlns="http://jabber.org/protocol/rsm">
            <first index="2">B</first>
            <last>A</last>
            <count>4</count>
        </set>
    </pubsub>
</iq>

4.6 Extending this Specification

This specification can be extended by further XEPs, proposing other kind of ordering in the 'by' attribute (e.g. ordering by filename for a file sharing service). But this is beyond the scope of this XEP, and a client should not assume that other ordering than "creation" and "modification" are available without further negotiation. Any new ordering specified in a other XEP SHOULD use the Clark notation to avoid any collision (i.e.: {some_namespace}some_ordering).

5. General Considerations

It is important to note the following points:

6. Discovering Support

If a server supports the "order by" protocol, it MUST advertize it including the "urn:xmpp:order-by:1" discovery feature (see Protocol Namespaces regarding issuance of one or more permanent namespaces) in response to a Service Discovery (XEP-0030) [5] information request.
In addition to the general feature support, an entity MUST indicated on which protocols Order-By can be used, by using the notation urn:xmpp:order-by:1@other_namespace, i.e. a concatenation of:

So if Order-By is implemented for Publish-Subscribe (XEP-0060) [1], the service MUST advertise urn:xmpp:order-by:1@http://jabber.org/protocol/pubsub. If Order-By is implemented for Message Archive Management (XEP-0313) [2], it is urn:xmpp:order-by:1@urn:xmpp:mam:2.
In the following example, the server example.org advertizes Order-By support, and indicates that it is implemented for Pubsub and MAM:

Example 15. Service Discovery information request
<iq from='example.org'
    id='disco1'
    to='example.com'
    type='get'>
  <query xmlns='http://jabber.org/protocol/disco#info'/>
</iq>
Example 16. Service Discovery information response
<iq from='example.com'
    id='disco1'
    to='example.org'
    type='result'>
  <query xmlns='http://jabber.org/protocol/disco#info'>
    …
    <feature var='urn:xmpp:order-by:1'/>
    <feature var='urn:xmpp:order-by:1@http://jabber.org/protocol/pubsub'/>
    <feature var='urn:xmpp:order-by:1@urn:xmpp:mam:2'/>
    …
  </query>
</iq>

7. Business Rules

Several ordering elements may be used, this allows to solve next levels of ordering in case of equality. In this case, the first ordering (i.e. the top most <order> element) is the main one, the second <order> element is used in case of equality, then the next one if a new equality happens and so on.

In case of equality, if no new <order> element is specified, the item order is not guaranteed and is up to the implementation (the implementation MUST keep this order consistent across requests though).

8. Implementation Notes

It may be difficult to find a correct value for <first> and <last> elements of RSM. Indeed, internal ID of items can't be suited for all orderings. For Pubsub service using a SQL database as backend, item ID (XMPP or internal) could be used with a window function such as row_number (supported by major database engines such as PostgreSQL, MariaDB/MySQL or SQLite) over the requested ordering. For instance, on a hypothetical table where items are requested by ascending creation then modification dates after the value ABC (which correspond to XMPP item ID in our case), a request similar to this could be used:

Example 17. SQL Query to Handle <after> value
WITH cte_1 AS 
 (SELECT items.id AS id, row_number() OVER (ORDER BY created ASC, modified ASC) - 1 AS item_index 
    FROM items 
    WHERE items.node_id = 123)
 SELECT cte_1.item_index, items.id, items.payload 
    FROM items JOIN cte_1 ON items.id = cte_1.id 
    WHERE cte_1.item_index > (SELECT cte_1.item_index 
        FROM cte_1 
        WHERE cte_1.id = "ABC")
    ORDER BY cte_1.item_index ASC
    LIMIT 10;

In this example, row_number is decreased by 1 to match RSM index (row_number starts at 1 while RSM index starts at 0), thus the item_index column can be used directly to fill RSM metadata. A Common Table Expression has been used for better readability.

9. Security Considerations

This document introduces no additional security considerations above and beyond those defined in the documents on which it depends.

10. IANA Considerations

This document requires no interaction with the Internet Assigned Numbers Authority (IANA) [6].

11. XMPP Registrar Considerations

11.1 Protocol Namespaces

This specification defines the following XML namespace:

11.2 Protocol Versioning

If the protocol defined in this specification undergoes a revision that is not fully backwards-compatible with an older version, the XMPP Registrar shall increment the protocol version number found at the end of the XML namespaces defined herein, as described in Section 4 of XEP-0053.

12. XML Schema

<?xml version='1.0' encoding='UTF-8'?>

<xs:schema
    xmlns:xs='http://www.w3.org/2001/XMLSchema'
    targetNamespace='urn:xmpp:order-by:1'
    xmlns='urn:xmpp:order-by:1'
    elementFormDefault='qualified'>

  <xs:element name='order' maxOccurs='unbounded'>
      <xs:complexType>
          <xs:attribute name='by' type='xs:string' use='required'/>
      </xs:complexType>
  </xs:element>

</xs:schema>

13. Acknowledgements

Thanks to Philipp Hörist, Evgeny xramtsov, Jonas Schäfer¸ and Holger Weiß for their feedback.


Appendices

Appendix A: Document Information

Series
XEP
Number
0413
Publisher
XMPP Standards Foundation
Status
Experimental
Type
Standards Track
Version
0.2
Last Updated
2021-08-21
Approving Body
XMPP Council
Dependencies
XMPP Core
Supersedes
None
Superseded By
None
Short Name
NOT_YET_ASSIGNED
Source Control
HTML

This document in other formats: XML  PDF

Appendix B: Author Information

Jérôme Poisson
Email
goffi@goffi.org
JabberID
goffi@jabber.fr

Copyright

This XMPP Extension Protocol is copyright © 1999 – 2020 by the XMPP Standards Foundation (XSF).

Permissions

Permission is hereby granted, free of charge, to any person obtaining a copy of this specification (the "Specification"), to make use of the Specification without restriction, including without limitation the rights to implement the Specification in a software program, deploy the Specification in a network service, and copy, modify, merge, publish, translate, distribute, sublicense, or sell copies of the Specification, and to permit persons to whom the Specification is furnished to do so, subject to the condition that the foregoing copyright notice and this permission notice shall be included in all copies or substantial portions of the Specification. Unless separate permission is granted, modified works that are redistributed shall not contain misleading information regarding the authors, title, number, or publisher of the Specification, and shall not claim endorsement of the modified works by the authors, any organization or project to which the authors belong, or the XMPP Standards Foundation.

Disclaimer of Warranty

## NOTE WELL: This Specification is provided on an "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, express or implied, including, without limitation, any warranties or conditions of TITLE, NON-INFRINGEMENT, MERCHANTABILITY, or FITNESS FOR A PARTICULAR PURPOSE. ##

Limitation of Liability

In no event and under no legal theory, whether in tort (including negligence), contract, or otherwise, unless required by applicable law (such as deliberate and grossly negligent acts) or agreed to in writing, shall the XMPP Standards Foundation or any author of this Specification be liable for damages, including any direct, indirect, special, incidental, or consequential damages of any character arising from, out of, or in connection with the Specification or the implementation, deployment, or other use of the Specification (including but not limited to damages for loss of goodwill, work stoppage, computer failure or malfunction, or any and all other commercial damages or losses), even if the XMPP Standards Foundation or such author has been advised of the possibility of such damages.

IPR Conformance

This XMPP Extension Protocol has been contributed in full conformance with the XSF's Intellectual Property Rights Policy (a copy of which can be found at <https://xmpp.org/about/xsf/ipr-policy> or obtained by writing to XMPP Standards Foundation, P.O. Box 787, Parker, CO 80134 USA).

Visual Presentation

The HTML representation (you are looking at) is maintained by the XSF. It is based on the YAML CSS Framework, which is licensed under the terms of the CC-BY-SA 2.0 license.

Appendix D: Relation to XMPP

The Extensible Messaging and Presence Protocol (XMPP) is defined in the XMPP Core (RFC 6120) and XMPP IM (RFC 6121) specifications contributed by the XMPP Standards Foundation to the Internet Standards Process, which is managed by the Internet Engineering Task Force in accordance with RFC 2026. Any protocol defined in this document has been developed outside the Internet Standards Process and is to be understood as an extension to XMPP rather than as an evolution, development, or modification of XMPP itself.

Appendix E: Discussion Venue

The primary venue for discussion of XMPP Extension Protocols is the <standards@xmpp.org> discussion list.

Discussion on other xmpp.org discussion lists might also be appropriate; see <https://xmpp.org/about/discuss.shtml> for a complete list.

Errata can be sent to <editor@xmpp.org>.

Appendix F: Requirements Conformance

The following requirements keywords as used in this document are to be interpreted as described in RFC 2119: "MUST", "SHALL", "REQUIRED"; "MUST NOT", "SHALL NOT"; "SHOULD", "RECOMMENDED"; "SHOULD NOT", "NOT RECOMMENDED"; "MAY", "OPTIONAL".

Appendix G: Notes

1. XEP-0060: Publish-Subscribe <https://xmpp.org/extensions/xep-0060.html>.

2. XEP-0313: Message Archive Management <https://xmpp.org/extensions/xep-0313.html>.

3. XEP-0277: Microblogging over XMPP <https://xmpp.org/extensions/xep-0277.html>.

4. XEP-0059: Result Set Management <https://xmpp.org/extensions/xep-0059.html>.

5. XEP-0030: Service Discovery <https://xmpp.org/extensions/xep-0030.html>.

6. The Internet Assigned Numbers Authority (IANA) is the central coordinator for the assignment of unique parameter values for Internet protocols, such as port numbers and URI schemes. For further information, see <http://www.iana.org/>.

Appendix H: Revision History

Note: Older versions of this specification might be available at https://xmpp.org/extensions/attic/

  1. Version 0.2 (2021-08-21)

    Add a way to discover on which protocols Order-By applies

    Remove references to SQL (except in implementation notes)

    Specify that order-by operate on the whole item set and inside a RSM result set

    Explicitly says that creation and modification dates are set by Pubsub service itself

    Specify that Clark notation should be used for extensions

    Add a full example with Pubsub and RSM

    Add hint for SQL based implementations

    removed XEP-0060 and XEP-0313 as dependencies, they are mentioned as use cases, but are not mandatory

    better wording following feedback

    Namespace bump

    jp
  2. Version 0.1.1 (2019-08-20)

    Editorial language fixes

    edhelas
  3. Version 0.1.0 (2019-02-04)
    Accepted by vote of Council on 2018-01-09.
    XEP Editor (jsc)
  4. Version 0.0.1 (2019-01-05)

    First draft.

    jp

Appendix I: Bib(La)TeX Entry

@report{poisson2019xep0413,
  title = {Order-By},
  author = {Poisson, Jérôme},
  type = {XEP},
  number = {0413},
  version = {0.2},
  institution = {XMPP Standards Foundation},
  url = {https://xmpp.org/extensions/xep-0413.html},
  date = {2019-01-05/2021-08-21},
}

END