Repo.get(User, id) #nil or %User{}
Repo.get!(User, id) #error or %User
MyRepo.get_by(Post, title: "My post") #error if not one result
Repo.all(User)
Repo.all(from u in User, select: u.email, where: u.age == ^age) #[...]
Repo.one(from u in User, where: ilike(u.email, "%1%"), select: count(u.id)) # of users with 1 in their email
Repo.one(from ea in EventAnalytics, where: ea.email == "j@g" and ea.type == "RSVP") #will error if > one response, nil if none
Supports literal Integers, Floats, Bools, Binary(<<1, 2 >>), Strings, and arrays, other external variables must be pinned^. Using with Schema(User instead of "users") lets it know what type to cast to and automatically retrieves all fields
query = from u in User, where: u.age == ^age and u.height > ^(height_ft * 3.28), order_by: u.city, preload: [:company]
#Can explicitly cast type
query = from u in "users", where: u.age > type(^age, :integer), select: u.name
#List of map from id to email
query = from u in User, select: %{u.id => u.email}, where: not is_nil(u.birthday)
#Complex child query stuff idk
child_query = from c in Comment, where: parent_as(:posts).id == c.post_id
query = from p in Post, as: :posts, join: c in subquery(child_query)
Repo.all(query)
Repo.one(query) #will error if >1 response
import Ecto.Query
uid = "34547f91-ffaa-4f03-8a0f-e63daaeced0b"
ReactPhoenix.Repo.all(from m in ReactPhoenix.Events.Meeting, where: m.user1_id == ^uid) # use pin to use external var
Join
:inner_join, :left_join, :right_join, :cross_join, :full_join, :inner_lateral_join or :left_lateral_join. :join is equivalent to :inner_join(match both returned)
counts_and_events =
from ea in subquery(count_types),
join: e in Event,
on: ea.event_id == e.id,
where: e.user_id == ^user_id,
order_by: [desc: e.start_time],
# order_by: e.start_time would be asc
select: %{
id: ea.event_id,
title: e.title,
start_time: e.start_time,
}
Repo.all(
from q in Question,
where: q.subevent_id == ^subevent_id,
preload: [:user],
left_join: qu in QuestionUpvote,
on: qu.question_id == q.id,
group_by: [q.id],
select: %{question: q, upvotes: count(qu.id)}
)
Preload
Can be used very similarly to a join, and keeps proper select and structure
def get_event!(id) do
Repo.get!(Event, id)
|> Repo.preload(:subevents)
end
Repo.all(from q in Question, where: q.subevent_id == ^subevent_id, preload: [user])
# seperating ra_query first gets all the reqs then fills in the association(if joined with where it wouldn't return reqs without ra with the right email)
ra_query = from ra in RegistrationAnswer, where: ra.email == ^email
Repo.all(from req in RegistrationEventQuestion,
where: req.event_id == ^event_id,
preload: [registration_answers: ^ra_query],
select: req)
