Don Pflaster

Ecto Composable Queries

For a while, I wondered how it was possible to prepare a query in Ecto that involves a join, then pass that query on and add a condition based on that join.

q = (from r in Rule, join: e in Event, on: e.rule_id ==
(from e in q, where: == ^"Hello")

The above example will look for the name “Hello” on the Rule table, not the Event table. The answer is to use the where function, which accepts a list of bindings as the second argument.

where(q, [_, e], == ^"Hello")

Any bindings that you’ve prepared in the previous query will be passed in the order that they were joined.

Posted in Ecto, Elixir

The great Vision Quest of 2016

Occasionally one starts to feel stagnant in a world of ever-faster-evolving technologies. More and more sites seem to be adopting transpiled Javascript frameworks, and thus it’s time to start hitting some education. I’ve chosen React, as that’s what seems to be popping up most in my circles.

For fun, I’m also learning how to program for the Unreal Engine. I’ve always had a passing interest in game design – and it’s a really cool excuse to revisit C++ programming after what’s probably a solid decade of scripting languages.

Udemy is a site I hadn’t visited before for web courses – I really like their design. Links:

React Course
Unreal Course

Posted in Uncategorized

Ecto and tricky joined tables

Elixir’s Ecto framework has a lot of quirks. For the first time, I wanted to try to sort on a table that I joined up to another table using a LEFT JOIN. Furthermore, these fields are in a JS Datatable, and I wanted to be able to dynamically sort the query based on what header the user clicks on.

I therefore needed to be able to switch the table in my ORDER BY clause. The original query was as follows:

query = from a in query,
     left_join: ag in assoc(a, :agents),
     order_by: [{:asc, ag.status}],
     select: {a, ag}

In this query, the joined dataset will always be sorted by the “status” column in the associated “agents” table. But what if I want to sort it by fields from either table dynamically? You can’t really select “a” or “ag” dynamically in this case, nor can you pass them to functions.

Ultimately, I came up with this solution that transforms the query object based on the field I want to sort. Given variables “col” and “dir” set earlier which are atoms representing the fields and direction of sort:

query = (from a in query,
   left_join: ag in assoc(a, :agents),
     select: {a, ag})
   |> sort_field(col, dir)

def sort_field(query, col, dir) do
   case col do
     :agent_status -> from [a, ag] in query, order_by: [{^dir, field(ag, ^:status)}]
     :agent_ttl ->    from [a, ag] in query, order_by: [{^dir, field(ag, ^:price)}]
     _ ->             from [a, ag] in query, order_by: [{^dir, field(a, ^col)}]

Now the two fields from the join table (agent_status and agent_ttl) will sort based on the “ag” table reference, and anything else will use the “a” table reference.

Posted in Uncategorized

Ecto EXISTS clauses with stuff in ’em

I have always loved the EXISTS() clause in PostgreSQL. It’s a super-convenient way of finding records that have associated records in another table without actually joining that table into your main query. Great for when you’re only interested as to whether those records exist and not what’s in them. If you are joining to the many side of your relationship, it also easily skirts display of duplicate records.

With ActiveRecord in Rails, it was always relatively easy to construct an EXISTS() clause, as ActiveRecord isn’t particularly picky about string interpolation. Elixir’s Ecto is – if you try to use the fragment() method to construct a where clause, it’s going to yell at you if it has any string interpolation in it.

Thus, if you want to construct your own IN() clause from a list, you can’t do this:

where: fragment(
"SELECT 1 FROM table_x WHERE table_x.y_id = AND myfield in (#{Enum.join(my_awesome_id_list, ",")})"

You must instead do this:

where: fragment(
"SELECT 1 FROM table_x WHERE table_x.y_id = AND myfield"
) in ^my_awesome_id_list

That’s all well and good – but I wanted to have a query that was wrapped in an EXISTS() clause. So how can I do that when Ecto is tacking on this IN() clause after my fragment?
I discovered that fragment methods are nestable:

where: fragment(
"SELECT 1 FROM table_x WHERE table_x.y_id = AND myfield"
) in ^my_awesome_id_list

I’m glad that capability is there, because EXISTS() clauses are quite convenient and performant. I wish Ecto was a bit less picky, but I suppose it’s forcing me to make better choices in my code.

Posted in Uncategorized Tagged with: ,

Selenium Grid Status as JSON

Oy, modern developers want things packaged so nicely for them. I’ve become one of them over the last few years.

Which is why I was surprised to see that Selenium Grid, which has a bunch of other API calls, doesn’t just have a simple one which will list all of the browsers currently running on it and their status. Especially surprising because somebody went to the trouble of building a whole web interface which you can access at http://GRID_HOST/grid/console.

Was about to write a package to parse the frickin’ HTML out of that web interface, but then found that somebody already did it.

Check out on Github.

Posted in Uncategorized

Go on, Emp.ex!

Had a great time attending the first ever Empire City Elixir Conference today.

It’s really awesome to see how excited people are about a new language – the momentum is palpable and I truly believe this is where most heavy hitters in the Ruby community are gravitating, or will be.

What I found especially inspiring was a talk by Bruce Tate, author of Seven Languages in Seven Weeks. Loved his story about how he met José Valim, creator of Elixir, and how they had a mutual fanboy moment as they realized that each had inspired the other in their work.

Posted in Uncategorized Tagged with:

Easy-peasy major PostgreSQL version upgrade for Mac

The thought of doing a major version upgrade in PostgreSQL (in this case, 9.4.5_2 to 9.5.3), always gives me the heebie-jeebies.

It could be that a couple years ago, when we finally got our 9.1 database at my startup out of the dark ages and went to 9.3, it wasn’t an easy one. We were running the PostGIS extension and we couldn’t use pg_upgrade with that, we had to do a full dump and reload. These days, though, I’m running a much simpler DB and upgrading my development machine’s version was super-easy.

At the end of the day, it’s all about just making a second data directory, porting the data over, then swapping directories. This link will show you how to do it in minutes. Don’t be skeered.

Posted in Uncategorized

Interacting with ReactJS forms as an outsider

Recently I was attempting to automate data entry on a site that was powered by ReactJS. We’re definitely in a new world of tools that the kids are using these days – you can’t just plug data into an input box and submit a form like olden times when they use these Javascript frameworks.

ReactJS uses a “Shadow DOM” behind the scenes, and any changes you make to the traditional HTML DOM will not change those values. I initially thought my cause was dismal. But then a colleague pointed out that tools like 1Password are somehow able to fill values into those boxes and make them stick. I then went down a Javascript Event rabbit hole and figured out how to change values for inputs and select dropdowns as though I were a real user interacting with these elements.

The site also wasn’t using jQuery, so I had to go pure Javascript on it.

For input controls
To begin interacting with a text input, first select the element:

box = document.querySelector('#thebox');

Fire a focus event:

var event = document.createEvent('FocusEvent');
event.initEvent('focus', true, true);

Then iterate over each character you want to type and fire a “textInput” event. In this case, the character is “3”:

var event = document.createEvent('TextEvent');
event.initTextEvent('textInput', true, true, window, "3");

Be sure to blur afterward, just in case any UI updates depend on you being done with that box:

var event = document.createEvent('FocusEvent');
event.initEvent('blur', true, true);

For select dropdowns
Set the input of the control as normal, but then follow it up with an “input” and a “change” event.

select = document.querySelector('#theselect');

select.value = "the option value";

var event = document.createEvent('Event');
event.initEvent('input', true, true);

var event = document.createEvent('Event');
event.initEvent('change', true, true);

For buttons and radio controls, a click is still a click, so any of those can be interacted with traditionally.

Posted in Javascript, ReactJS

Controlling Amazon EC2 instances with Elixir

Had a need to start, stop, create, and terminate EC2 instances from my Elixir-based Phoenix Framework app.
While there isn’t yet a solid EC2 management library for Elixir, there’s a full-on solid one for Erlang called Erlcloud which does just about anything you need to do.

Most of the examples I found out for using the library were in Erlang, but I was lucky enough to stumble upon some gists of people making equivalent calls in Elixir.

After including erlcloud in your project, configure your ec2 creds somewhere in your module.

@ec2'your access key', 'your secret key')

If you plan to spawn new instances, you will need to import the ec2_instance_spec:

Record.defrecord :ec2_instance_spec, Record.extract(:ec2_instance_spec, from: "deps/erlcloud/include/erlcloud_ec2.hrl")

If there is an image you’d like to use as the basis for your newly-spawned server (as I will show in the example below), set it as a constant also. NOTE THE SINGLE QUOTES. Normally Elixir libraries would use double-quoted strings, but Erlang libraries generally prefer single-quoted character strings.

@image_id 'instance-id'

Then follow the example of these methods to make the calls. Keep in mind that since this is an Erlang library, it very much likes to receive char lists instead of strings. Thankfully, calling to_char_list on a char list returns itself, so you don’t need to worry about what you’re passing in all that much.

  def start_instances(aws_ids) do
:erlcloud_ec2.start_instances(, fn x -> to_char_list x end), @ec2)

