Prisma Query
Hello I'm trying to create a search logic using nest js and prisma,
async searchConsumer(
prisma: Prisma.TransactionClient,
query: SearchAccountQuery,
page: number,
pageSize: number,
) {
try {
console.log('Rewarding Transactions Where:', {
AND: [
query.fromAmount !== undefined
? { amount: { gte: query.fromAmount } }
: {},
query.toAmount !== undefined
? { amount: { lte: query.toAmount } }
: {},
query.fromPoints !== undefined
? { points: { gte: query.fromPoints } }
: {},
query.toPoints !== undefined
? { points: { lte: query.toPoints } }
: {},
],
});
const consumers = await prisma.account.findMany({
where: {
AND: { account_type: 'consumer' },
OR: [
{ name: { contains: query.name, mode: 'insensitive' } },
{ email: { contains: query.email, mode: 'insensitive' } },
{ phone_number: { contains: query.phone_number } },
{ id: { equals: query.id } },
],
},
include: {
rewarding_transactions: {
where: {
AND: [
query.fromAmount !== undefined
? { amount: { gte: query.fromAmount } }
: {},
query.toAmount !== undefined
? { amount: { lte: query.toAmount } }
: {},
query.fromPoints !== undefined
? { points: { gte: query.fromPoints } }
: {},
query.toPoints !== undefined
? { points: { lte: query.toPoints } }
: {},
],
},
select: {
amount: true,
points: true,
},
},
},
...(page && {
...(pageSize && {
skip: Number(pageSize) * Number(page - 1),
take: Number(pageSize),
}),
}),
});
return {
totalCount: consumers.length,
data: consumers,
};
} catch (error) {
this.logger.error(error);
throw new BadRequestException(error);
}
}
But When I send a from and to amount or points I didn't get anything.1 Reply
import { ApiProperty } from '@nestjs/swagger';
import { Prisma } from '@prisma/client';
import { Transform } from 'class-transformer';
import {
IsOptional,
IsInt,
Min,
IsString,
IsEnum,
IsNotEmpty,
} from 'class-validator';
class PaginationParams {
@ApiProperty({
required: false,
description: 'Page number',
type: Number,
example: 1,
})
@IsOptional()
@Transform(({ value }) => parseInt(value))
@IsInt()
@Min(1)
page?: number;
@ApiProperty({
required: false,
description: 'Number of items per page',
type: Number,
example: 10,
})
@IsOptional()
@Transform(({ value }) => parseInt(value))
@IsInt()
@Min(1)
pageSize?: number;
}
export class SearchAccountQuery
extends PaginationParams
implements Prisma.AccountWhereInput
{
@ApiProperty({
required: false,
})
@IsOptional()
@IsString()
name?: string;
@ApiProperty({
required: false,
})
@IsOptional()
@IsString()
phone_number?: string;
@ApiProperty({
required: false,
})
@IsOptional()
@IsString()
email?: string;
@ApiProperty({
required: false,
type: Number,
})
@IsOptional()
@IsInt()
@Min(1)
@Transform(({ value }) => parseInt(value))
id?: number;
@ApiProperty({
required: false,
type: Number,
example: 0,
})
@IsOptional()
@Transform(({ value }) => parseInt(value))
@IsInt()
@Min(0)
fromAmount?: number;
@ApiProperty({
required: false,
type: Number,
example: 100,
})
@IsOptional()
@Transform(({ value }) => parseInt(value))
@IsInt()
@Min(0)
toAmount?: number;
@ApiProperty({
required: false,
type: Number,
example: 0,
})
@IsOptional()
@Transform(({ value }) => parseInt(value))
@IsInt()
@Min(0)
fromPoints?: number;
@ApiProperty({
required: false,
type: Number,
example: 100,
})
@IsOptional()
@Transform(({ value }) => parseInt(value))
@IsInt()
@Min(0)
toPoints?: number;
}
MY models
model rewarding_transactions {
id Int @id @default(autoincrement())
owner_id String?
clone_id String?
debit Int?
credit Int?
cashback_debit String?
cashback_credit String?
points Int?
amount Int?
epoints_type epointsTransactionType?
trx_date DateTime?
merchant_logo String?
merchant_name String?
created_at DateTime @default(now())
updated_at DateTime?
is_deleted Boolean @default(false)
earning_metric_id Int?
earning_metric earning_metric? @relation(fields: [earning_metric_id], references: [id])
system_config_id Int?
system_config systemConfigs? @relation(fields: [system_config_id], references: [id])
account Account @relation(fields: [account_id], references: [id])
account_id Int
}
model Account {
id Int @id @default(autoincrement())
SystemConfigID Int?
facebook_id String?
google_id String?
apple_id String?
biometric_id String?
name String
email String?
phone_number String?
country String?
street_address String?
city String?
state String?
password String?
dob DateTime?
gender String?
national_id String?
verified Boolean? @default(false)
confirmed Boolean? @default(false)
confirm_token String?
reset_password Boolean?
upload_id Int?
upload upload? @relation(fields: [upload_id], references: [id])
is_deleted Boolean @default(false)
created_at DateTime @default(now())
updated_at DateTime?
account_type account_type
admins admin[]
devices device[]
tokens token[]
userAddress userAddress[]
rewarding_transactions rewarding_transactions[]
// ePoints Data
card_holder_id String?
card_details Json?
ep_token String?
pin String?
systemConfigs systemConfigs? @relation(fields: [SystemConfigID], references: [id])
notifications notification[]
payment_cards paymentCard[]
payments payment[]
referralCode referralCode[]
calculation_module_admin calculation_module_admin[]
product product[]
segment segment[]
tier tier[]
type type[]
earning_metric earning_metric[]
@@unique([email, SystemConfigID])
@@unique([google_id, SystemConfigID])
@@unique([facebook_id, SystemConfigID])
@@unique([apple_id, SystemConfigID])
@@unique([phone_number, SystemConfigID])
@@unique([national_id, SystemConfigID])
}