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

Questions / Help
,
1

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 users.name ilike '%87%'  
or courses.id IN

(Select users.id
From users.courses 
Inner Join groups on courses.id = groups.course_id
Inner Join groups_sections on groups.id = groups_sections.group_id
Inner Join sections on groups_sections.section_id = sections.id
Inner Join sessions on sessions.section_id = sections.id
Inner Join sessions_instructors on sessions_instructors.session_id = sessions.id
Inner Join instructors on instructors.id = sessions_instructors.instructor_id
where instructors.name 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?

2

What have you tried?

Starting point on how to build a query:

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

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

And then pipe your joins and preloads after.

2 Likes
3

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?

4

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

5

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

6

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

7

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: