Wrong Arel expressions for semi-joins

[leoarnold]

Given the SQL statement

SELECT * FROM ingredients WHERE EXISTS (
  SELECT "1" AS one FROM dishes
  WHERE dishes.taste = 'umami' AND dishes.ingredient_id = ingredients.id
);

scuttle suggests

Ingredient.select(Arel.star).where(
  Dish.select(:1.as('one')).where(
    Dish.arel_table[:taste].eq('umami').and(
      Dish.arel_table[:ingredient_id].eq(Ingredient.arel_table[:id])
    )
  ).exists
)

which is incorrect, as .exists is defined only on a SelectManager, so we need #project:

Ingredient.select(Arel.star).where(
  Dish.arel_table.project(:1.as('one')).where( # <---- 
    Dish.arel_table[:taste].eq('umami').and(
      Dish.arel_table[:ingredient_id].eq(Ingredient.arel_table[:id])
    )
  ).exists
)

Also, for the negation

SELECT * FROM ingredients WHERE NOT ( EXISTS (
  SELECT "1" AS one FROM dishes
  WHERE dishes.taste = 'umami' AND dishes.ingredient_id = ingredients.id
));

scuttle seems to ignore the the negation:

Ingredient.select(Arel.star).where(
  Dish.select(:1.as('one')).where(
    Dish.arel_table[:taste].eq('umami').and(
      Dish.arel_table[:ingredient_id].eq(Ingredient.arel_table[:id])
    )
  ).exists
)

We would need:

Ingredient.select(Arel.star).where.not( # <----
  Dish.arel_table.project(:1.as('one')).where( # <---- 
    Dish.arel_table[:taste].eq('umami').and(
      Dish.arel_table[:ingredient_id].eq(Ingredient.arel_table[:id])
    )
  ).exists
)

[camertron]

Thanks for the bug report @leoarnold. Do you know if project is always allowed in place of select?

Also, would this be something you'd be willing to dive in and fix?