When I’ve had to write similar code in Rails, I’ve found the in-built query language to be quite frustrating, and I’ve usually dropped down to raw SQL to get the job done. But, this is my first major Elixir project, so I decided to try and do everything using Ecto, and I have to say it’s a complete joy to use. Building up a very complex SQL statement from composable query clauses keeps the code very clear and concise. But, that might be the subject of another blog post. For this post, I just want to highlight one specific feature.
At one point in my SQL, I need to select the top N records from a table, in descending order of
value. If there are records with the same
value, I want a random selection.
In SQL, I would do something like this;
SELECT * FROM mytable ORDER BY `value` DESC, RAND() LIMIT 5;
Using Ecto, the first part is easy enough (assuming
MyObject is an Ecto model);
from obj in MyObject, order_by: [desc: obj.value], limit: 5
RAND() part was a little trickier. My first try was this;
from obj in MyObject, order_by: [desc: obj.value, asc: "RAND()"], limit: 5
But, that just results in a SQL statement with the string literal
'RAND()' in the ORDER BY clause, which does nothing.
After a little bit of digging, I found Ecto’s fragment/1 function, which allows you to send expressions directly through to the database, with no interpolation. So, the working version of my code looks like this;
from obj in MyObject, order_by: [desc: obj.value, asc: fragment("RAND()")], limit: 5