Dynamically adding groups of conditions in Ecto

Phoenix Forum Questions / Help
,
1

I have a patient who has first_name and last_name and a medical_record which belongs to patient schemas patient and record

I have the following (simplified) schemas and fields

patient:
  last_name
  first_name
  
medical_record:
  patient_id
  code

All the fields are strings

In the index page there is a single input field for the search criteria where the user types fragments of text.

Having the following data

                 code
LEWIS HAMILTON   44
MAX VERSTAPPEN   33
SEBASTIAN VETTEL 5
NICO HULKENBERG  27
DANIEL RICCIARDO 3
DANIIL KYVAT     26

I have the following results

Search criteria: H
LEWIS HAMILTON   44
NICO HULKENBERG  27

Search criteria: VE
MAX VERSTAPPEN   33
SEBASTIAN VETTEL 5

Search criteria: VE 3
MAX VERSTAPPEN   33

Search criteria: VE MA
MAX VERSTAPPEN 33

Search criteria: VE MA 456
(EMPTY)

Search criteria: 3
MAX VERSTAPPEN   33
DANIEL RICCIARDO 3

The final SQL statement should look like this

SELECT * FROM patients as p
	INNER JOIN medical_records as mr ON mr.patient_id = p.id
	WHERE (p.last_name = 'VE' OR p.first_name = 'VE' or mr.code = 'VE')
	AND  (p.last_name = 'MA' OR p.first_name = 'MA' or mr.code = 'MA')
        AND ...

The idea is to chain condition groups with AND for each of the search params.

Here is what I have done in elixir

def search_patients(search_params) do
  q =
    from p in Patient,
      join: r in MedicalRecord,
      on: p.id == r.patient_id,
      order_by: [p.last_name, p.first_name]

  params =
    search_params
    |> String.split()

  q =
    params
    |> Enum.reduce(q, &filter/2)

  IO.inspect(Repo.to_sql(:all, q))
end

def filter(param, query) do
  from p in query,
    where: p.last_name == ^param,
    or_where: p.first_name == ^param
end

However, using “VE MA” as search_params the resulting SQL is as follows:

 {"SELECT p0.id, p0.first_name, p0.last_name 
FROM patient AS p0 INNER JOIN medical_records AS m1 ON p.id = m1.patient_id 
WHERE ((((p0.last_name = $1)) OR (p0.first_name = $2)) AND (p0.last_name = $3)) OR (p0.first_name = $4)",
["VE", "VE", "MA", "MA"]}

Which is not what I expected.

What am I doing wrong?

How can I add a test against medical_record in the filter function? Until now I have only used patient.

Currently I am using a normal comparison (==). How do I introduce LIKE?

2

You might wish to redact all of the patient names in this snippet, or explicitly mention that they were made up on the spot for this example, if that’s the case. Depending on what country/jurisdiction you operate out of, sharing even this much may violate regulations in your industry (i.e. HIPAA).

1 Like
3

I believe they’re Formula One racers, so all is well

1 Like
4

Have you tried Ecto.Query.API — Ecto v3.11.1 ?

1 Like
5

@shanesveller, @david_ex

Yes, they are Formula One drivers. This year’s season starts in a few hours. F1 is the only sport that I actively follow.

I used those names because the made up names that I use normally are in spanish.

Thanks for your comments!

1 Like
6

Not yet, I am currently stuck in traversing the search terms and generating the query with the right combination of ORs and ANDs

7

Ecto.Query also provides dynamic/2 to build queries; see:

https://hexdocs.pm/ecto/Ecto.Query.html#dynamic/2

Hope this helps.

8

Your code is equivalent to:

q = 
  from p in Patient,
    join: r in MedicalRecord,
    on: p.id == r.patient_id,
    order_by: [p.last_name, p.first_name],
    where: p.last_name == ^"VE",
    or_where: p.first_name == ^"VE",
    where: p.last_name == ^"MA",
    or_where: p.first_name == ^"MA"

Which is not what you want.

What you want is changing the filter function to something like this:

def filter(param, query) do
  from p in query,
    where: p.last_name == ^param or p.first_name == ^param
end

I do not like or_where very much for this exact reason. where has AND associativity, and if you need to build more complex expressions and use OR just write the expression explicitely.

3 Likes
9

How can I add a test against medical_record in the filter function? Until now I have only used patient.

You need to bind the second table in your filter function, then you can use it in your where expression.

def filter(param, query) do
  from [p, r] in query,
    where: p.last_name == ^param or p.first_name == ^param or r.code == ^param
end
3 Likes
10

Great! It works perfect!