How to write a two queries for a single search term?

So I have a query where the term is searching the first query and based on another term it’s giving result from different database table.

So I have a raw query like this

SELECT * FROM z.users where users.code ilike '%87%' or ilike '%87%'  
or IN

Inner Join groups on = groups.course_id
Inner Join groups_sections on = groups_sections.group_id
Inner Join sections on groups_sections.section_id =
Inner Join sessions on sessions.section_id =
Inner Join sessions_instructors on sessions_instructors.session_id =
Inner Join instructors on = sessions_instructors.instructor_id
where ilike '%87%' or instructors.code ilike '%87%')

So I’m having a hard time converting this to ecto query. Can someone tell me how can I convert this to ecto query?

What have you tried?

Starting point on how to build a query:

def search(term) when is_binary(term) do
  search_term = "%#{term}%"

  |> where([u], ilike(u.code, ^search_term)
  |> or_where([u], ilike(, ^search_term)

And then pipe your joins and preloads after.


This much I have done. But I’m asking about the next part. Because you see the IN condition for the other query. That has the mostly joins and preload. How do I tackle that?

Have you checked out Ecto.Query.subquery/2?

I was thinking of dynamic query. But I’m not sure about if it can help

Do you think subquery can help ? Because I don’t want first query to be a part of second query. I don’t know if I’m makin sense

Why is that exactly?

Also yes, Ecto has the dynamic construct. Do a search here on ElixirForum – probably for something like “ecto dynamic” – and you’ll find several posts with people using it. It’s not that hard or intimidating, don’t worry too much. :slight_smile: