Some background information
As I described in my recent Twitter thread, I spent most of my day last Friday refactoring one function. I always wanted it to use Ecto queries but was not quite sure how to get there. As I usually do, I start with whatever first solution I could come up with and then I refactor. I went from using some functions in the Enum module and iterating over two lists, to using raw SQL, and then to my Ecto queries.
Along the way I made some silly mistakes, learned some new things, and successfully refactored even further to one Ecto query.
Phase 1: build a working function
The purpose of this function, as indicated by its name, was to get all the forms that did not have a sponsor. While Form and Campaign records each have a form_id field, they are not associated with each other. There is not a field in the Form record that would denote it is not in use. Although if there were, I could update it when a campaign with the same form_id changed its status. A form that is not part of an active campaign is said to not have a sponsor.
I was not sure how to get the results I wanted using Ecto queries. I decided the best course of action would be to write some code that works and then figure things out from there. The idea was I would get a list of all forms, get a list of all active campaigns, iterate through both of them, and return a list of the ones without a sponsor.
I knew based on the number of forms and active campaigns, I should end up with a list of 112 forms. Unfortunately, it took me a good deal of time to come up with a solution. It was not a great solution but it worked and that is what mattered.
I would iterate through every form. With each form I would then iterate through all the active campaigns. If the form_id of the two matched, I would return true. The end result for each form would be a list. The list would have all nil values or one would be true with the rest nil. Finally, I would check the list with Enum.member? to see if it contained a true value.
This process is started with Enum.reject. It rejects anything that returns a truthy value. The end result is the 112 forms that did not have a sponsor.
You may have noticed the part of the code in the red box. As was pointed out by @zimt28 on Twitter, this case statement was completely unnecessary. Writing true -> true and false -> false should have tipped me off. So that was awkward. I think I was too excited to have had a working solution in place. I should have used Enum.member?(values, true) without the case statement.
Phase 2: turning it into a SQL query
When I built the function last Tuesday, it was done with the intention of being refactored into Ecto queries. Yet, I was not sure how I would go about doing that. When I was taking my morning walk Friday, it dawned on me. If I could get the same result with raw SQL, I should be able to turn that into an Ecto query.
I got to work on Friday writing the SQL query. I am not great with SQL so this was a challenge. I connected to my dev environment database with pgAdmin, opened up the query tool, and began the many hours of trial and error. I had no idea how to use a query within a query to get the results I wanted.
Eventually with the help of Google I arrived at a solution. Using parentheses, I could use a query within a query. In my solution below, you will see this in action with the second select statement. I knew it was ugly, I knew there had to be a better way, but it worked and again, that was all that mattered.
As was later pointed out by @egze on Twitter, I could have arrived to a simpler solution with a LEFT JOIN. He also included this nice graphic from a Reddit post which was helpful.
Phase 3: turning SQL into Ecto queries
Now that I had my complicated raw SQL, it was time to find a way to turn that into Ecto queries. What made this tricky was figuring out how to use my WHERE NOT EXISTS. I could not find anything like that with Ecto. Thankfully, I came across EXCEPT_ALL which saved the day.
My first query returned all the forms that were associated with an active campaign. Then my second query used EXCEPT_ALL to return all forms except all the forms that were part of the first query. Just like that my long journey of refactoring was a success.
That was my original solution. But after getting the SQL suggestion on Twitter, I was able to further refactor and reduce the function down to a single Ecto query.
And there you have it! I felt really good getting this done. While it took several hours to get to this point, it was not so much about the solution but the journey along the way. This is part of why I love programming.
Top comments (0)