How to merge two queries into one?

Phoenix Forum Questions / Help
,
#1

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}%")
          )
#2

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.

#3

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
      )