How can I make a model join query(condition) and sort on relation models on Sails?
Example: I have 4 tables(collections in mongodb) and 4 related models in mongodb:
- User: user_id, name
- Follow: user_id, following_id (user id is being followed)
- Point: user_id, point
- Post: name, content, user_id, created_at
So from the post table, I want to make a query to find the posts of users that I'm following and sort by their point. Like this raw sql:
SELECT post.* FROM post
LEFT JOIN user_point up ON up.user_id = post.user_id
WHERE post.user_id IN (1,2,3,4) // assume I got my following_user_ids result is 1,2,3,4 for this case so no need to join follow table
ORDER BY up.point DESC // high point then first return
I don't know how can do this by Sails model? I have read many instructions by got no helps. Almost people said: Sails Association, but it just helps return the relation instead of do the where or order by to sort original model results(is this case: post).
I have worked with Yii2, a PHP framework so with this case I can do it easily:
Post::model()->leftJoin('user_point up', 'up.user_id = post.user_id')->where(['post.user_id' => [1,2,3,4])->orderBy(['up.point' => SORT_DESC])->all();
I'm stucked in Sails, very thanks if someone help me!!!
