Rails: how to sort a many-to-many relationship
I have a many-to-many relationship between a User and Picture model. They are linked by a join table called an image.
If I get a list of users of a single image, i.e. picture.users -> how can I ensure that the resulting result is sorted either by creating an overlay string (i.e. the order the image was linked to by the user). How does this change if I want to get it in order of modification?
Thanks!
Edit Maybe something like
picture.users.where(:order => "created_at")
- but this created_at refers to the created_at in the image
a source to share
You have an extra column similar to a sequence in your image table and define the sort order as the default area in your graphic
default_scope :order => 'sequence ASC'
If you need a default sort order based on modified_at use the following default scope
default_scope :order => 'modified_at DESC'
a source to share
Well, in my case, I need to sort a many-to-many relationship on a column named weight in the middle table. After hours of trying, I figured out two solutions to a many-to-many sort relationship .
Solution1: In Rails mode
picture.users.where(:order => "created_at")
cannot return ActiveRecord :: Relation sorted by Picturization created_at column.
I tried to rewrite the default_scope method in the overlay scope, but it doesn't work:
def self.default_scope
return Picturization.all.order(weight: :desc)
end
Instead, first, you need to get the IDs of the sorted image:
ids = Picturization.where(user_id:user.id).order(created_at: :desc).ids
Then you can get sorted objects with MySQL field functin
picture.users.order("field(picturizations.id, #{ids.join(",")})")
which generates the SQL looks like this:
SELECT `users`.*
FROM `pictures` INNER JOIN `picturizations`
ON `pictures`.`id` = `picturizations`.`picture_id`
WHERE `picturizations`.`user_id = 1#for instance
ORDER BY field(picturizations.id, 9,18,6,8,7)#for instance
Solution2: in the original SQL path
you can get the answer directly using the order by function :
SELECT `users`.*
FROM `pictures` INNER JOIN `picturizations`
ON `pictures`.`id` = `picturizations`.`picture_id`
WHERE `picturizations`.`user_id = 1
order by picturizations.created_at desc
a source to share