As I'm transitioning from ActiveRecord to Sequel to make more use of Postgres' and SQL's capabilities I'm running into some differences occasionally. We've been using a readonly AR model to represent a materialized view that we use as a convenience "table" to avoid a lot of joining.
I have the following contrived setup in Postgres (http://sqlfiddle.com/#!17/e4f6e/1) CREATE TABLE persons ( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL ); INSERT INTO persons (name) VALUES ('Steve'), ('John'), ('Bonucci'), ('Rabiot'); CREATE TABLE employee_records ( id SERIAL PRIMARY KEY, person_id INTEGER NOT NULL REFERENCES persons(id), employment_start_date DATE NOT NULL DEFAULT NOW() ); INSERT INTO employee_records (person_id) VALUES (1), (3); CREATE MATERIALIZED VIEW employees AS ( SELECT p.id AS person_id, er.id AS employee_record_id, p.name, er.employment_start_date FROM employee_records er INNER JOIN persons p ON (p.id = er.person_id) ) WITH DATA; CREATE TABLE tasks ( id SERIAL PRIMARY KEY, assigned_to_employee_id INTEGER NOT NULL REFERENCES employee_records(id), title VARCHAR NOT NULL, completed_at TIMESTAMP WITH TIME ZONE ); INSERT INTO tasks (assigned_to_employee_id, title) VALUES (1, 'Pick up dry-cleaning'), (2, 'Do the dishes'), (1, 'Feed the cat'); The respective (two) models are: class Task < Sequel::Model dataset_module do # Uncompleted first in the order, according to due_at, they should be completed. # Followed by completed tasks in the order they were completed. def in_todo_order order(Sequel.desc(:completed_at, nulls: :first)) end end many_to_one :assigned_to_employee, class: '::ORM::EmployeeFullInfo', key: :assigned_to_employee_id, primary_key: :employee_record_id end class Employee < Sequel::Model one_to_many :assigned_tasks, key: :assigned_to_employee_id, primary_key: :employee_record_id, class: '::Task' end This works _almost_ fine! Employee.first.assigned_tasks works, Task.first.assigned_to_employee works. However I've got two remaining issues that I'm unsure how to proceed with: Calling assigned_tasks_dataset on Employee results in the following error: ::ORM::Employee.first.assigned_tasks_dataset (0.001130s) SELECT * FROM "employee" LIMIT 1 Sequel::Error: No primary key is associated with this model from /usr/local/bundle/gems/sequel-5.5.0/lib/sequel/model/base.rb:1361:in `pk' It makes sense since there is of course no primary key. I don't understand why the "naked" association (Employee.first.assigned_tasks) but not the _dataset method. Is this something I can solve? Additionally if I understand a GitHub issue I read last night the right way to make a "model" read-only is to override the save/update/delete methods. I would be fine with that for now but is there perhaps another established pattern to deal with materialized views like the one I have and "treating them like read only models" (i.e. how does the community deal with this?) Thank you -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sequel-talk. For more options, visit https://groups.google.com/d/optout.
