Can I retrieve objects from a complex query that limits results to fields from a single table?
- by Sean Redmond
I have a model whose rows I always want to sort based on the values in another associated model and I was thinking that the way to implement this would be to use set_dataset in the model. This is causing query results to be returned as hashes rather than objects, though, so none of the methods from the class can be used when iterating over the dataset.
I basically have two classes
class SortFields < Sequel::Model(:sort_fields)
set_primary_key :objectid
end
class Items < Sequel::Model(:items)
set_primary_key :objectid
one_to_one :sort_fields, :class => SortFields, :key => :objectid
end
Some backstory: the data is imported from a legacy system into mysql. The values in sort_fields are calculated from multiple other associated tables (some one-to-many, some many-to-many) according to some complicated rules. The likely solution will be to just add the values in sort_fields to items (I want to keep the imported data separate from the calculated data, but I don't have to). First, though, I just want to understand how far you can go with a dataset and still get objects rather than hashes.
If I set the dataset to sort on a field in items like so
class Items < Sequel::Model(:items)
set_primary_key :objectid
one_to_one :sort_fields, :class => SortFields, :key => :objectid
set_dataset(order(:sortnumber))
end
then the expected clause is added to the generated SQL, e.g.:
>> Items.limit(1).sql
=> "SELECT * FROM `items` ORDER BY `sortnumber` LIMIT 1"
and queries still return objects:
>> Items.limit(1).first.class
=> Items
If I order it by the associated fields though...
class Items < Sequel::Model(:items)
set_primary_key :objectid
one_to_one :sort_fields, :class => SortFields, :key => :objectid
set_dataset(
eager_graph(:sort_fields).
order(:sort1, :sort2, :sort3)
)
end
...I get hashes
?> Items.limit(1).first.class
=> Hash
My first thought was that this happens because all fields from sort_fields are included in the results and maybe if selected only the fields from items I would get Items objects again:
class Items < Sequel::Model(:items)
set_primary_key :objectid
one_to_one :sort_fields, :class => SortFields, :key => :objectid
set_dataset(
eager_graph(:sort_fields).
select(:items.*).
order(:sort1, :sort2, :sort3)
)
end
The generated SQL is what I would expect:
>> Items.limit(1).sql
=> "SELECT `items`.* FROM `items` LEFT OUTER JOIN `sort_fields` ON (`sort_fields`.`objectid` = `items`.`objectid`) ORDER BY `sort1`, `sort2`, `sort3` LIMIT 1"
It returns the same rows as the set_dataset(order(:sortnumber)) version but it still doesn't work:
>> Items.limit(1).first.class
=> Hash
Before I add the sort fields to the items table so that they can all live happily in the same model, is there a way to tell Sequel to return on object when it wants to return a hash?
