QueryBuilder join fails with "Metadata for entity undefined not found"

[isanosian]

Trying to use join on two entities with QueryBuilder makes my app crash.

Would appreciate any help trying to solve this.

Stack trace

MetadataError: Metadata for entity undefined not found
    at Function.missingMetadata (/srv/node_modules/@mikro-orm/core/errors.js:178:16)
    at MetadataStorage.get (/srv/node_modules/@mikro-orm/core/metadata/MetadataStorage.js:54:42)
    at QueryBuilder.joinReference (/srv/node_modules/@mikro-orm/knex/query/QueryBuilder.js:596:36)
    at QueryBuilder.join (/srv/node_modules/@mikro-orm/knex/query/QueryBuilder.js:126:14)
    at /srv/app/controllers/offer.ts:149:33
    at Generator.next (<anonymous>)
    at fulfilled (/srv/app/controllers/offer.ts:5:58) {
  entity: undefined
}

To Reproduce

# offer.ts

const qb = em.createQueryBuilder(Offer);
const offers = await qb.join('o.campaigns', 'c')
    .select(['*'])
    .getResultList();

# model/campaign.ts
@Entity({collection: 'campaign'})
export class Campaign {
    @PrimaryKey()
    id!: number;

    @ManyToOne(
        () => Offer,
        {joinColumn: 'offer_id', ref: true},
    )
    offer!: Ref<Offer>;
}

# model/offer.ts

@Entity({collection: 'offer'})
export class Offer {
    @PrimaryKey()
    id!: number;

    @OneToMany(
        () => Campaign,
        campaign => campaign.offer,
    )
    campaigns = new Collection<Campaign>(this);
}

# model/index.ts

export {Campaign} from './campaign';
export {Offer} from './offer';

# orm.ts

import * as Entities from '../model';


const ormConfig = defineConfig({
    debug: true,
    cache: {enabled: false},
    logger: console.log.bind(console),

    host: config.mysql.hostname,
    user: config.mysql.username,
    password: config.mysql.password,
    port: config.mysql.port,
    dbName: config.mysql.database,

    entities: Object.values(Entities),
    namingStrategy: EntityCaseNamingStrategy,
});

Expected behavior
Query does not fail and actually returns results.

Additional context
I also could not update to mikro-orm@5.7 since it requests an old version of mysql2 as a peer dependency.

Solved in #4353 (comment)

npm WARN ERESOLVE overriding peer dependency
npm WARN While resolving: @mikro-orm/knex@5.7.6
npm WARN Found: mysql2@3.3.1
npm WARN node_modules/@mikro-orm/mysql/node_modules/mysql2
npm WARN   mysql2@"3.3.1" from @mikro-orm/mysql@5.7.6
npm WARN   node_modules/@mikro-orm/mysql
npm WARN     @mikro-orm/mysql@"~5.7.6" from the root project
npm WARN     1 more (@mikro-orm/core)
npm WARN
npm WARN Could not resolve dependency:
npm WARN peerOptional mysql2@"^2.1.0" from @mikro-orm/knex@5.7.6
npm WARN node_modules/@mikro-orm/mysql/node_modules/@mikro-orm/knex
npm WARN   @mikro-orm/knex@"~5.7.6" from @mikro-orm/mysql@5.7.6
npm WARN   node_modules/@mikro-orm/mysql
npm WARN
npm WARN Conflicting peer dependency: mysql2@2.3.3
npm WARN node_modules/mysql2
npm WARN   peerOptional mysql2@"^2.1.0" from @mikro-orm/knex@5.7.6
npm WARN   node_modules/@mikro-orm/mysql/node_modules/@mikro-orm/knex
npm WARN     @mikro-orm/knex@"~5.7.6" from @mikro-orm/mysql@5.7.6
npm WARN     node_modules/@mikro-orm/mysql

Versions

Dependency	Version
node	v18.16.0
typescript	4.9.5
mikro-orm	5.7.6
your-driver	mysql2@3.3.1

[B4nan]

I also could not update to mikro-orm@5.7 since it requests an old version of mysql2 as a peer dependency.

Not sure what you mean by that, you shouldn't be installing mysql2 yourself, it is a direct dependency of @mikro-orm/mysql, not a peer.

[isanosian]

@B4nan thanks, removing mysql2 from package.json helped updating mikro-orm to 5.7.6. The primary issue remains, I've updated the versions and the trace above.

Just for the record, this seems to be the line referenced in that npm log that was complaining about the peer dependency conflict: https://github.com/mikro-orm/mikro-orm/blob/v5.7.6/packages/knex/package.json#L75

[B4nan]

Oh right, that's indeed wrong, thanks for the hint.

[B4nan]

