Hello, can you tell me how can i merge that two queries in one ?
ids = from(relationship in UserProject,
where: relationship.user_id == ^current_user.id,
join: p in Project, where: relationship.project_id == p.id,
select: p.id
)
|> Repo.all
query = from(p in Project,
where: (p.id in ^ids),
where: ilike(p.name, ^"%#{substring}%")
)
Can add one more join maybe?
Project
|> where([p], ilike(p.name, ^"%#{substring}%"))
|> join(:inner, [p], up in UserProject, p.id == up.project_id)
|> where([p, up], up.user_id == ^current_user.id)
|> select([p, up], p)
|> Repo.all()
Or just remove Repo.all()
to get a query.
Here is variant where you get query in the end still
user_projects_query =
from(
relationship in UserProject,
join: p in Project,
on: relationship.project_id == p.id,
where: relationship.user_id == ^current_user.id,
where: ilike(p.name, ^"%#{substring}%"),
select: p
)