How can I get the rows of a table in the same order as it is in my database, using ObjectionJS relationMapping?

Question

I have a table called pathway, pathway_courses, and courses. The idea is, each pathway has certain number of courses. pathway_courses is the join table joining pathway table from id to courses table. Basically,

pathway (id) --> pathway_courses(pathway_id) <--> pathway_courses(course_id) <-- courses(id)

I am using ObjectionJS ORM and used ManyToMany relation mapping.

And this is my response when I try to fetch data.


  "courses": {
    "id": 1,
    "code": "FRTEND",
    "name": "Front-End",
    "description": "Learn Front End Development",
    "created_at": "2020-09-18T12:36:33.299Z",
    "tracking_enabled": true,
    "tracking_frequency": "weekly",
    "tracking_day_of_week": 2,
    "tracking_days_lock_before_cycle": 7,
    "courses": [
      {
        "id": "60",
        "type": "html",
        "name": "Let's talk Technology - SV201",
        "logo": null,
        "notes": null,
        "days_to_complete": "30",
        "short_description": "Let's talk on various technology topics to improve our understanding of Technology as well as English",
        "sequence_num": "20",
        "created_at": "2020-09-20T19:23:15.991Z"
      },
      {
        "id": "59",
        "type": "html",
        "name": "Kuch Baatein (Samvaad) - SV101",
        "logo": null,
        "notes": null,
        "days_to_complete": "30",
        "short_description": "Let's start talking in English with the help of these very simple topics",
        "sequence_num": "19",
        "created_at": "2020-09-20T19:23:15.991Z"
      },
      {
        "id": "61",
        "type": "html",
        "name": "Let's talk Technology (Samvaad) - SV301",
        "logo": null,
        "notes": null,
        "days_to_complete": "30",
        "short_description": "Let's talk on various technology topics to improve our understanding of Technology as well as English",
        "sequence_num": "22",
        "created_at": "2020-09-20T19:23:15.991Z"
      },
      {
        "id": "33",
        "type": "html",
        "name": "Hackathon - Dry Run on a Dry Day",
        "logo": null,
        "notes": null,
        "days_to_complete": "45",
        "short_description": "Isse aap program ka dry run ke through program ka control flow bananana seekhoge.",
        "sequence_num": "0",
        "created_at": "2020-09-20T19:23:15.991Z"
      }
    ]
  }
}

But the order in which I get the courses is not correct.

How do I solve this. How can I get the courses in the same order as in my table?

I don't think you should expect data to be returned in any specific order from the database unless you specify the order by adding an .orderBy() to your query. This ensures that the result is ordered by the appropriate key. Generally databases can reorganise data from time to time and if you don't specify it, the order can change arbitrarily. Your example is somewhat confusing as there is an [id] column and also a [course_id] column, but the JSON data is reporting the value of [course_id] under the key [id]. Maybe there is some confusion between these columns in your code? — JohnRC, Sep 20 '20 at 21:05

score 0 · Answer 1 · answered Sep 22 '20 at 07:38

When you query your courses table directly DB might not use index or might use different indexes to execute the query. Also when indexes grow the ordering may change and is not guaranteed to be the same on different machines. As @johnrc said you should add .orderBy part to your query to make order to be stable.

You could maybe make this work by adding filter / modify attribute to your relation mapping. https://vincit.github.io/objection.js/api/types/#type-relationmapping

filter: query => query.orderBy('id')

How can I get the rows of a table in the same order as it is in my database, using ObjectionJS relationMapping?

1 Answers1