Pony ORM - Order by specific order
<p>Performing a Pony ORM query and attempting to sort the query by three attributes that live on the model. First by song type, which can be one of the five values listed in <code>ssf_type_order_map</code>, then by duration (int), and uuid (string). </p>

<p>For song type, I would like to have the songs sorted in the following order: Full, Full (Instrumental), Shorts, Loops, Stems</p>

<p>If I attempt to sort using the following <code>.order_by()</code> call, it doesn't return any errors but it doesn't sort by the type as I need it in the aforementioned order (duration and UUID sorting works fine though).</p>

<pre><code>song_source_files = self.song_source_files.select(lambda ssf: True).order_by(lambda ssf: (ssf.type, ssf.duration, ssf.uuid))
</code></pre>

<p>This is what I would think would be the ideal query, map the string types to a map that ranks their ordering.</p>

<pre><code>ssf_type_order_map = {
    'Full': 1,
    'Full (Instrumental)': 2,
    'Shorts': 3,
    'Loops': 4,
    'Stems': 5
}

song_source_files = self.song_source_files.select(lambda ssf: True).order_by(lambda ssf: (ssf_type_order_map[ssf.type], ssf.duration, ssf.uuid))
</code></pre>

<p>But I get an error when running this saying "Expression <code>ssf_type_order_map</code> has unsupported type 'dict'.</p>

<p>The Pony ORM docs on order_by <a href="https://docs.ponyorm.com/crud.html#sorting-of-query-results" rel="nofollow">here</a>, are very vague on using lambdas in this context.</p>

<p><strong>Update - Sept 7th</strong></p>

<p>I've also tried adding the following getter property on the model as follows:</p>

<pre><code>@property
def source_type(self):
  ssf_type_order_map = {
    'Full': 1,
    'Full (Instrumental)': 2,
    'Shorts': 3,
    'Loops': 4,
    'Stems': 5
  }

  return ssy_type_order_map[self.type]
</code></pre>

<p>I then try ordering the query as follows:</p>

<pre><code>song_source_files = self.song_source_files.select(lambda ssf: True).order_by(lambda ssf: (ssf_type_order_map[ssf.type], ssf.duration, ssf.uuid))
</code></pre>

<p>But I receive an error basically saying that the model does not have this property. My assumption based off of a similar issue with Django's ORM is that you can only access attributes that are present in the database models. </p>

<p>If that is the case with Pony as well, how does one pull off something that I would like to accomplish?</p>

<p>At first, I want to say that Pony distinguishes two types of sub-expressions: external expressions and correlated expressions. External expressions don't depend on the value of a generator loop variable(s), while correlated expressions do. Consider the following example:</p>

<pre class="lang-py prettyprint-override"><code>from some_module import f, g

x = 100
query = select(e for e in MyEntity if e.attr &gt; f(x, 200) and g(x, e))
</code></pre>

<p>In this query we have two sub-expressions: the first is <code>f(x, 200)</code> and the second is <code>g(x, e)</code>. The former is considered by Pony as external expression because it doesn't use any loop variable. In that case Pony assumes that it is possible to calculate the value of the expression in Python before query execution, and then translate the expression into a single parameter. For such expressions Pony doesn't impose any restrictions on which Python functions can be used inside them, because a result of such an expression is just a single value evaluated in Python.</p>

<p>The second expression <code>g(x, e)</code> cannot be evaluated in Python, because it depends on the value of the loop variable <code>e</code>. The result of such expression may be different for different table rows. Therefore, Pony needs to translate such expressions to SQL. Not every Python expression can be translated to SQL, and <code>g</code> needs to be a function which Pony specifically know how to translate. Pony defines a subset of Python operations which can be translated. This subset includes arithmetic operations on numeric types, string methods such as <code>startswith</code>, <code>endswith</code>, <code>in</code>, etc., and aggregated functions such as <code>sum</code> and <code>max</code>. </p>

<p>In your code, when you write</p>

<pre class="lang-py prettyprint-override"><code>.order_by(lambda ssf: (ssf_type_order_map[ssf.type], ssf.duration, ssf.uuid))
</code></pre>

<p>the expression <code>ssf_type_order_map[ssf.type]</code> refers to the object variable <code>ssf</code>, and hence will have different values for each table row, so this is correlated expression and Pony needs to translate that expression into SQL. Currently Pony doesn't understand how to perform such specific translation, but in principle this is doable. The result of translation will the following SQL <code>CASE</code> statement:</p>

<pre class="lang-sql prettyprint-override"><code>ORDER BY CASE ssf.type
    WHEN 'Full' THEN 1
    WHEN 'Full (Instrumental)' THEN 2
    WHEN 'Shorts' THEN 3
    WHEN 'Loops' THEN 4
    WHEN 'Stems' THEN 5
    ELSE 0
END
</code></pre>

<p>The good news is that you can write such expression in Pony using Python if-expression syntax:</p>

<pre class="lang-py prettyprint-override"><code>(1 if ssf.type == 'Full' else
 2 if ssf.type == 'Full (Instrumental)' else
 3 if ssf.type == 'Shorts' else
 4 if ssf.type == 'Loops' else
 5 if ssf.type == 'Stems' else 0)
</code></pre>

<p>At this moment Pony does not support decompiling if-expressions yet, so if you attempt to write such code directly, you will get an exception. As a workaround you need to pass the source of a lambda function as a string. In this case it will be translated just right, because we can directly parse the string to AST without decompiling. So you can write:</p>

<pre class="lang-py prettyprint-override"><code>song_source_files = self.song_source_files.select().order_by("""
    lambda ssf: ((1 if ssf.type == 'Full' else
                  2 if ssf.type == 'Full (Instrumental)' else
                  3 if ssf.type == 'Shorts' else
                  4 if ssf.type == 'Loops' else
                  5 if ssf.type == 'Stems' else 0),
                 ssf.duration, ssf.uuid)
""")
</code></pre>

<p>This should work perfectly, but I'd recommend to solve this problem in another way: we can have the <code>SourceFileType</code> entity with the <code>name</code> and <code>code</code> attributes and then order <code>ssf</code> records by <code>ssf.type.code</code> value:</p>

<pre class="lang-py prettyprint-override"><code>class SongSourceFile(db.Entity):
    name = Required(str)
    type = Required(lambda: SourceFileType)
    duration = Required(timedelta)
    uuid = Required(uuid.UUID, unique=True, default=uuid.uuid4)

class SourceFileType(db.Entity):
    name = Required(str)
    code = Required(int)
    files = Set(lambda: SongSourceFile)
</code></pre>

<p>Then it becomes possible writing the query in the following way:</p>

<pre class="lang-py prettyprint-override"><code>song_source_files = self.song_source_files.select().order_by(
    lambda ssf: (ssf.type.code, ssf.duration, ssf.uuid)
)
</code></pre>

<p>I think this approach is more universal, because now you can add other useful attributes to <code>SourceFileType</code> besides <code>name</code> and <code>code</code> and use them in queries too.</p>

python sorting lambda ponyorm