Databases
StackOverflow2013
Postsdbo
POCan I retrieve objects with Sequel from a complex query that limits results to fields from a single table?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCan I retrieve objects with Sequel from a complex query that limits results to fields from a single table?
    text
    copied!<p>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 <code>set_dataset</code> 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.</p> <p>I basically have two classes</p> <pre><code>class SortFields &lt; Sequel::Model(:sort_fields) set_primary_key :objectid end class Items &lt; Sequel::Model(:items) set_primary_key :objectid one_to_one :sort_fields, :class =&gt; SortFields, :key =&gt; :objectid end </code></pre> <p>Some backstory: the data is imported from a legacy system into mysql. The values in <code>sort_fields</code> 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 <code>sort_fields</code> to <code>items</code> (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.</p> <p>If I set the dataset to sort on a field in <code>items</code> like so</p> <pre><code>class Items &lt; Sequel::Model(:items) set_primary_key :objectid one_to_one :sort_fields, :class =&gt; SortFields, :key =&gt; :objectid set_dataset(order(:sortnumber)) end </code></pre> <p>then the expected clause is added to the generated SQL, e.g.:</p> <pre><code>&gt;&gt; Items.limit(1).sql =&gt; "SELECT * FROM `items` ORDER BY `sortnumber` LIMIT 1" </code></pre> <p>and queries still return objects:</p> <pre><code>&gt;&gt; Items.limit(1).first.class =&gt; Items </code></pre> <p>If I order it by the associated fields though...</p> <pre><code>class Items &lt; Sequel::Model(:items) set_primary_key :objectid one_to_one :sort_fields, :class =&gt; SortFields, :key =&gt; :objectid set_dataset( eager_graph(:sort_fields). order(:sort1, :sort2, :sort3) ) end </code></pre> <p>...I get hashes</p> <pre><code>?&gt; Items.limit(1).first.class =&gt; Hash </code></pre> <p>My first thought was that this happens because all fields from <code>sort_fields</code> are included in the results and maybe if selected only the fields from <code>items</code> I would get <code>Items</code> objects again:</p> <pre><code>class Items &lt; Sequel::Model(:items) set_primary_key :objectid one_to_one :sort_fields, :class =&gt; SortFields, :key =&gt; :objectid set_dataset( eager_graph(:sort_fields). select(:items.*). order(:sort1, :sort2, :sort3) ) end </code></pre> <p>The generated SQL is what I would expect:</p> <pre><code>&gt;&gt; Items.limit(1).sql =&gt; "SELECT `items`.* FROM `items` LEFT OUTER JOIN `sort_fields` ON (`sort_fields`.`objectid` = `items`.`objectid`) ORDER BY `sort1`, `sort2`, `sort3` LIMIT 1" </code></pre> <p>It returns the same rows as the <code>set_dataset(order(:sortnumber))</code> version but it still doesn't work:</p> <pre><code>&gt;&gt; Items.limit(1).first.class =&gt; Hash </code></pre> <p>Before I add the sort fields to the <code>items</code> 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?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload