Select adds unnecessary table name in relation load


#1

When doing

    await game.load('languages', builder => {
      builder.select('gameLanguages.isDefault');
    });

it adds unecessary table name to the selected column - languages.gameLanguages.isDefault, how do I prevent this? the relation is belongsToMany


#2

Are you trying to select the default language? I’m going to assume by belongsToMany relationship that gameLanguages table is a join table between games table and languages table that allows the many-to-many relationship.

games ↔︎ gameLanguages ↔︎ languages

If I’m not mistaken, the context of builder is always set to the table you specified as the first argument of .load() method. You can join and do a WHERE clause like so:

await game.load('languages', builder => {
  builder.innerJoin('gameLanguages', 'gameLanguages.language_id', 'languages.id')
    .where('gameLanguages.isDefault', true);
});

If this is not what you were trying to accomplish, please clarify your intentions.


#3

With belongsToMany the join is already made, I just want to pick the isDefault column from the pivot table to have it in the main results/model attributes instead of the pivot property, so that I dont have to do a for loop to move it afterwards.


#4

Ah, okay. Looking at Lucid source code, I don’t think there’s a way to select a table from the relation query builder:

That’s the part that runs the builder => {} function you passed in as part of the .load method. As you can see, the callback is called with relationInstance which will be your Language model.

So lazy eagerloading might not be what you want to do if you’re simply just trying to get the isDefault column.

Instead, you might have to build up your query more manually:

const isDefaultLanguage = await Language.query()
  .innerJoin('gameLanguages', 'gameLanguages.languageId', 'languages.id')
  .where('gameLanguages.gameId', game.id)
  .select('gameLanguages.isDefault') // Don't query unnecessary columns
  .pluck('gameLanguages.isDefault') // Pluck out the isDefault value from the query result

Better yet, if you already defined the Pivot Model called GameLanguage, you can forego inner join altogether and just do:

const isDefaultLanguage = await GameLanguage.query()
  .where('gameId', game.id)
  .select('isDefault')
  .pluck('isDefault');

This has exactly same effect of doing lazy eagerloading (meaning you make one extra query to DB) but you have more control over how you retrieve the data you need.

Hope this solves your needs.


#5

Thanks, that really helps as I just started with Adonis!