CODEWITHSUNDEEP
knex.js
tjr226
tjr226

Reputation: 675

Knex uses one set of variables to SELECT, and another to JOIN

I'm building a DB query in Knex. The joins and filtering are working like I want, and I used the .select() function to avoid overwriting the ID variable with the latest join.

However, I'm not sure if this code is as "clean" as I can make it. I don't like that I'm selecting columns with one name, but then doing the join with the original name.

Are there ways I can make this cleaner and clearer?

    return db('user_cards')
        .select([ 'user_cards.id as user_cards_id', 'user_cards.user_id', 'user_cards.card_id', 'user_cards.unix_timestamp',
            'cards.id as card_id', 'cards.question as card_question', 'cards.answer as card_answer', 'cards.lecture_segment_id as lecture_segment_id',
            'lecture_segments.id as lecture_segment_id', 'lecture_segments.lecture_id as lecture_id'
        ])
        .join('cards', 'user_cards.card_id', 'cards.id')
        .join('lecture_segments', 'cards.lecture_segment_id', 'lecture_segments.id')
        .where('user_id', user_id)
        .where('lecture_id', lecture_id)

Details:

The product is a flashcard app where the flashcards are tied to users, lecture segments, and overall lectures.

In the query, I'm accessing these databases:

User_Cards, which has FKs for user_id and card_id Cards, which has FK for lecture_segment_id Lecture_Segment_Id, which has an FK for lecture_id.

Upvotes: 0

Views: 434

Answers (1)

Rich Churcher
Rich Churcher

Reputation: 7654

Well, you allude to one way you could make it... at least, more compact? This isn't always best, but sometimes I find it makes you more likely to fully qualify column names, be thorough, etc.

Each time you mention a table, you can give it an alias. This might leave you with something like:

db("user_cards.as uc")
  .select([
    "uc.id as uc.id",
    "uc.user_id",
    "uc.card_id",
    "uc.unix_timestamp",
    "c.id as card_id",
    "c.question as card_question",
    "c.answer as card_answer",
    "c.lecture_segment_id as lecture_segment_id",
    "ls.id as lecture_segment_id",
    "ls.lecture_id as lecture_id"
  ])
  .join("cards as c", "uc.card_id", "c.id")
  .join("lecture_segments.as ls", "c.lecture_segment_id", "ls.id")
  .where("uc.user_id", user_id)
  .andWhere("uc.lecture_id", lecture_id);

You'll need to be the judge of whether this is more readable/maintainable.

Another practice you could consider: maintaining the list of columns in a separate variable (or a function that generates the list according to current state):

db("user_cards as uc").select(FLASHCARD_MAIN_VIEW_REQUIREMENTS)

or some such easy-to-search-for name.

Upvotes: 1

Related Questions