Rails - Select, join, and order

Question

I am trying to order my assets so that they are appearing in descending order based off of the number of users. This code works in Postgres, but it doesn't seem to be working in Ruby. Not sure what is wrong. Any help is appreciated. Thank you in advance.

def order_assets
  @asset = Asset.select("assets.id, count(assets_users.asset_id) as the_count")
                .joins("LEFT OUTER JOIN assets_users ON assets.id = assets_users.asset_id")
                .group("assets.id")
                .having("count(*) > 0")
                .order("the_count")
end

I want all of the yellow'd assets to be on the top, when the ones with users filled in below.

Screenshot of Problem

Postgres code:

SELECT
        assets.id,
        count(assets_users.asset_id) as the_count
FROM assets
LEFT OUTER JOIN assets_users ON assets.id = assets_users.asset_id
GROUP BY assets.id
HAVING count(*) > 0
ORDER BY the_count;

This is how the Postgres comes out:

Screenshot of Postgres

Did you try to add .to_sql to your ActiveRecord query? That method willreturn the SQL it creates. That might help you to find the differences betwen the query from Rails and the pure SQL query. Furthermore you write doesn't seem to be working, can you specify that? — spickermann
– spickermann, Commented Nov 11, 2014 at 20:20
The .to_sql comes out exactly as my sql code is typed out. And, the ruby code just doesn't seem to have any effect on the order of the assets. — Briknthewall
– Briknthewall, Commented Nov 11, 2014 at 20:55
So the result in Rails is not ordered like expected? What is the order in Rails? — spickermann
– spickermann, Commented Nov 12, 2014 at 0:30
Is it possible that you are re-ordering the results in the view layer somehow? — San
– San, Commented Nov 12, 2014 at 16:27

Briknthewall · Accepted Answer · 2014-11-12 17:25:31Z

Ended up moving everything over to the Asset model. Will post that code under the answer's code, if anyone needs/wants it.

I first switched assets.id to assets.*, because there was an asset_profile_id parameter that wasn't going through. I included the .where function, so that the query would know which asset_profile to get the assets from. The only other thing I added in was an additional ordering for the assets, so that the remainders would be ordered based off of their id number.

def set_assets
  @assets = Asset.select("assets.*, count(assets_users.asset_id) as the_count").joins("LEFT OUTER JOIN assets_users ON assets.id = assets_users.asset_id").where(asset_profile_id: params[:id]).group("assets.id").having("count(*) > 0").order("the_count ASC, assets.id ASC")
end

I ended up moving the code over to a scope within the Asset model:

scope :ordered_by_user_count, -> (profile_id) { 
  select("assets.*, count(assets_users.asset_id) as the_count")
    .joins("LEFT OUTER JOIN assets_users ON assets.id = assets_users.asset_id")
    .where(asset_profile_id: profile_id)
    .group("assets.id")
    .having("count(*) > 0")
    .order("the_count ASC, assets.id ASC")
}

Thank you guys for steering me in the right direction.

Collectives™ on Stack Overflow

Rails - Select, join, and order

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related