1

I have a model to which I need to create a default scope. I am unsure of the best way to write this scope but I will explain how it needs to work.

Basically I need to get all items of the model and if two items have the same "order" value then it should look to the "version" field (which will contain, 1, 2, 3 etc) and pick the one with the highest value.

Is there a way of achieving this with just a scope?

rctneil
  • 7,016
  • 10
  • 40
  • 83

5 Answers5

1

Try this code: 

scope :group_by_order, -> { order('order ASC').group('order') }
default_scope, { (group_by_order.map{ |key,values| values.order('version DESC') }.map{|key, values| values - values[1..-1]}).values.flatten }

Explanation Code:

  1. order by "order" field.
  2. group by "order" field.
  3. map on the result hash, and order each values by "version" field
  4. map again on values, and remove from index "1" to the end.
  5. get all values, and flatten them
Mohamed Yakout
  • 2,868
  • 1
  • 25
  • 45
  • Thanks for this. Will try this shortly. Any chance you could add and explanation of how this is actually working? – rctneil Aug 17 '14 at 09:56
  • When trying this I get "unexpected "}". – rctneil Aug 17 '14 at 10:08
  • Hmmmm, Still getting an unexpected } on the default_scope line – rctneil Aug 17 '14 at 10:21
  • actually I used sublime, and I can't find unnecessary "}" ?, can you help me to find this brackets :), or use the explanation code steps for your scope :) – Mohamed Yakout Aug 17 '14 at 10:26
  • if you use default scope, you use it as default_scope { (group_by_order.map{ |key,values| values.order('version DES') }.map{|key, values| values - values[1..-1]}).values.flatten }. When you use named scopes, -> (lambda) sign should be used after the scope name – Andrey Deineko Aug 17 '14 at 10:27
  • I update my solution as @andrey said, you're right :) – Mohamed Yakout Aug 17 '14 at 10:34
  • Ok. A gist of my results now after using @andrey-deineko 's code: https://gist.github.com/anonymous/9340c9ebd4c612252474 – rctneil Aug 17 '14 at 10:35
  • you have to delete word scope and , in the default_scope, like I showed :) `default_scope { (group_by_order.map{ |key,values| values.order('version DESC') }.map{|key, values| values - values[1..-1]}).values.flatten }` – Andrey Deineko Aug 17 '14 at 10:42
  • Yup, done that. Still getting the DB errors shown in this gist: https://gist.github.com/anonymous/9340c9ebd4c612252474 – rctneil Aug 17 '14 at 10:44
  • I spotted that and fixed that one earlier but it still fails. – rctneil Aug 17 '14 at 10:48
  • I'm wondering if it's confusing my field name called "order" with the "order" command. Is that possible? – rctneil Aug 17 '14 at 10:51
  • try to use group_by(&:order) instead of group('order') – Mohamed Yakout Aug 17 '14 at 10:53
  • ^ No difference. Still failing with "ORDER BY order ASC" in the query. – rctneil Aug 17 '14 at 11:00
  • Try this code `default_scope, { (group_by_order.map{ |key,values| values.sort_by{ |v| v.version } }.map{|key, values| values - values[0..-2]}).values.flatten }` – Mohamed Yakout Aug 17 '14 at 11:12
  • ^ Again, no change. Still failing about "order". Sorry for the troubles. I really hope we can get this working. – rctneil Aug 17 '14 at 11:19
  • The ordering based on "order" works fine with order(order: :asc) but as soon as you try to group_by based on "order" it fails. Any ideas at all? – rctneil Aug 17 '14 at 12:53
  • This is the part it is failing at: Coaster Load (7.9ms) SELECT "coasters".* FROM "coasters" ORDER BY "coasters"."order" ASC ArgumentError: wrong number of arguments (1 for 0) – rctneil Aug 17 '14 at 13:53
  • Ok, so some more information on this issue. I have renamed the "order" field to "order_ridden", just to be on the safe side as I think that could have been interfering. Also, I am getting the following SQL error now based on `.group`: `ActiveRecord::StatementInvalid: PG::GroupingError: ERROR: column "coasters.id" must appear in the GROUP BY clause or be used in an aggregate function` If I stop it from grouping, it says that `.sort_by` is undefined. The latest version of the code is in the next comment. – rctneil Aug 17 '14 at 15:42
  • Latest code: ` scope :group_by_order, lambda { order(order_ridden: :asc).group(:order_ridden) } default_scope { (group_by_order.map{ |key,values| values.sort_by{ |v| v.version } }.map{|key, values| values - values[0..-2]}).values.flatten }` Also just for your information. The `version` field may have an integer value in it up to any value or just be empty. – rctneil Aug 17 '14 at 15:43
  • Anymore thoughts on this @MohamedYakot ? I really want to get this sort but am really stuck to how to get this set up correctly as a scope. – rctneil Aug 17 '14 at 18:32
  • OMG! Grouping? Sql can just order on two fields by default. What are you doing here? A `group by` needs an aggregate function to actually mean something (like `SUM, AVG, ...`) so this is completely incorrect. – nathanvda Aug 23 '14 at 12:00
