Can't make query: select() and with()


#1

Hi again,

I’m trying to make this query work:

const userdata = await User.query()
  .where('id', auth.user.id)
  .select('id, username, last_visit')
  .with('group')
  .first()

It works without the .select() method:

  • When there is no select, all fields in the users database are loaded and the relationship with posts is also loaded.
  • But when I put the select in the query, the relation stops running (group returns undefined).

My question is: How can I use select (for the users table) and with (for the group relation) at the same time?


#2

Hello guys?


#3

try :

const userdata = await User.query()
  .where('id', auth.user.id)
  .select('id', 'username', 'last_visit')
  .with('group')
  .first()

#4

You’re selecting one column whose name is ‘id, username, last_visit’.

Since there are no such columns in your database, you get undefined.

You need to write your select statement like wanghao8080 above.


#6

I tried this, but it continues with the error…

const users = await User.query()
  .select('id', 'username', 'is_active', 'created_at')
  .with('groups', (builder) => builder.select('id', 'name').limit(1))
  .with('position', (builder) => builder.select('id', 'name'))
  .paginate(page, 40)

The users.data[0] looks like this:

{
  "id": 1,
  "username": "Luiz",
  "is_active": 1,
  "created_at": "2018-06-01 20:58:21",
  "groups": [
    {
        "id": 1,
        "name": "Administradores",
        "pivot": {
          "group_id": 1,
          "user_id": 1
        }
    }
  ],
  "position": null
}

The interesting thing is that with belongsToMany works perfectly, but withbelongsTo no. :thinking:

Note that if I remove the 2nd line of the query (select):

const users = await User.query()
  .with('groups', (builder) => builder.select('id', 'name').limit(1))
  .with('position', (builder) => builder.select('id', 'name'))
  .paginate(page, 40)

Works perfectly.
But I still need to select the users table data.

Thanks if you could help.


My User model:

'use strict'

const Model = use('Model')

class User extends Model {
  static boot () {
    super.boot()

    this.addHook('beforeCreate', 'UserHook.hashPassword')
  }

  /**
   * USERS <-> GROUPS
   */
  groups () {
    return this
      .belongsToMany('App/Models/Group')
      .pivotTable('pivot_group_user')
  }

  /**
   * USERS --> POSITIONS
   */
  position () {
    return this.belongsTo('App/Models/Position')
  }

  /**
   * USERS --> TOKENS
   */
  tokens () {
    return this.hasMany('App/Models/Token')
  }
}

module.exports = User

#7

Any news here?