CODEWITHSUNDEEP
sqlpostgresqlselectelixirecto
BeniaminoBaggins
BeniaminoBaggins

Reputation: 12483

Elixir PostgreSQL - select query returning results which do not conform to WHERE clause

I have this function which select shops and their products from my database:

def create_unique_shop_query_no_keyword(categories, shop_ids) do
    products_shops_categories = from p in Product,
    join: ps in ProductShop, on: p.id == ps.p_id,
    join: s in Shop, on: s.id == ps.s_id,
    join: pc in ProductCategory, on: p.id == pc.p_id,
    join: c in Subcategory, on: c.id == pc.c_id,
    distinct: s.id,
    where: c.id in ^categories,
    where: s.id in ^shop_ids,
    group_by: [s.id, s.name],
    select: %{products: fragment(
      "json_agg( DISTINCT (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) AS products",
      p.id,
      p.name,
      p.brand,
      p.description,
      p.image,
      p.rating,
      p.number_of_votes,
      ps.not_in_shop_count,
      ps.is_in_shop_count,
      ps.price,
      p.not_vegan_count,
      p.vegan_count),
      shop: fragment(
        "json_agg( DISTINCT (?, ?, ST_X(?), ST_Y(?), ?, ?, ?, ?, ?)) AS shop",
        s.id,
        s.name,
        s.point,
        s.point,
        s.place_id,
        s.street,
        s.suburb,
        s.city,
        s.street_number
        )
      }
  end

I have another function that does the same thing, but with a keyword thrown into the mix, which checks for a keyword (not necessarily a full word) being in the product name, or the product brand:

def create_unique_shop_query(keyword, categories, shop_ids) do
    products_shops_categories = from p in Product,
    join: ps in ProductShop, on: p.id == ps.p_id,
    join: s in Shop, on: s.id == ps.s_id,
    join: pc in ProductCategory, on: p.id == pc.p_id,
    join: c in Subcategory, on: c.id == pc.c_id,
    distinct: s.id,
    where: c.id in ^categories,
    where: s.id in ^shop_ids,
    where: like(p.name, ^("%#{keyword}%")),
    or_where: like(p.brand, ^("%#{keyword}%")),
    group_by: [s.id, s.name],
    select: %{products: fragment(
      "json_agg( DISTINCT (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)) AS products",
      p.id,
      p.name,
      p.brand,
      p.description,
      p.image,
      p.rating,
      p.number_of_votes,
      ps.not_in_shop_count,
      ps.is_in_shop_count,
      ps.price,
      p.not_vegan_count,
      p.vegan_count),
      shop: fragment(
        "json_agg( DISTINCT (?, ?, ST_X(?), ST_Y(?), ?, ?, ?, ?, ?)) AS shop",
        s.id,
        s.name,
        s.point,
        s.point,
        s.place_id,
        s.street,
        s.suburb,
        s.city,
        s.street_number
        )
      }
  end

The top function, with no keyword, works as intended. But in the function which has a keyword, the results contain shops where the shop id (s.id) is not in shop_ids. Why is this?

Upvotes: 0

Views: 127

Answers (1)

Kalvin Hom
Kalvin Hom

Reputation: 301

You have an or_where, so your query condition is essentially....

(c.id in categories) and (s.id in shop_ids) and (p.name like keyword) or (p.brand like keyword)

As long as p.brand is like keyword, it will match.

This is probably what you're looking for, grouping the OR condition within the AND where for the keyword matching.

...
where: c.id in ^categories,
    where: s.id in ^shop_ids,
    where: like(p.name, ^("%#{keyword}%")) or like(p.brand, ^("%#{keyword}%")),
...

This translates it to

(c.id in categories) and (s.id in shop_ids) and (p.name like keyword or p.brand like keyword)

Upvotes: 2

Related Questions