0

A word of caution using default scopes with order. When you performs updated on the collection such as update_all it will use the default scope to fetch the records, and what you think would be a quick operation will bring your database to its knees as it copies the rows to a temporary table before updating.

I would recommend just using a normal scope instead of a default scope.

Have a look at Select the 3 most recent records where the values of one column are distinct on how to construct the sql query you want and then put that into a find_by_sql statemate mentioned in How to chain or combine scopes with subqueries or find_by_sql

Community
  • 1
  • 1
roo
  • 7,106
  • 8
  • 39
  • 45
  • Thanks for commenting. I did previously look into grouping in SQL but it really confused me as to what it did. I tried your previous code snippet and get: `ActiveRecord::StatementInvalid: PG::GroupingError: ERROR: column "coasters.id" must appear in the GROUP BY clause or be used in an aggregate function` I am using PostgreSQL by the way. – rctneil Aug 20 '14 at 15:17
  • Just been trying to return the correct results directly in SQL. I ahve this query: "SELECT order_ridden, max(version) AS version FROM coasters GROUP BY order_ridden" This returns the correct number of results but I need all the other fields. Whenever I try to add them it says they need to be included in the group by clause and when I do that I get wrong results. Any ideas? Once Ia hev it working in plain SQL I can then move on to translating it into ActiveRecord and using it as a normal scope. – rctneil Aug 20 '14 at 17:10
  • Any more ideas on this? I just need it to add on the rest of the fields but the fields from the record that match the max(version) record. I'm desperate to get this resolved. – rctneil Aug 20 '14 at 20:11
0

The ActiveRecord order method simply uses the SQL ORDER function which can have several arguments. Let's say you have some model with the attributes order and version then the correct way order the records as you describe it, is order(:order, :version). If you want this as the default scope would you end up with:

default_scope { order(:order, :version) }
jokklan
  • 3,520
  • 17
  • 37
0

First, default_scopes are dangerous. They get used whenever you use the model, unless you specifically force 'unscoped'. IME, it is rare to need a scope to every usage of a model. Not impossible, but rare. And rarer yet when you have such a big computation.

Instead of making a complex query, can you simplify the problem? Here's one approach:

In order to make the version field work, you probably have some code that is already comparing the order fields (otherwise you would not have unique rows with the two order fields the same, but the version field differing). So you can create a new field, that is higher in value than the last field that indicated the right entity to return. That is, in order to create a new unique version, you know that you last had a most-important-row. Take the most-important-rows' sort order, and increment by one. That's your new most-important-rows' sort order.

Now you can query for qualifying data with the highest sort order (order_by(sort_order, 'DESC').first).

Rather than focus on the query, focus on whether you are storing the right data, that can the query you want to achieve, easier. In this case, it appears that you're already doing an operation that would help identify a winning case. So use that code and the existing database operation, to reduce future database operations.

JezC
  • 1,868
  • 17
  • 19
0

In sql you can easily order on two things, which will first order on the first and then order on the second if the first thing is equal. So in your case that would be something like

select * from posts order by order_field_1, version desc

You cannot name a column order since it is a sql reserved word, and since you did not give the real column-name, I just named it order_field_1.

This is easily translated to rails:

Post.order(:order_field_1, version: :desc)

I would generally advice against using default_scope since once set it is really hard to avoid (it is prepended always), but if you really need it and know the risks, it is really to apply as well: 

class Post < ActiveRecord::Base

  default_scope { order(:order_field_1, version: :desc) }

end

This is all actually documented very well in the rails guides.

nathanvda
  • 49,707
  • 13
  • 117
  • 139