Query method returns empty array while count returns correct number

[Brambo27]

Hi, I'm facing an issue with the query method (and subsequently the entire graphql endpoint). I'm expecting this.orderService.query({}) to return all records but it returns empty array. Using this.orderService.count({}) does return correct number of records.

I was having the same issue with just typescript but that was fixed by adding the table name 'orders' into createQueryBuilder like so: this.orderRepository.createQueryBuilder('orders').getMany().then((orders) => console.log(orders));

Observed Behavior: this.orderService.query({}) returns an empty array []. this.orderService.count({}) returns 12.

Expected Behavior: this.orderService.query({}) should return an array of Order entities matching the number of records returned by the count.

Additional Context: Direct queries using TypeORM's repository return the correct data. Ensured TypeORM logging shows correct queries being executed, but nestjs-query appears not to map results correctly.

I'm having a hard time debugging this issue, as I'm not sure where to start to gain any insight on what is going on under the hood. If someone could point me in the right direction or help me identify what I'm doing wrong I would be grateful.

//app.service.ts
import { Inject, Injectable } from '@nestjs/common';
import { Order } from './entities/order.entity';
import { QueryService, InjectQueryService} from '@ptc-org/nestjs-query-core';

@Injectable()
export class AppService {
  constructor(
    @InjectQueryService(Order) 
    readonly orderService: QueryService<Order>,
  ) {}

  test(): void {
    this.orderService.query({}).then((orders) => console.log(orders)); // output: []
    this.orderService.count({}).then((count) => console.log(count)); // output: 12
  }
}

//order.entity.ts
import {
  Entity,
  PrimaryGeneratedColumn,
  Column,
  CreateDateColumn,
  UpdateDateColumn,
} from 'typeorm';
import { ObjectType, ID } from '@nestjs/graphql';
import { FilterableField, IDField } from '@ptc-org/nestjs-query-graphql';

@ObjectType('Order')
@Entity('orders')
export class Order {
  @IDField(() => ID)
  @PrimaryGeneratedColumn('uuid')
  uuid: string;

  @Column({ name: 'client_uuid', type: 'varchar' })
  clientUuid: string;

  @Column({ type: 'varchar' })
  currency: string;

  @Column({ type: 'varchar' })
  note: string;

  @Column({ type: 'varchar' })
  incoterms: string;

  @Column({ name: 'special_details', type: 'varchar' })
  specialDetails: string;

  @Column({ name: 'packing_instructions', type: 'varchar' })
  packingInstructions: string;

  @Column({ name: 'special_instructions', type: 'varchar' })
  specialInstructions: string;

  @Column({ type: 'varchar' })
  reference: string;

  @Column({ name: 'source_identifier', type: 'varchar' })
  sourceIdentifier: string;

  @Column({ name: 'source_name', type: 'varchar' })
  sourceName: string;

  @Column({ name: 'source_url', type: 'varchar' })
  sourceUrl: string;

  @Column({ name: 'subtotal_price', type: 'double precision' })
  subtotalPrice: number;

  @Column({ type: 'varchar' })
  status: string;

  @Column({ name: 'tax_lines', type: 'varchar', length: 65535 })
  taxLines: string;

  @Column({ name: 'total_discounts', type: 'double precision' })
  totalDiscounts: number;

  @Column({ name: 'total_order_lines_price', type: 'double precision' })
  totalOrderLinesPrice: number;

  @Column({ name: 'shipping_price', type: 'double precision' })
  shippingPrice: number;

  @Column({ name: 'total_price', type: 'double precision' })
  totalPrice: number;

  @Column({ name: 'total_tax', type: 'double precision' })
  totalTax: number;

  @Column({ name: 'billing_address', type: 'varchar', length: 65535 })
  billingAddress: string;

  @Column({ name: 'shipping_address', type: 'varchar', length: 65535 })
  shippingAddress: string;

  @CreateDateColumn({ name: 'created_at', type: 'timestamp' })
  createdAt: Date;

  @UpdateDateColumn({ name: 'updated_at', type: 'timestamp' })
  updatedAt: Date;

  @Column({ name: 'shipping_preference', type: 'varchar' })
  shippingPreference: string;
}

Table Schema:

CREATE TABLE orders (
  uuid character varying(256),
  client_uuid character varying(256),
  currency character varying(256),
  note character varying(256),
  incoterms character varying(256),
  special_details character varying(256),
  packing_instructions character varying(256),
  special_instructions character varying(256),
  reference character varying(256),
  source_identifier character varying(256),
  source_name character varying(256),
  source_url character varying(256),
  subtotal_price double precision,
  status character varying(256),
  tax_lines character varying(65535),
  total_discounts double precision,
  total_order_lines_price double precision,
  shipping_price double precision,
  total_price double precision,
  total_tax double precision,
  billing_address character varying(65535),
  shipping_address character varying(65535),
  created_at timestamp without time zone,
  updated_at timestamp without time zone,
  shipping_preference character varying(256)
);

