Rails find_by_sql datetime attribute return nil value

Question

When I execute find_by_sql query, datetime selection return 'nil' as value:

ruby: 3.0.0p0 rails: 6.1.3.1 postgresql: 10

schema:

  create_table "availabilities", force: :cascade do |t|
    t.bigint "timeslot_id", null: false
    t.bigint "option_id", null: false
    t.integer "qty"
    t.integer "stock"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["option_id"], name: "index_availabilities_on_option_id"
    t.index ["timeslot_id"], name: "index_availabilities_on_timeslot_id"
  end

  create_table "locales", force: :cascade do |t|
    t.string "code", null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

  create_table "option_translations", force: :cascade do |t|
    t.bigint "option_id", null: false
    t.bigint "locale_id", null: false
    t.string "name"
    t.string "description"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["locale_id"], name: "index_option_translations_on_locale_id"
    t.index ["option_id"], name: "index_option_translations_on_option_id"
  end

  create_table "options", force: :cascade do |t|
    t.bigint "variation_id", null: false
    t.bigint "vat_id", null: false
    t.string "neptis_key", null: false
    t.string "external_id"
    t.decimal "price", precision: 10, scale: 2, null: false
    t.decimal "external_price", precision: 10, scale: 2, null: false
    t.integer "min_sell_qty"
    t.integer "max_sell_qty"
    t.integer "capacity"
    t.datetime "sales_begin_at"
    t.datetime "sales_end_at"
    t.string "duration_type"
    t.string "duration_value"
    t.integer "booking_notice_period"
    t.integer "sort_index"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["neptis_key"], name: "unique_neptis_key", unique: true
    t.index ["variation_id"], name: "index_options_on_variation_id"
    t.index ["vat_id"], name: "index_options_on_vat_id"
  end

  create_table "products", force: :cascade do |t|
    t.bigint "user_ticket_office_id", null: false
    t.string "external_id", null: false
    t.string "external_ref"
    t.string "external_supplier_id", default: ""
    t.float "rating_summary", default: 0.0
    t.integer "reviews_count", default: 0
    t.jsonb "appointment_types", default: []
    t.boolean "need_appointment", default: false
    t.string "duration_type", default: "hour"
    t.jsonb "duration_values", default: []
    t.integer "booking_notice_period", default: 0
    t.jsonb "ticket_opening_times", default: []
    t.jsonb "extra"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["user_ticket_office_id"], name: "index_products_on_user_ticket_office_id"
  end

  create_table "timeslots", force: :cascade do |t|
    t.bigint "variation_id", null: false
    t.datetime "begin_at"
    t.boolean "available"
    t.integer "qty_available"
    t.integer "capacity"
    t.datetime "end_booking_at"
    t.integer "hours_before_booking"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["variation_id"], name: "index_timeslots_on_variation_id"
  end

  create_table "variation_translations", force: :cascade do |t|
    t.bigint "variation_id", null: false
    t.bigint "locale_id", null: false
    t.string "name"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["locale_id"], name: "index_variation_translations_on_locale_id"
    t.index ["variation_id"], name: "index_variation_translations_on_variation_id"
  end

  create_table "variations", force: :cascade do |t|
    t.bigint "product_id", null: false
    t.string "external_id"
    t.date "first_date"
    t.date "last_date"
    t.jsonb "available_dates"
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
    t.index ["product_id"], name: "index_variations_on_product_id"
  end

  create_table "vats", force: :cascade do |t|
    t.float "value", null: false
    t.datetime "created_at", precision: 6, null: false
    t.datetime "updated_at", precision: 6, null: false
  end

query:

