Query with variable number of joins

[jolmg]

For example, let's say that I have a list representing a category path, like

Tool > Screwdriver > Phillips

and I want to find the corresponding record in a table "category" with columns "parent_id" and "name".

In Ruby with ActiveRecord, I could do something like:

category_path = ["Tool", "Screwdriver", "Phillips"]
leaf_cat_name, *parent_cat_names = category_path.reverse
query_init = Category.where(name: leaf_cat_name)
category = parent_cat_names.to_enum.with_index.reduce(query_init) do |query, (name, i)|
    query.joins(<<~SQL)
      join categories cat#{i + 1}
        on #{i.zero? ? "categories" : "cat#{i}"}.parent_id = cat#{i + 1}.id
    SQL
    .where("cat#{i + 1}.name = :name", name: name)
  end
  .first!

That results in a query like:

select  `categories`.*
  from `categories`
  join categories cat1
    on categories.parent_id = cat1.id
  join categories cat2
    on cat1.parent_id = cat2.id
  where `categories`.`name` = 'Phillips'
    and (cat1.name = 'Screwdriver')
    and (cat2.name = 'Tool')
  order by `categories`.`id` asc
  limit 1

Could something like that be done with Esqueleto? preferably without having to manually handle the aliasing. Right now, it seems Esqueleto's joins are limited to a static set based on the parameter of the function passed to from.

I was thinking of perhaps having functions like:

leftJoinOn
  :: PersistEntity a
  => (SqlExpr (Entity a) -> SqlExpr (Value Bool))
  -> SqlQuery (SqlExpr (Entity a))

to be able to write the equivalent to that Ruby code, which could be like

let catNames = ["Tool", "Screwdriver", "Phillips"]
    leafCatName : parentCatNames = reverse catNames

select $ from $ \ leafCat -> do
  where_ $ leafCat ^. CategoryName =. val leafCatName
  -- Please excuse the free-point
  rootCat <- ((.).(.)) flip flip foldlM leafCat parentCatNames $ \ subCat name -> do
    supCat <- joinOn $ \ supCat ->
      subCat ^. CategoryParentId =. just (supCat ^. CategoryId) 
    where_ $ supCat ^. CategoryName =. val name
    return supCat
  where_ $ rootCat ^. CategoryParentId =. nothing  
  return leafCat

I haven't read the source in detail, but I see that there's State IdentState in SqlQuery. If no aliasing is done automatically yet, perhaps the aliases could be kept track-of in there.

I could perhaps work on this next week, but I was wondering if there would be any complications this would imply or anything that I may have overlooked that someone here could advise me on.

[belevy]

The work on #172 should address this. We started with a similar idea for the syntax but ran in to issues with mixing the old style of introducing tables into the query and these standalone joins. Please take a look at that PR and feel free to leave comments on anything that you feel is missing over there. Currently it is being held up by bad type errors but hopefully we can land it soon™️

[jolmg]

That's really exciting, especially the fact that it also seems to work towards being able to build subqueries in the joins, and unions! I'm practically salivating now. I'm going to look over it over the next few days.

[belevy]

@jolmg Are you able to accomplish what you want using the new syntax or is this still an issue in your opinion?