def stop_instances(aws_ids) do
:erlcloud_ec2.stop_instances(, fn x -> to_char_list x end), @ec2)

def create_new_instances(number) do
spec = ec2_instance_spec(image_id: @image_id,
instance_type: 'c4.xlarge',
key_name: 'your-ssh-key',
group_set: ['default', 'some_other_security_group'],
min_count: number,
max_count: number)
:erlcloud_ec2.run_instances(spec, @ec2)

def terminate_instances(aws_ids) do
:erlcloud_ec2.terminate_instances(, fn x -> to_char_list x end), @ec2)

Posted in Elixir, Phoenix

Phoenix Framework – Setting an associated ID back to nil using a form

When using a Phoenix Framework form, I had a select box on my page which had an option to set a belongs_to value to nil.

<%= select f, :relation_id, 
  Enum.into(, fn p -> {,} end),
[{"None", nil}]) %>

The form would usually send the ID, but when the nil value is selected, it passed the value as an empty string:

"relation_id" => ""

When I pass this into an Ecto changeset, it throws an error that the changeset is invalid, as it expects an integer for this ID. I could probably have intercepted the map, set the value to null, and passed the updated map into the changeset. But there was a much easier way to do this.

In the controller that receives the params, throw in the following plug which will strip params that are empty strings and convert them to nils:

defmodule MyApp.SomeThingController do
use MyApp.Web, :controller

plug :scrub_params, "some_thing" when action in [:create, :update]

# def ....

Where “some_thing” is the object in your params that has the “relation_id” field. Thanks to Stack Overflow!

Posted in Ecto, Elixir, Phoenix