module Catalog
  class AllBookingsAvailableQuery

    def self.call( user: , product:, locale: nil, begin_at: nil, end_at: nil)
      query = Catalog::AllBookingsAvailableQuery.new(  user: user, product: product, locale: locale, begin_at: begin_at, end_at: end_at )
      query.exec_sql
    end

    def initialize( user: , product: ,  locale: , begin_at: Time.now, end_at: Time.now + 3.months)
      @locale_id   = locale.id
      user.switch_tenant!
      @user_id     = user.id
      @product_id  = product.id
      @begin_at    = begin_at
      @end_at      = end_at
    end

    def exec_sql
      Availability.find_by_sql [Arel.sql(build_sql), { begin_at: @begin_at, end_at: @end_at} ]
    end

    def build_sql
      <<~SQL
        SELECT 
               (vs.id || '') as lvl0,
               (vs.id || '.' || os.id) as lvl1,
               tims.begin_at as option_event_dt,
               to_char(tims.begin_at, 'HH24' || ':' || 'MI' ) as event_hour,
               to_char(tims.begin_at, 'DD/MM/YYYY' ) as event_date,
               vs.id as variation_id, coalesce(locale_variation_trans.name, first_variation_trans.name) as variation_name,
               os.id as option_id, coalesce(locale_option_trans.name, first_option_trans.name) as option_name,
               os.price, vats.value as vat, os.min_sell_qty, os.max_sell_qty, os.sort_index,
               avs.qty, avs.stock
        FROM "user_#{@user_id}".availabilities avs
            LEFT JOIN "user_#{@user_id}".timeslots tims on tims.id = avs.timeslot_id AND tims.begin_at > :begin_at AND tims.begin_at < :end_at
            LEFT JOIN "user_#{@user_id}".options     os on os.id   = avs.option_id
            LEFT JOIN "public".vats           on os.id   = avs.option_id
            LEFT JOIN "user_#{@user_id}".variations  vs on vs.id   = os.variation_id
            LEFT JOIN LATERAL (
                SELECT vts1.name
                FROM "user_#{@user_id}".variation_translations vts1
                WHERE vts1.variation_id = vs.id AND vts1.locale_id = #{@locale_id}
                LIMIT 1
            ) locale_variation_trans on TRUE
             LEFT JOIN LATERAL (
                 SELECT vts2.name
                 FROM "user_#{@user_id}".variation_translations vts2
                 WHERE vts2.variation_id = vs.id
                 LIMIT 1
                 ) first_variation_trans on TRUE
            LEFT JOIN LATERAL (
                SELECT ots1.name
                FROM "user_#{@user_id}".option_translations ots1
                WHERE ots1.option_id = os.id AND ots1.locale_id = #{@locale_id}
                LIMIT 1
            ) as locale_option_trans on TRUE
             LEFT JOIN LATERAL (
                    SELECT ots2.name
                    FROM "user_#{@user_id}".option_translations ots2
                    WHERE ots2.option_id = os.id
                    LIMIT 1
                 ) as first_option_trans on TRUE
        WHERE  vs.product_id = #{@product_id}
      SQL
    end


  end
end

run:

u = User.find(1)
p = Product.find(1)
l = locale.find(3)
::Catalog::AllBookingsAvailableQuery.call(  user: u, product: p, locale: l).first.attributes
# => {"lvl0"=>"1", "lvl1"=>"1.1", "option_event_dt"=>nil, "event_hour"=>nil, "event_date"=>nil, "variation_id"=>1, "variation_name"=>"Normal", "option_id"=>1, "option_name"=>"Adulte", "price"=>0.2e2, "vat"=>5.0, "min_sell_qty"=>0, "max_sell_qty"=>0, "sort_index"=>0, "qty"=>350, "stock"=>350, "id"=>nil}

As you can see, all attributes related to event datetime are nulls. When i run query in psql console, i have these datetime:

Does anyone knows what append with find_by_sql and attributes for missing event's datetime attributes ?

EDIT 1: @engineersmnky ask for the query ( without Arel.sql ). I don't know if it's not a tenant's side effet ...

irb(main):011:0> a = Catalog::AllBooking.call( user: u, product: Product.find(1), locale: Locale.find(3))
  [api_billetteries_development] [user_1]   Product Load (0.4ms)  SELECT "products".* FROM "products" WHERE "products"."id" = $1 LIMIT $2  [["id", 1], ["LIMIT", 1]]
  [api_billetteries_development] [user_1]   Locale Load (0.3ms)  SELECT "public"."locales".* FROM "public"."locales" WHERE "public"."locales"."id" = $1 LIMIT $2  [["id", 3], ["LIMIT", 1]]
  [api_billetteries_development] [user_1]   Availability Load (4.2ms)  SELECT 
       (vs.id || '') as lvl0,
       (vs.id || '.' || os.id) as lvl1,
       tims.begin_at as option_event_dt,
       to_char(tims.begin_at, 'HH24' || ':' || 'MI' ) as event_hour,
       to_char(tims.begin_at, 'DD/MM/YYYY' ) as event_date,
       vs.id as variation_id, coalesce(locale_variation_trans.name, first_variation_trans.name) as variation_name,
       os.id as option_id, coalesce(locale_option_trans.name, first_option_trans.name) as option_name,
       os.price, vats.value as vat, os.min_sell_qty, os.max_sell_qty, os.sort_index,
       avs.qty, avs.stock
