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>