Problem with Account verification with multiple configuration

[jaahoo]

Gemfile

• ruby "3.1.2"
• gem "rails", "~> 7.0.3"
• gem 'rodauth-rails', '~> 1.4', '>= 1.4.2'

I have set RodauthAdmin

class RodauthApp < Rodauth::Rails::App
  # ...
  configure RodauthAdmin, :admin

  route do |r|
    # ...
    r.rodauth(:admin)
  end
end

class RodauthAdmin < Rodauth::Rails::Auth
  # ...
  after_create_account do
    db[:account_types].insert(account_id: account_id, type: "admin")
  end

  auth_class_eval do
    def account_ds(*)
      super.join(:account_types, account_id: :id).where(type: "admin")
    end
  end
end

and type table

class CreateAccountTypes < ActiveRecord::Migration
  def change
    create_table :account_types, id: false do |t|
      t.references :account, foreign_key: { on_delete: :cascade }, null: false
      t.string :type, null: false
    end
  end
end

After I create admin account RodauthApp.rodauth(:admin).create_account(login: "user@example.com", password: "secret") I got e-mail with verify link and after clicking on verification link an error occurs Need multiple FROM tables if updating/deleting a dataset with JOINs

And in console:

09:44:16 web.1  | Started POST "/admin/verify-account" for ::1 at 2022-05-30 09:44:16 +0200
09:44:16 web.1  | Processing by RodauthApp#call as HTML
09:44:16 web.1  |   Parameters: {"authenticity_token"=>"[FILTERED]", "commit"=>"Verify Account"}
09:44:16 web.1  |   Sequel (0.2ms)  SELECT "key" FROM "account_verification_keys" WHERE ("id" = 'b7d50431-6672-4e09-99e0-0b1cfdf5bb09') LIMIT 1
09:44:16 web.1  |   ↳ app/misc/rodauth_app.rb:13:in `block in <class:RodauthApp>'
09:44:16 web.1  |   Sequel (0.4ms)  SELECT * FROM "accounts" INNER JOIN "account_types" ON ("account_types"."account_id" = "accounts"."id") WHERE (("id" = 'b7d50431-6672-4e09-99e0-0b1cfdf5bb09') AND ("type" = 'admin') AND ("status" = 1)) LIMIT 1
09:44:16 web.1  |   ↳ app/misc/rodauth_app.rb:13:in `block in <class:RodauthApp>'
09:44:16 web.1  |   TRANSACTION (0.1ms)  BEGIN
09:44:16 web.1  |   ↳ app/misc/rodauth_app.rb:13:in `block in <class:RodauthApp>'
09:44:16 web.1  |   TRANSACTION (0.1ms)  ROLLBACK
09:44:16 web.1  |   ↳ app/misc/rodauth_app.rb:13:in `block in <class:RodauthApp>'
09:44:16 web.1  | Completed 500 Internal Server Error in 10ms (ActiveRecord: 0.8ms | Allocations: 4375)
09:44:16 web.1  |
09:44:16 web.1  |
09:44:16 web.1  |
09:44:16 web.1  | Sequel::Error (Need multiple FROM tables if updating/deleting a dataset with JOINs):

If I remove auth_class_eval the verification will pass withou error

[janko]

Thanks for reporting, this came up in a discussion as well, but I forgot to follow through. Could you try replacing the join with the subquery shown in the linked comment?

By the way, why was the id primary key removed from the table in the wiki page? Did it cause problems?

[jaahoo]

Yes I wrote it in revision message: "Hi, with id on account_types table I have 'PG::AmbiguousColumn: ERROR: column reference "id" is ambiguous' error in account_ds method. After I set id to false everything works. I tried to update the join query to works but I couldn't make it."

But with that your comment from that discussion I think the id could stay =) (it will fix it)

Only one small thing, you have super.where(id: db[:account_types].where(type: "admin").select(:id)) but that id is id of account_types table, so I change it to super.where(id: db[:account_types].where(type: "admin").select(:account_id))

This works with my test app. If you think it's ok I will update the wiki.

[janko]

Yes, thanks for the catch, the updated subquery looks good. Feel free to update the wiki 👍🏻

[jaahoo]

Problem solved The solution has been added to the wiki https://github.com/janko/rodauth-rails/wiki/Account-Types