query with preload on ManyToMany relation, alwasy return empty array.

[deloz]

model Post and Tag have a many-to-many relationship.

When querying the list of posts, I'm unable to retrieve the tag data associated with the posts.

but, tags_count has the correct number. the MySQL query log is also correct.

// model: Post.ts
export default class Post extends BaseModel {
  @column({ isPrimary: true, consume: (v: string) => BigInt(v) })
  public id: bigint

  @manyToMany(() => Tag)
  public tags: ManyToMany<typeof Tag>
}

// model: Tag.ts
export default class Tag extends BaseModel {
  @column({ isPrimary: true, consume: (v: string) => BigInt(v) })
  public id: bigint

  @manyToMany(() => Post)
  public posts: ManyToMany<typeof Post>
}

// migration: posts table
export default class extends BaseSchema {
  protected tableName = 'posts'

  public async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.bigIncrements('id')

      /**
       * Uses timestamptz for PostgreSQL and DATETIME2 for MSSQL
       */
      table.timestamp('created_at', { useTz: true, precision: 6 })
      table.timestamp('updated_at', { useTz: true, precision: 6 })
    })
  }

  public async down() {
    this.schema.dropTable(this.tableName)
  }
}

// migration: tags table
export default class extends BaseSchema {
  protected tableName = 'tags'

  public async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.bigIncrements('id')
      table.string('name', 128).notNullable().unique()
    })
  }

  public async down() {
    this.schema.dropTable(this.tableName)
  }
}

// migration: post_tag table
export default class extends BaseSchema {
  protected tableName = 'post_tag'

  public async up() {
    this.schema.createTable(this.tableName, (table) => {
      table.bigInteger('post_id').unsigned().references('posts.id')
      table.bigInteger('tag_id').unsigned().references('tags.id')
      table.primary(['post_id', 'tag_id'])
    })
  }

  public async down() {
    this.schema.dropTable(this.tableName)
  }
}

// PostController.ts
export default class PostsController {
  public async index({ request, view }): Promise<Post[]> {
    const page: number = request.input('page', 1)
    const limit: number = 1
    const posts = await Post
      .query()
      .preload('tags')
      .withCount('tags')
      .orderBy('id', 'desc')
      .paginate(page, limit)

    posts.baseUrl('/posts')

    return await view.render('post/index', {
      posts: posts,
    })
  }
}

// view: post/index.edge
@each (post in posts)
        {{ inspect(post) }}
@end

inspect result :

