Ordering by relationship value?


#1

I have a Questions table that has a hasMany relationship with a QuestionVote table.

I’m able to fetch Question models with their related Question vote models.

However, I want the Question models to be ordered descending based on each Question’s count of votes and I cannot figure out how to do this.

    var questions = await Question.query()
      .with("category")
      .with("poster")
      .with("upvotes", (builder) => {
        builder.where("user_id", user.id);
        builder.where("is_positive", true);
      })
      .with("downvotes", (builder) => {
        builder.where("user_id", user.id);
        builder.where("is_positive", false);
      })
      .with("allupvotes", (builder) => {
        builder.where("is_positive", true);
      })
      .with("allupvotescount", (builder) => {
        builder.count("is_positive", true);
      })
      .with("alldownvotes", (builder) => {
        builder.where("is_positive", false);
      })
      .with("answers.author")
      .paginate(Number(request.input("page", 1)), 10);

I obviously can’t just do an .orderBy as the related votes are a relation, not a column.

What is the best way for me to take the questions, order them based on the allupvotescount for example in order to return them as questions.toJSON() to a view?

Thanks


#2

You need to use a join if you want to sort by a relation.

Here’s an example where I’m sorting by ‘author’ (user first/last names)

audiences = await Audience
  .query()
  .select('audiences.*')
  .join('users', 'audiences.user_id', '=', 'users.id')
  .orderBy('users.first_name', request.input('direction', 'asc'))
  .orderBy('users.last_name', request.input('direction', 'asc'))
  .with('author', (builder) => builder.select('id', 'first_name', 'last_name'))
  .with('organization', (builder) => builder.select('id', 'name'))
  .with('tags', (builder) => builder.select('id', 'name'))
  .paginate(request.input('page', 1), request.input('perPage', 10))

Note the select enforces that just those fields in the audiences table are actually pulled to populate the audience models in the result, but the join allows for ordering on the user relationship.

Then the various eager loads attach the relevant related data I care about, and finally I paginate.

Works great. I simply use a switch/case to apply the appropriate join depending on which thing I want to sort by.

NOTE: This specifically is for belongsTo relations where the thing you’re sorting by will only ever have one relation to the primary item. You could accomplish similar for many:many relations, but would need to also include some grouping/etc…


#3

Thanks for the response. I actually found a different way to go about doing it, so will paste it below in case anyone comes across this again in the future:

    var questions = await Question.query()
      .with("category")
      .with("poster")
      .with("upvotes", (builder) => {
        builder.where("user_id", user.id);
        builder.where("is_positive", true);
      })
      .with("downvotes", (builder) => {
        builder.where("user_id", user.id);
        builder.where("is_positive", false);
      })
      .withCount("allupvotes", (builder) => {
        builder.where("is_positive", true);
      }) 
      .withCount("alldownvotes", (builder) => {
        builder.where("is_positive", false);
      }) 
      .with("answers.author")
      .orderBy("allupvotes_count", "desc")
      .paginate(Number(request.input("page", 1)), 10);

So a .withCount instead of just a .with then ordering by ‘_count’ worked.

Cheers


#4

:+1:

Of course that won’t work if it’s not for counts. :wink: