Getting Started on Elixir and Ecto Part 5

Querying Data

Querying a database requires two steps in Ecto. First, we must construct the query, and then we must execute that query against the database by passing the query to the repository.

The Ecto.Repo.one function will only return a struct if there is one record in the result from the database and an error if there exists more than one record. Let’s start off with fetching just one record from our tasks table.

	Taskers.Tasks |> Ecto.Query.first |> Taskers.Repo.one
	///This will generate an Ecto.Query command below:
	#Ecto.Query<from t0 in Taskers.Tasks, order_by: [asc: t0.id], limit: 1>
	///We can construct this query with the same syntax as above like:
	require Ecto.Query Ecto.Query.from t in Taskers.Tasks, order_by: [asc: t.id], limit: 1
	///We had to use Ecto.Query to enable macros from that module.

view raw ecto_query.ex hosted with ❤ by GitHub

To fetch all records from the schema, Ecto provides the all function:

Taskers.Tasks |> Taskers.Repo.all

To fetch a record based on its ID, you use the get function:

Taskers.Tasks |> Taskers.Repo.get(1)
#=> %Taskers.Tasks{__meta__: #Ecto.Schema.Metadata<:loaded, "tasks">, id: 1, title: "Hello World", ...}

If we want to get a record based on something other than the id attribute, we can use get_by:

Taskers.Tasks |> Taskers.Repo.get_by(title: "Hello World")
#=> %Taskers.Tasks{__meta__: #Ecto.Schema.Metadata<:loaded, "tasks">, id: 1, title: "Hello World", ...}

If we want to get multiple records matching a specific attribute, we can use where:

Taskers.Tasks |> Ecto.Query.where(title: "Hello World") |> Taskers.Repo.all

NOTE: One important thing to note with Ecto query syntaxes is that they require variables to be pinned, using the pin operator (^). The pin operator instructs the query builder to use parameterized SQL queries to protect against SQL injection.

title = "Hello World"
Taskers.Tasks |> Ecto.Query.where(title: ^title) |> Taskers.Repo.all

Ecto queries can be built up by calling Ecto.Query functions on existing queries.

	query = Taskers.Tasks |> Ecto.Query.where(title: "Hello World")
	query = query |> Ecto.Query.where(user_id: 1)
	///Now we have two where clauses in the query:
	#Ecto.Query<from t in Taskers.Tasks, where: t.title == "Hello World", where: t.user_id == 1>

view raw ecto_where_query.ex hosted with ❤ by GitHub