Hmm, this is not really a bug, I will add a validation for this - the problem is your QB instance uses default alias, which is o0, not o, so you are trying to join on an undefined alias.

Both of this will work:

const qb = orm.em.createQueryBuilder(Offer);
const offers = await qb.join('o0.campaigns', 'c').select(['*']).getResultList();

const qb = orm.em.createQueryBuilder(Offer, 'o');
const offers = await qb.join('o.campaigns', 'c').select(['*']).getResultList();

Or just skip the alias, you dont need to provide that for root entity:

const qb = orm.em.createQueryBuilder(Offer);
const offers = await qb.join('campaigns', 'c').select(['*']).getResultList();

[isanosian]

@B4nan Confirmed all 3 solutions, thank you. Really appreciate your help.

[isanosian]

@B4nan would you please take a look, should I start a separate thread for this?

const qb = em.createQueryBuilder(Offer, 'o');
const offers = await qb.join('o.campaigns', 'c')
    .select([
        '*',
        qb.raw('COUNT(DISTINCT c.id) AS campaigns_number')
    ])
    .where(filter)
    .groupBy('o.id')
    .having('campaigns_number > 1')
    .offset(0)
    .limit(10)
    .getResultList();

The above crashes with:

Error: select `o`.*, COUNT(DISTINCT c.id) AS campaigns_number from `offer` as `o` inner join `campaign` as `c` on `o`.`id` = `c`.`offer_id` where `o`.`id` in (select `o`.`id` from (select `o`.`id` from `offer` as `o` inner join `campaign` as `c` on `o`.`id` = `c`.`offer_id` group by `o`.`id` having (campaigns_number > 1) limit 10) as `o`) group by `o`.`id` having (campaigns_number > 1) - Unknown column 'campaigns_number' in 'having clause'
    at Packet.asError (/srv/node_modules/mysql2/lib/packets/packet.js:728:17)
    at Query.execute (/srv/node_modules/mysql2/lib/commands/command.js:29:26)
    at Connection.handlePacket (/srv/node_modules/mysql2/lib/connection.js:456:32)
    at PacketParser.onPacket (/srv/node_modules/mysql2/lib/connection.js:85:12)
    at PacketParser.executeStart (/srv/node_modules/mysql2/lib/packet_parser.js:75:16)
    at Socket.<anonymous> (/srv/node_modules/mysql2/lib/connection.js:92:25)
    at Socket.emit (node:events:513:28)
    at Socket.emit (node:domain:489:12)
    at addChunk (node:internal/streams/readable:324:12)
    at readableAddChunk (node:internal/streams/readable:297:9) {
  code: 'ER_BAD_FIELD_ERROR',
  errno: 1054,
  sqlState: '42S22',
  sqlMessage: "Unknown column 'campaigns_number' in 'having clause'",
  sql: 'select `o`.*, COUNT(DISTINCT c.id) AS campaigns_number from `offer` as `o` inner join `campaign` as `c` on `o`.`id` = `c`.`offer_id` where `o`.`id` in (select `o`.`id` from (select `o`.`id` from `offer` as `o` inner join `campaign` as `c` on `o`.`id` = `c`.`offer_id` group by `o`.`id` having (campaigns_number > 1) limit 10) as `o`) group by `o`.`id` having (campaigns_number > 1)'
}

The generated query indeed fails to execute in mysql console.

SELECT
    `o`.*,
    COUNT(DISTINCT c.id) AS campaigns_number
FROM
    `offer` AS `o`
    INNER JOIN `campaign` AS `c` ON `o`.`id` = `c`.`offer_id`
WHERE
    `o`.`id` in(
        SELECT
            `o`.`id` FROM (
                SELECT
                    `o`.`id` FROM `offer` AS `o`
                    INNER JOIN `campaign` AS `c` ON `o`.`id` = `c`.`offer_id`
                GROUP BY
                    `o`.`id`
                HAVING (campaigns_number > 1)
            LIMIT 10) AS `o`)
GROUP BY
    `o`.`id`
HAVING (campaigns_number > 1)

Query 1 ERROR: Unknown column 'campaigns_number' in 'having clause'

Below is the reference query that I'm trying to translate:

SELECT
    `o`.*,
    COUNT(DISTINCT c.id) AS campaigns_number
FROM
    `offer` AS `o`
    INNER JOIN `campaign` AS `c` ON `o`.`id` = `c`.`offer_id`
GROUP BY
    `o`.`id`
HAVING (campaigns_number > 1)

[B4nan]

Disable pagination via qb.setFlag(QueryFlag.DISABLE_PAGINATE).

[B4nan]

I guess it should be allowed only when you don't provide the grouping clause.

[isanosian]

@B4nan Works perfectly now. Thank you!