Hacking Ecto: Constructing Ecto Query from Raw SQL
Tags: elixir, ecto, sql
Published: 2021-01-13 13:24:40

Hi all, here's sharing "hack" tips for constructing `Ecto.Query` from Raw SQL. You would want to do this in case you want all good feature of `Ecto.Query` but want to bypass safeguard put in place.

### A bit of introduction
`Ecto` is really cool database layer in Elixir, for SQL it let you define SQL query inside Elixir in readable and concise way, and execute it with automatic mapping to defined data structure.
![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/wdjjpehwzel2xcv7mq1p.png)
*example of SQL query and execution in Ecto* 

`Ecto.Query` is main module and data structure for defining a query in `Ecto`. `Ecto.Query` is composable and dynamically programmable, which means that we can extend a lot and reuse query functionality.

![Alt Text](https://dev-to-uploads.s3.amazonaws.com/i/ploerr642u4xtuo7wug6.png)
*we define with_search_keyword query filtering function and use it as part of bill_of_ladings function*

### Constructing Ecto from Raw Query

By default, `from`, usual construction method of `Ecto.Query` doesn't support complicated SQL. `from` support only `Ecto.Schema` data structure and table name in string, like:
``` elixir
from(t in "transporter",
  where: t.name == "STAR TRANSPORT COMPANY")
```

However, you can specify Raw SQL in join, via `fragment`, which usually used in case you have complicated join query.
```elixir
from(t in "transporter",
  join: i in fragment("SELECT * FROM invoice WHERE invoice.transporter_id = ?", t.id),
  where: i.date == ^date
)
```

We can leverage this Raw SQL join to get Raw SQL as base object of `Ecto.Query`.

#### Define dummy query

We need a dummy query, need to has exactly one result. as example we can query from "constants" table and limit it to 1
```elixir
dummy_query = from x in "truck_types", as: :dummy, select: x.ksuid, limit: 1
```

#### Join dummy query with raw query
By using `subquery`, we can reuse the dummy query to be used as join component, which we use then to join with raw data
```elixir
aggregate_subquery = 
  from(d in subquery(dummy_query))
    |> join(:inner, [], raw in fragment(@raw_sql), on: true, as: :raw)
    |> select([raw: raw], %{
      id: raw.id,
      name: raw.name
    })

```

Example of @raw_sql would be below. @raw_sql need to be constant string.
```SQL
SELECT id, name FROM transporter
UNION ALL
SELECT id, name FROM shipper
```

#### Wrap joined query in subquery (again)
By wrapping the joined query again in subquery, we will have a query which is just like Ecto.Query but with custom SQL
```elixir
query = from(q in subquery(aggregate_subquery))
``` 

#### Now you can use query as in usual Ecto way
Like in below, which search from transporter and shipper who has name "ANDI"
```elixir
from(q in query,
  where: name == "ANDI"
)
```

### Why would you want to hack it?
`Ecto` has really flexible and expressive way to write SQL, why then you would want to resort to such hacking as writing Raw SQL?

For me, the case come when i need to retrofit an existing SQL query, ~500 line - full with Postgres specific SQL feature, with existing functionality which is already built with `Ecto.Query`. Rewriting it in `Ecto` would need to decipher such 500 line of SQL, and would be full of `fragment` for safety hatch of writing Raw SQL. I reason that doing it all in Raw SQL would be easier to comprehend, which then led to this hack.