why I can't select column others than id, created_at and updated_at on joined table?

[tsulatsitamim]

this is the code I use

this is the result

if I change select to users.* it only show id, created_at, updated_at

[mybigman]

I just ran a quick query and works as expected?

const order = Database.from('db.customer_order as o')
      .leftJoin('db.customer_account as ca', 'o.parent_id', '=', 'ca.object_id')
      .select('o.order_id', 'ca.object_name')
      .where('order_id', 6969)

[tsulatsitamim]

Turn out, I should use query builder instead of model. or serialize extra object

https://docs.adonisjs.com/guides/models/serializing-models#serializing-extras-object

[tsulatsitamim]

https://docs.adonisjs.com/guides/models/serializing-models#serializing-extras-object

[RomainLanz]

Hey @tsulatsitamim! 👋🏻

Is there any reason you are not using relation instead of manually joining tables?

[tsulatsitamim]

I need to display the data on datatable, which will send property path to api. If I use relation, it will send input { sort: {field: users.name, sort: 'asc'} } and I still need join to sort by related model. If I join the table i can pass sort field input to parameter of orderBy query

[tsulatsitamim]

here are my simple version of my controller

public async index({ authUser, request }: HttpContextContract) {
    const query = ComprehensiveExam.query({ connection: authUser.db })
      .join('users', 'user_id', '=', 'users.id')
      .select(
        `${ComprehensiveExam.table}.id`,
        'users.name as user_name',
        'users.id_number as user_id_number',
        'kaprodi_submission_approval'
      )

    if (request.input('search')) {
      query.where((whereQuery) => {
        ;['users.name', 'users.id_number', 'exam_date'].forEach((column) =>
          whereQuery.orWhere(column, 'LIKE', `%${request.input('search')}%`)
        )
      })
    }

    if (request.input('sort')) {
      query.orderBy(request.input('sort.field'), request.input('sort.sort'))
    }

    if (request.input('pagination')) {
      return query.paginate(request.input('pagination.page'), request.input('pagination.perpage'))
    }

    return query
  }