Is it possible to execute a self join in Sequel?

Question

I'm working in a publications management application. By the business logic, a publication can belongs to another publication if its texts have a similarity level equal or higher than 75%. So, in the database, I created a self relation on the publications table.

So far, I have the following query that works perfectly:

SELECT 
    publications.id, publications.body, 
    similar_publications.id similar_id, similar_publications.body similar_body
FROM publications
LEFT JOIN publications similar_publications
  ON similar_publications.id = publications.publication_id
WHERE publications.id = <an-id-here>;

The problem is that I want to "convert" this query to Sequel's DSL, in order to simply call my model in a way like so: Publication.including_similar_publications.where('publications.id = ?', 1)

I know I need to create this method into my Model. My doubt is: how? Is there something similar to ActiveRecord named scopes in Sequel? Another problem is that because my query is a SELF JOIN, I need to use aliasing on LEFT JOIN... Does Sequel permit that I use aliases the way I exposed in my query? If yes, how?

Answer 1

You just need to alias the table during the join:

class Publication < Sequel::Model
  dataset_module do
    def including_similar_publications
      select{[publications[:id], publications[:body], similar_publication[:id].as(:similar_publication_id), similar_publication[:body].as(:similar_body)]}.
        left_join(Sequel[:publications].as(:similar_publication), :id=>:publication_id)
    end
  end
end