Ecto where: c.company_name in ^companies is there any way to match every single thing in list?

Phoenix Forum Questions / Help
,
1

Hi.

Context:

I have this ecto query where: c.company_name in ^companies. I need the left side c.company_name to match every single item in the right side ^companies.

Problem:

The in only require the left side to match one item on the right side to be true. I need it to match all of them to be true.

Thank you for your time.

2

How is that going to work?

To be true all the list entries would have to be identical. If that is the case reduce it to that one single entry beforehand and go for a simple equality:

where: c.company_name == ^same_company
1 Like
3

The left side is a list too.

Thanks, I’m actually doing a reduce and composing query right now to solve this problem.

 135   def get_all_perfume_by_perfume_name_con_comp(query, name, concentration, companies) do
 136   query = get_all_perfume_by_perfume_name_con(query, name, concentration)
 137   Enum.reduce(companies,
 138               query,
 139               fn(company, query) -> match_all_company_for_dupe(query, company)
 140               end)
 147   end
 149   defp match_all_company_for_dupe(query, nil)
 150     query
 151   end
 152   defp match_all_company_for_dupe(query, [])
 153     query
 154   end
 155   defp match_all_company_for_dupe(query, company)
 156     from p in query,
 157       join: j in Fumigate.Fragrance.PerfumeCompanyJoin,
 158       where: p.id == j.perfume_id,
 159       join: c in Fumigate.Fragrance.Company,
 160       where: c.id == j.company_id,
 161       where: c.company_name in ^company
 162   end
 164   def get_all_perfume_by_perfume_name_con(query, name, concentration) do
 165     from p in query,
 166       join: j in Fumigate.Fragrance.PerfumeCompanyJoin, where: p.id == j.perfume_id,
 167       join: c in Fumigate.Fragrance.Company, where: c.id == j.company_id,
 168       where: [perfume_name: ^name],
 169       where: [concentration: ^concentration]
 170   end

It’s a work in progress but you gave me a good idea where to go.

Thanks!

4

I think I got something going.

Going add it here just in case any body is on the same boat as I am.

Thx guys!

 135   def get_all_perfume_by_perfume_name_con_comp(query, name, concentration, companies) do
 136     query = get_all_perfume_by_perfume_name_con(query, name, concentration)
 137     query = Enum.reduce(companies, query,
 138                         fn(company, query) ->
 139                           match_all_company_for_dupe(query, company)
 140                         end)
 141     from q in query,
 142       group_by: q.id
 143   end
 144
 145   defp match_all_company_for_dupe(query, nil) do
 146     query
 147   end
 148   defp match_all_company_for_dupe(query, []) do
 149     query
 150   end
 151   defp match_all_company_for_dupe(query, company) do
 152     from p in query,
 153       join: j in Fumigate.Fragrance.PerfumeCompanyJoin,
 154       where: p.id == j.perfume_id,
 155       join: c in Fumigate.Fragrance.Company,
 156       where: c.id == j.company_id,
 157       where: c.company_name in [^company]
 158   end
 159
 160   def get_all_perfume_by_perfume_name_con(query, name, concentration) do
 161     from p in query,
 162       join: j in Fumigate.Fragrance.PerfumeCompanyJoin, where: p.id == j.perfume_id,
 163       join: c in Fumigate.Fragrance.Company, where: c.id == j.company_id,
 164       where: [perfume_name: ^name],
 165       where: [concentration: ^concentration]
 166   end

The whole idea is I’ve broke up the query into two parts.

The second part is the list of company_names I have to match against every single item in the list. I made a loopy function for it.

The query return multiple entries so I did a group_by, yeah I know pretty hackish. I haven’t test this code throughly but it seems to work.

5

For note, I think the traditional way would be to build up a dynamic query with each list’s elements.

1 Like
6

There may be another option, provided postgres is the underlying database: Arrays

This may serve as a guide:

Given data:

# select name from company;
 name
------
 abc
 def
 ghi
(3 rows)

The query:

select array_length( array(select name from company
   intersect
  (select unnest(array['abc','def']))),1);

returns:

 array_length
--------------
            2

A query fragment might look like, using dates: (untested)

      where: fragment( "(select array_length(  array( select unnest(array[?]::date[])
      intersect select the_date from ?),1) = ?)",
              type(^dates, {:array, :date}),
              ve,
              ^length( dates )
      )

I’ve used this to good effect. Apologies if there are errant bits in the Ecto part, but it should be close.

4 Likes