Help on orderBy with relationShip - ER-NON-UNIQUE-ERROR

I have Companies. This Companies have a CompanyCard. And each Company can have Accounts which are other companies.

tables:
company: id, name.
company_card: id, company_id, short_name.
accounts: id, company_id, target_company_id.

Company.js

  card(){
    return this.hasOne('App/Models/CompanyCard')
  }

  accounts(){
    return this.hasMany(
      'App/Models/Account'
    )     
  }

Account.js

  company(){
    return this.belongsTo('App/Models/Company')
  }

  target(){
    return this.belongsTo(
      'App/Models/Company',
      'target_company_id')
  }

I’m having trouble getting the accounts ordered by the company_card.short_name.

This is the query:

let accounts = Account
.query()
.where('company_id',companyId)
.select('accounts.*')
.from('accounts')
.leftJoin('company_cards','accounts.target_company_id','company_cards.company_id')
.orderBy('company_cards.short_name', sortDirection)
	
const result = await accounts.paginate(pageNumber,limit)

I get the following error:
{“error”:{“message”:“select count(*) as total from accounts left join company_cards on accounts.target_company_id = company_cards.company_id where company_id = 28 and accounts.company_id = 28 - ER_NON_UNIQ_ERROR: Column ‘company_id’ in where clause is ambiguous”

I guessthe query is confused with accounts.company_id and company_cards.company_id, but I can’t find the way to solve it.

Any sugestion?

Instead of leftJoins, you can’t use:

let accounts = Account
.query()
.where('company_id', companyId)
.with('company', (builder) => {
    builder.with('card')
})

?

Hi @un-versed

Thanks for your help. The issue here is to sort the companies by the company card [short_name] short name while sending the company info.