SQL How To » Order with attribute of an associated record
This article assumes:
- You have a
userstable - You have a
teamstable
We have Users, each users is associated to a Team. Let's imagine we want to get all the users, but we want them ordered by Team name, and if they're in the same team, we want them to be ordered by creation date. Here are our relations:
module Relations
class Users < ROM::Relation[:sql]
attribute :created_at, ROM::Types::DateTime
schema(:users) do
associations do
belongs_to :team
end
end
end
class Teams < ROM::Relation[:sql]
schema(:teams) do
attribute :name, ROM::Types::String
associations do
has_many :users
end
end
end
end
And here is the code to get such ordering:
2.5.3 :003 > users.join(:teams).order(teams[:name], :created_at)
=> #<Relations::Users name=ROM::Relation::Name(users) dataset=#<Sequel::Mysql2::Dataset: "SELECT `users`.`id`,
`users`.`name`, `users`.`created_at`, `users`.`team_id` FROM `users` INNER JOIN `teams` ON (`users`.`team_id` =
`teams`.`id`) ORDER BY `teams`.`name`, `created_at`">>
2.5.3 :004 > users.combine(:teams).join(:teams).order(teams[:name], :created_at).to_a
=> [#<ROM::Struct::User id=1 name="John Doe" created_at=2019-07-07 15:54:14 +0200 team_id=1 team=#<ROM::Struct::Team
id=1 name="A team">>, #<ROM::Struct::User id=3 name="Jack Doe" created_at=2019-07-07 15:54:27 +0200 team_id=1
team=#<ROM::Struct::Team id=1 name="A team">>, #<ROM::Struct::User id=2 name="Jane Doe" created_at=2019-07-07 15:54:22
+0200 team_id=2 team=#<ROM::Struct::Team id=2 name="B team">>]
If you want to know more about how you can use "#order" method, you can take a look at the API docs.