FROM "user_1".availabilities avs
    LEFT JOIN "user_1".timeslots tims on tims.id = avs.timeslot_id AND tims.begin_at > NULL AND tims.begin_at < NULL
    LEFT JOIN "user_1".options     os on os.id   = avs.option_id
    LEFT JOIN "public".vats           on os.id   = avs.option_id
    LEFT JOIN "user_1".variations  vs on vs.id   = os.variation_id
    LEFT JOIN LATERAL (
        SELECT vts1.name
        FROM "user_1".variation_translations vts1
        WHERE vts1.variation_id = vs.id AND vts1.locale_id = 3
        LIMIT 1
    ) locale_variation_trans on TRUE
     LEFT JOIN LATERAL (
         SELECT vts2.name
         FROM "user_1".variation_translations vts2
         WHERE vts2.variation_id = vs.id
         LIMIT 1
         ) first_variation_trans on TRUE
    LEFT JOIN LATERAL (
        SELECT ots1.name
        FROM "user_1".option_translations ots1
        WHERE ots1.option_id = os.id AND ots1.locale_id = 3
        LIMIT 1
    ) as locale_option_trans on TRUE
     LEFT JOIN LATERAL (
            SELECT ots2.name
            FROM "user_1".option_translations ots2
            WHERE ots2.option_id = os.id
            LIMIT 1
         ) as first_option_trans on TRUE
WHERE  vs.product_id = 1

=> [#<Availability id: nil, option_id: 1, qty: 350, stock: 350>, #<Availability id: nil, option_id: 1, qty: 350, stock: 350>, #<Availability id: nil, option_id: 1, qty: 350, stock: 350>, #<A...
irb(main):012:0> a[0].option_event_dt
=> nil

Edit 2:

I got a running result by executing 'traditional' query, lateral_joins inside joins:

module Catalog
  class AllBookingsAvailableQuery

    SELECT_SQL = <<~SQL
            "availabilities".id,
            "availabilities".timeslot_id,
            "availabilities".option_id,
            "variations".id as variation_id,
            "timeslots".begin_at,
            coalesce(locale_variation_trans.name, first_variation_trans.name) as variation_name,
            "options".id as option_id,
            coalesce(locale_option_trans.name, first_option_trans.name) as option_name,
            "options".price,
            "vats".value as option_vat,
            "options".min_sell_qty,
            "options".max_sell_qty,
            "options".sort_index,
            "availabilities".qty,
            "availabilities".stock
    SQL

    def self.call( user: , product:, locale: nil, begin_at: nil, end_at: nil)
      query = Catalog::AllBookingsAvailableQuery.new(  user: user, product: product, locale: locale, begin_at: begin_at, end_at: end_at )
      query.fetch_availabilities
    end

    def initialize( user: , product: ,  locale: , begin_at: Time.now, end_at: Time.now + 2.weeks)
      @locale_id   = locale.id
      user.switch_tenant!
      @user_id     = user.id
      @product_id  = product.id
      @begin_at    = begin_at
      @end_at      = end_at
    end

    def fetch_availabilities
      Availability.joins( :timeslot, option: [ :vat, :option_translations, variation: [:product, :variation_translations]]).
        joins( lat_join_names).
        select( SELECT_SQL ).
        where( options: { variations: { products: { id: @product_id } } }).
        where('"timeslots".begin_at BETWEEN ? AND ?', @begin_at, @end_at).
        order('variation_id, sort_index, begin_at')    end

    def lat_join_names
      <<~SQL
            LEFT JOIN LATERAL (
                SELECT vts1.name
                FROM "user_#{@user_id}".variation_translations vts1
                WHERE vts1.variation_id = "variations".id AND vts1.locale_id = #{@locale_id}
                LIMIT 1
            ) locale_variation_trans on TRUE
             LEFT JOIN LATERAL (
                 SELECT vts2.name
                 FROM "user_#{@user_id}".variation_translations vts2
                 WHERE vts2.variation_id = "variations".id
                 LIMIT 1
                 ) first_variation_trans on TRUE
            LEFT JOIN LATERAL (
                SELECT ots1.name
                FROM "user_#{@user_id}".option_translations ots1
                WHERE ots1.option_id = "options".id AND ots1.locale_id = #{@locale_id}
                LIMIT 1
            ) as locale_option_trans on TRUE
             LEFT JOIN LATERAL (
                    SELECT ots2.name
                    FROM "user_#{@user_id}".option_translations ots2
                    WHERE ots2.option_id = "options".id
                    LIMIT 1
                 ) as first_option_trans on TRUE
      SQL
    end

  end
end

Answer 1

Okay I see your issue. begin_at and end_at are nil. Just because you set default values does not mean these cannot be overridden by nil. For Example:

def foo(bar:'baz') 
  bar.inspect
end 
foo
#=> "/"baz/"" 
foo(bar:nil)
#=> "nil" 

Your issue comes in with self.call which sets begins_at and ends_at to default to nil and then passes these values through.

If you want to use this functionality I would change initialize to:

 def initialize( user: , product: ,  locale: , begin_at: nil, end_at: nil)
      @locale_id   = locale.id
      user.switch_tenant!
      @user_id     = user.id
      @product_id  = product.id
      @begin_at    = begin_at || Time.now
      @end_at      = end_at || @begin_at + 3.months
    end

This way if begin_at is nil it will use Time.now and if end_at is nil then it will use @begin_at + 3.months