//reporting.module.ts
import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { join } from 'path';
import { ApolloDriver } from '@nestjs/apollo';
import { GraphQLModule } from '@nestjs/graphql';
import { OrderModule } from './order.module';
import { Order } from './entities/order.entity';
import { dataSourceOptionsReporting } from './database/typeorm_reporting';

@Module({
  imports: [
    GraphQLModule.forRoot({
      driver: ApolloDriver,
      autoSchemaFile: join(process.cwd(), 'src/schema.gql'),
    }),
    TypeOrmModule.forRoot({
      ...dataSourceOptionsReporting,
      entities: [Order],
      synchronize: false,
    }),
    OrderModule]
})
export class ReportingModule {}

//order.module.ts
import { Module } from '@nestjs/common';
import { NestjsQueryGraphQLModule } from '@ptc-org/nestjs-query-graphql';
import { NestjsQueryTypeOrmModule } from '@ptc-org/nestjs-query-typeorm';
import { Order } from './entities/order.entity';
import { AppController } from './app.controller';
import { AppService } from './app.service';

@Module({
  imports: [
    NestjsQueryGraphQLModule.forFeature({
      imports: [NestjsQueryTypeOrmModule.forFeature([Order])],
      resolvers: [
        {
          EntityClass: Order,
          DTOClass: Order,
          enableAggregate: true,
        },
      ],
    }),
  ],
  providers: [AppService],
  controllers: [AppController],
})
export class OrderModule {}

[TriPSs]

Intresting, could you create a repo?

[Brambo27]

Yes, here you go: https://github.com/Brambo27/reporting Let me know if you need anything else, thanks.

[TriPSs]

Could you also add a small seed etc/database? So I can just run the docker-compose up and start the API to test? Then I can take a quick look to see without needing to first setup database and insert data etc.

[Brambo27]

Yes of course.

Just as I was setting up the repo, I figured I'd check if the issue was the database just to be sure. We're actually using an aws redshift database but we're connecting with psql. Just now I tried to see if it worked with just a normal psql database and yes that all works as expected, so I guess that's the issue.

I would still love to get this working with redshift but I don't think I can provide you with a redshift test database. Do you have any ideas?

Thanks already for your time.

[TriPSs]

Just to be sure:

1. the query runs just fine and returns it's data correctly?
2. when running the same query in Postgres, does the response look different?

[Brambo27]

For both the redshift and Postgres db the query that gets executed is the same, I'm not sure if the data is returned correctly, I don't know how I can check this in typeorm or nestjs-query. This is the query that runs: SELECT "Order"."uuid" AS "Order_uuid", "Order"."client_uuid" AS "Order_client_uuid", "Order"."currency" AS "Order_currency", "Order"."note" AS "Order_note", "Order"."incoterms" AS "Order_incoterms", "Order"."special_details" AS "Order_special_details", "Order"."packing_instructions" AS "Order_packing_instructions", "Order"."special_instructions" AS "Order_special_instructions", "Order"."reference" AS "Order_reference", "Order"."source_identifier" AS "Order_source_identifier", "Order"."source_name" AS "Order_source_name", "Order"."source_url" AS "Order_source_url", "Order"."subtotal_price" AS "Order_subtotal_price", "Order"."status" AS "Order_status", "Order"."tax_lines" AS "Order_tax_lines", "Order"."total_discounts" AS "Order_total_discounts", "Order"."total_order_lines_price" AS "Order_total_order_lines_price", "Order"."shipping_price" AS "Order_shipping_price", "Order"."total_price" AS "Order_total_price", "Order"."total_tax" AS "Order_total_tax", "Order"."billing_address" AS "Order_billing_address", "Order"."shipping_address" AS "Order_shipping_address", "Order"."created_at" AS "Order_created_at", "Order"."updated_at" AS "Order_updated_at", "Order"."shipping_preference" AS "Order_shipping_preference" FROM "orders"

Running the same query via psql in the terminal for both Redshift and Postgres databases there is one difference, the redshift seems to ignore the capitalisation of the AS command: Redshift: order_uuid | order_client_uuid | order_currency | order_note | order_incoterms | ... Postgres: Order_uuid | Order_client_uuid | Order_currency | Order_note | Order_incoterms | ...

I'm guessing that makes it so typeorm or nestjs-query can't map it to an entity? I will see if i can set enable_case_sensitive_identifier on the redshift and see if that fixes it.

[Brambo27]

Confirmed setting enable_case_sensitive_identifier to true on redshift made everything behave like expected. Thank you for pointing me in the right direction and for the awesome library :)