un peu de code Ruby et de Sequel

article_code.rb

require 'sequel'
require 'byebug'

DB = Sequel.connect(adapter: 'postgres', host: '127.0.0.1',  user: 'postgres', password: '', database: 'sequel_test')

if DB.table_exists?(:chapters)
  DB[:chapters].truncate
else
  DB.create_table :chapters do
    primary_key :id
    String :name
    String :text
    String :published
    Integer :book_id
  end
end

if DB.table_exists?(:books)
  DB[:books].truncate
else
  DB.create_table :books do
    primary_key :id
    String :name
    String :intro
    String :published
  end
end

puts "Books count : #{DB[:books].count}"

books = DB[:books]

books.insert(name: 'Babc', intro: "Lorem Ipsum is", published: "yes")
books.insert(name: 'Bklm', intro: "Various versions", published: "no")
puts "Books count : #{DB[:books].count}"


published_book_1 = books.where(name: 'Babc').first
unpublished_book_1 = books.where(name: 'Bklm').first

chapters = DB[:chapters]
count = 1
chapters.insert(name: "Chapter #{count}", book_id: published_book_1[:id], published: 'yes' , text: 'All the Lorem Ipsum...')
count +=1
chapters.insert(name: "Chapter #{count}", book_id: published_book_1[:id], published: 'no' , text: 'All the Lorem Ipsum...')
count +=1
chapters.insert(name: "Chapter #{count}", book_id: published_book_1[:id], published: 'yes' , text: 'All the Lorem Ipsum...')
count +=1
chapters.insert(name: "Chapter #{count}", book_id: published_book_1[:id], published: 'no' , text: 'All the Lorem Ipsum...')

count = 1
chapters.insert(name: "Chapter #{count}", book_id: unpublished_book_1[:id], published: 'yes' , text: 'All the Lorem Ipsum...')
count +=1
chapters.insert(name: "Chapter #{count}", book_id: unpublished_book_1[:id], published: 'no' , text: 'All the Lorem Ipsum...')
count +=1
chapters.insert(name: "Chapter #{count}", book_id: unpublished_book_1[:id], published: 'yes' , text: 'All the Lorem Ipsum...')
count +=1
chapters.insert(name: "Chapter #{count}", book_id: unpublished_book_1[:id], published: 'no' , text: 'All the Lorem Ipsum...')

class Book < Sequel::Model(DB[:books])
  one_to_many :chapters

  dataset_module do
    def published
      where(published: 'yes')
    end

    def delete_published_ones
      published.delete
    end
  end
end

class Chapter < Sequel::Model(DB[:chapters])
  many_to_one :book
end

puts "Books count : #{Book.count}"
first_book = Book.where(name: 'Babc').first
same_book = Book[first_book.id]

book = Book.first
puts book.chapters
puts "This book has #{book.chapters.count} chapters"
puts "There is #{Book.published.count} published books"


published_chapters = Chapter.where(published: 'yes')
filtered_chapters = published_chapters.all.select { |c| c.book.published == 'yes' }
puts "We only have #{filtered_chapters.count} published chapters related to published books"


published_books = Book.where(published: 'yes')
published_chapters = published_books.all.collect { |t| t.chapters.select { |t| t.published == 'yes' } }.flatten
puts "We only have #{published_chapters.count} published chapters related to published books"

begin
  query = Chapter.where(published: "yes").join(Book.where(published: "yes"), id: :book_id)
  chapters = query.all
rescue Sequel::DatabaseError
  puts "this is a bad query !"
  puts query.sql
end

query = Chapter.from(Sequel[:chapters].as(:chapter)).where(Sequel[:chapter][:published] => 'yes').join(Book.from(Sequel[:books].as(:book)).where(Sequel[:book][:published] => 'yes'), id: :book_id)
chapters = query.all

if chapters.first.keys.include?(:intro)
  puts "this has bad data !"
end


query = Chapter.from(Sequel[:chapters].as(:chapter)).select(Sequel.lit('chapter.*')).where(Sequel[:chapter][:published] => 'yes').join(Book.from(Sequel[:books].as(:book)).where(Sequel[:book][:published] => 'yes'), id: :book_id)
chapters = query.all


byebug

puts "bob"

docker-compose.yml

version: "3.7"
services:
  sequel_postgres_db:
    image: postgres:11.6
    ports:
      - "5432:5432"
    volumes:
      - ./pgdata/:/var/lib/postgresql/data