Best way to use a custom query as a model dataset

[cpgo]

Hi all. I'm running some experiments with sequel to try and replace a mess of raw sql string manipulation on one of my apps.
I found about .with to generate CTEs and it looks perfect for me because I can directly translate some of my CTE queries.
So, by reading the docs I learned that I can pass a dataset to the base model class like so

class MarketSequelQuery < Sequel::Model(DB[some-query])
end

In my case I tried something like this

entry_level = Sequel.lit("custom sql string").as(:entry_level)

cte = DB[:impessions_by_showing_level].with(
  :impessions_by_showing_level,
  DB[Sequel[:markets].as(:m)].select(
    :id,
    entry_level,
  ).exclude(population: nil)
)

class MarketSequelQuery < Sequel::Model(cte)
end

I can use the model as MarketSequelQuery.where(id: 799) or MarketSequelQuery.all and so on.

But I think that creating the cte as an unscoped variable like that is kinda messy. Is there some api so I can setup the dataset more or less like this?

class MarketSequelQuery < Sequel::Model
  self.entry_level = Sequel.lit("custom sql string").as(:entry_level)

  self.set_dataset = DB[:impessions_by_showing_level].with(
    :impessions_by_showing_level,
    DB[Sequel[:markets].as(:m)].select(
      :id,
      self.entry_level,
    ).exclude(population: nil)
  )
end

Also, do I need to setup something on the model to signal that this is a "read-only" model?

[jeremyevans]

You can do:

MarketSequelQuery = Class.new(Sequel::Model)
MarketSequelQuery.class_eval do
  singleton_class.attr_accessor :entry_level
  # ...
end

In terms of signaling a read-only model, I'm not sure exactly what you are looking for. Are you looking for a specific error to be raised if you attempt to save the model? Do you want to freeze all instances so they cannot be modified?

[cpgo]

Hi jeremy, not sure I understand what you mean by that example (I'm a bit rusty on my ruby, sorry).
I kinda managed what I wanted by extracting the base CTE definiton to another class

class MarketCteDataset
  def self.cte
    entry_level = Sequel.lit("some-sql").as(:entry_level)

    DB[:impessions_by_showing_level].with(
      :impessions_by_showing_level,
      DB[Sequel[:markets].as(:m)].select(
        :id,
        entry_level
      ).exclude(population: nil)
    )
  end
end

And inheriting from that on the model

class MarketSequelQuery < Sequel::Model(MarketCteDataset.cte)

end

Is it possible to write a plugin for this kind of usecase? Not even sure if its possible to write such a plugin.

In terms of signaling a read-only model, I'm not sure exactly what you are looking for

Yeah, it would be just to avoid any confusion to make sure to raise a relevant error in case somebody does MarketSequelQuery.new(population: 1).save
I guess I could do something like

class MarketSequelQuery < Sequel::Model(MarketCteDataset.cte)
  def initialize(...)
    raise 'Read only class (or something like that)'
  end
end

But thats a minor detail, my main question is about the base CTE and if I'm setting up the dataset the right way.

[jeremyevans]

Personally, I would use a local variable in your case (the second code block in your original post). I don't understand what problem you are trying to solve by avoiding that. However, you shouldn't have a problem using the MarketCteDataset approach. In terms of a plugin, I'm not sure that would work, since in general you should have a working model before calling the plugin method, and your issue is creating a model with a custom dataset.

In terms of a read-only model, overriding initialize should do what you want, but it won't raise an error when trying to save/update an existing model. You may want to override save as well.

[cpgo]

I don't understand what problem you are trying to solve by avoiding that
Just trying to avoid some strange behaviour I had with rails class loader and zeitwerk.
Last year I had a class with some "dangling" variables like that and some times zeitwerk would just refuse to find the class constant during development and I had to restart the app.
But I could be wrong and the variables had nothing to do with the problem ( and I dont mean to bash on zeitwerk 😃 )

Thank you so much for the replies jeremy, I will go with the MarketCteDataset for the time being.

[JonathanFrias]

Here is a trick that I do sometimes to give myself scoped local variables:

class A < Sequel::Model
end

yield_self do # introduces a scope for local variables
  cte = ...
  A.set_dataset(cte)
end

[muellerj]

I'm not sure if this is along the same lines, but I was wondering about something similar:

I have a standard task-management setup (a Project has many Tasks). I would like each project to have its own tasks table (possibly named tasks-#{project.id}, so the ids of the tasks don't intermingle between different projects.

On the database level, this is easy:

@tasks = DB[:"tasks-#{@project.id}"]

Can I archieve something similar with Task.set_dataset(DB[:"tasks-#{@project.id}"])? The documentation mentions

Note that you should not use this to change the model's dataset at runtime. If you have that need, you should look into Sequel's sharding support, or creating a separate Model class per dataset

[jeremyevans]

You can probably handle this via:

  one_to_many :tasks, instance_specific: true do |ds|
    ds.from("tasks-#{id}")
  end

This doesn't change the model's dataset at runtime, it just changes the table used for the query.

I would advise against such an approach, though. If you need an auto incrementing task number per project, one way to handle it is an insert trigger on tasks that sets the task number for the inserted row. Something like this, though the syntax will depend on your database:

NEW.number := SELECT coalesce(max(number), 0) + 1 FROM tasks WHERE project_id = NEW.project_id;

With the appropriate transaction isolation level, that should be fine.

[muellerj]

That does seem like a much more focussed approach - thank you! I'm using Postgres (14.0) - can you give me a hint how to implement the trigger and the function? Assuming the column is called pid I tried the following but my syntax for the function definition seems to be off:

DB.create_table?(:tasks) do
  primary_key :id
  foreign_key :project_id, :projects, null: false
  Integer :pid, null: false
  String :title, null: false
  unique [:pid, :project_id]
end

DB.create_function(:fn_set_task_pid, <<~EOS, language: "PLPGSQL", returns: "TRIGGER")
  NEW.pid :=
    SELECT coalesce(max(pid), 0) + 1
    FROM tasks
    WHERE project_id = NEW.project_id;
EOS

DB.create_trigger(:tasks, :trg_set_task_pid, :fn_set_task_pid, after: true, events: [:insert])

[muellerj]

I managed to get this one working, for reference if anyone stumbles across something similar:

DB.create_function(:set_task_pid, <<~SQL, language: :plpgsql, returns: :trigger)
  BEGIN
    NEW.pid := (
      SELECT COALESCE(MAX(pid), 0) + 1
      FROM tasks
      WHERE project_id = NEW.project_id
    );
    RETURN NEW;
  END;
SQL

DB.create_trigger(:tasks, :trg_set_task_pid, :set_task_pid, events: [:insert], each_row: true)