{
  "modelOptions": {
    "connection": 'mysql',
    "profiler": {
      "appRoot": '/home/deloz/codes/nodejs/noblog',
      "logger": {
        "config": {
          "name": 'noblog',
          "enabled": true,
          "level": 'info',
          "prettyPrint": true
        },
        "pino": {
          "levels": [Object],
          "silent": [Function: noop],
          "trace": [Function: noop],
          "debug": [Function: noop],
          "info": [Function: LOG],
          "warn": [Function: LOG],
          "error": [Function: LOG],
          "fatal": [Function]
        }
      },
      "config": {
        "enabled": true,
        "whitelist": [],
        "blacklist": []
      }
    }
  },
  "modelTrx": undefined,
  "transactionListener": [Function: bound listener],
  "fillInvoked": false,
  "cachedGetters": {},
  "forceUpdate": false,
  "$columns": {},
  "$attributes": {
    "id": 85929485459263488n,
    "createdAt": {
      "ts": 1693056532000,
      "_zone": {},
      "loc": {
        "locale": 'en-US',
        "numberingSystem": null,
        "outputCalendar": null,
        "intl": 'en-US',
        "weekdaysCache": {
          "format": [Object],
          "standalone": [Object]
        },
        "monthsCache": {
          "format": [Object],
          "standalone": [Object]
        },
        "meridiemCache": null,
        "eraCache": {},
        "specifiedLocale": null,
        "fastNumbersCached": null
      },
      "invalid": null,
      "weekData": null,
      "c": {
        "year": 2023,
        "month": 8,
        "day": 26,
        "hour": 13,
        "minute": 28,
        "second": 52,
        "millisecond": 0
      },
      "o": -0,
      "isLuxonDateTime": true
    },
    "updatedAt": {
      "ts": 1693125353000,
      "_zone": {},
      "loc": {
        "locale": 'en-US',
        "numberingSystem": null,
        "outputCalendar": null,
        "intl": 'en-US',
        "weekdaysCache": {
          "format": [Object],
          "standalone": [Object]
        },
        "monthsCache": {
          "format": [Object],
          "standalone": [Object]
        },
        "meridiemCache": null,
        "eraCache": {},
        "specifiedLocale": null,
        "fastNumbersCached": null
      },
      "invalid": null,
      "weekData": null,
      "c": {
        "year": 2023,
        "month": 8,
        "day": 27,
        "hour": 8,
        "minute": 35,
        "second": 53,
        "millisecond": 0
      },
      "o": -0,
      "isLuxonDateTime": true
    }
  },
  "$original": {
    "id": 85929485459263488n,
    "createdAt": {
      "ts": 1693056532000,
      "_zone": {},
      "loc": {
        "locale": 'en-US',
        "numberingSystem": null,
        "outputCalendar": null,
        "intl": 'en-US',
        "weekdaysCache": {
          "format": [Object],
          "standalone": [Object]
        },
        "monthsCache": {
          "format": [Object],
          "standalone": [Object]
        },
        "meridiemCache": null,
        "eraCache": {},
        "specifiedLocale": null,
        "fastNumbersCached": null
      },
      "invalid": null,
      "weekData": null,
      "c": {
        "year": 2023,
        "month": 8,
        "day": 26,
        "hour": 13,
        "minute": 28,
        "second": 52,
        "millisecond": 0
      },
      "o": -0,
      "isLuxonDateTime": true
    },
    "updatedAt": {
      "ts": 1693125353000,
      "_zone": {},
      "loc": {
        "locale": 'en-US',
        "numberingSystem": null,
        "outputCalendar": null,
        "intl": 'en-US',
        "weekdaysCache": {
          "format": [Object],
          "standalone": [Object]
        },
        "monthsCache": {
          "format": [Object],
          "standalone": [Object]
        },
        "meridiemCache": null,
        "eraCache": {},
        "specifiedLocale": null,
        "fastNumbersCached": null
      },
      "invalid": null,
      "weekData": null,
      "c": {
        "year": 2023,
        "month": 8,
        "day": 27,
        "hour": 8,
        "minute": 35,
        "second": 53,
        "millisecond": 0
      },
      "o": -0,
      "isLuxonDateTime": true
    }
  },
  "$preloaded": {
    "tags": []   // this always empty array
  },
  "$extras": {
    "tags_count": 4   
  },
  "$sideloaded": {},
  "$isPersisted": true,
  "$isDeleted": false,
  "$isLocal": false
}

MySQL data:

# MySQL query log
2023-08-29T15:00:06.631749Z        17 Query     select count(*) as `total` from `posts`
2023-08-29T15:00:06.634955Z        17 Query     select `posts`.*, (select count(*) from `tags` inner join `post_tag` on `tags`.`id` = `post_tag`.`tag_id` where `posts`.`id` = `post_tag`.`post_id`) as `tags_count` from `posts` order by `id` desc limit 1
2023-08-29T15:00:06.637760Z        17 Query     select `tags`.*, `post_tag`.`post_id` as `pivot_post_id`, `post_tag`.`tag_id` as `pivot_tag_id` from `tags` inner join `post_tag` on `tags`.`id` = `post_tag`.`tag_id` where `post_tag`.`post_id` in ('85929485459263488')

[thetutlage]

Thanks for creating a detailed discussion. Can you please put this code inside a repo and share that with me? That will make it simpler for me to reproduce the same on my end

[deloz]

Thank you very much for your help despite your busy schedule. I have placed the source code at https://github.com/deloz/noblog, and the database backup file is in the root directory.

[deloz]

I've identified the issue.
Because I'm using the bigint data type, i need to configure supportBigNumbers and bigNumberStrings in the database connection settings.

// file: config/database.ts

const databaseConfig: DatabaseConfig = {

  connections: {
    mysql: {
      client: 'mysql2',
      connection: {
        host: Env.get('MYSQL_HOST'),
        port: Env.get('MYSQL_PORT'),
        user: Env.get('MYSQL_USER'),
        password: Env.get('MYSQL_PASSWORD', ''),
        database: Env.get('MYSQL_DB_NAME'),
        supportBigNumbers: true, // Needs to be set to true
        bigNumberStrings: true,  // Needs to be set to true
      },
    },
  },
}