javascript - TypeORM query all records from today - Stack Overflow

admin管理员组

文章数量:1023676

I am trying to query all records from today but I get nothing, so My question is: How to query with date validations in TypeORM?

My code:

all = await connection
                .createQueryBuilder(Earnings, 't0')
                .addSelect('t3.id', 't3_id')
                .addSelect('t3.UID', 't3_UID')
                .addSelect('t3.name', 't3_name')
                .addSelect('t3.chips', 't3_chips')
                .addSelect('t3.tickets', 't3_tickets')
                .addSelect('t3.masteredLevel', 't3_masteredLevel')
                .addSelect('t2.UID', 't2_UID')
                .addSelect('t2.name', 't2_name')
                .addSelect('t2.rule', 't2_rule')
                .addSelect('t1.id', 't1_id')
                .innerJoin(Questions, 't1', 't0.questionID = t1.id')
                .innerJoin(Lessons, 't2', 't1.lessonUID = t2.UID')
                .innerJoin(Topics, 't3', 't2.topicUID = t3.UID')
                .where('t0.challenge = 1')
                .andWhere('t0.createdAt = :today')
                .andWhere('t0.userID = :userID')
                .setParameters({ userID: user.id })
                .setParameters({ today: moment() })
                .getRawMany()

I am trying to query all records from today but I get nothing, so My question is: How to query with date validations in TypeORM?

My code:

all = await connection
                .createQueryBuilder(Earnings, 't0')
                .addSelect('t3.id', 't3_id')
                .addSelect('t3.UID', 't3_UID')
                .addSelect('t3.name', 't3_name')
                .addSelect('t3.chips', 't3_chips')
                .addSelect('t3.tickets', 't3_tickets')
                .addSelect('t3.masteredLevel', 't3_masteredLevel')
                .addSelect('t2.UID', 't2_UID')
                .addSelect('t2.name', 't2_name')
                .addSelect('t2.rule', 't2_rule')
                .addSelect('t1.id', 't1_id')
                .innerJoin(Questions, 't1', 't0.questionID = t1.id')
                .innerJoin(Lessons, 't2', 't1.lessonUID = t2.UID')
                .innerJoin(Topics, 't3', 't2.topicUID = t3.UID')
                .where('t0.challenge = 1')
                .andWhere('t0.createdAt = :today')
                .andWhere('t0.userID = :userID')
                .setParameters({ userID: user.id })
                .setParameters({ today: moment() })
                .getRawMany()

• javascript
• mysql
• typeorm

Share Improve this question Follow asked Feb 25, 2021 at 16:04 I'm not humanI'm not human 55411 gold badge1111 silver badges3131 bronze badges

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 4

Your .andWhere('t0.createdAt = :today') only selects rows created at the instant of today parameter. You have set this parameter as moment(), which is not a date.

Since we can safely assume no rows will be created in the future, your simplest solution here is: .andWhere('t0.createdAt >= :today'), which selects rows created AFTER 00:00 today.

You can bine the addWhere and setParameters into one:

.andWhere('t0.createdAt >= :today', { today: moment().toDate() }) // note moment.toDate()

Alternatively use the MySql CURDATE() function to get the current date:

.andWhere('t0.createdAt >= CURDATE()')

When you test this, I remend that you turn on TypeOrm full logging so you can see the actual generated SQL and you will be able to quickly solve any problems. See TypeOrm logging.

For TypeORM + PSQL

Sort items by today's date

.andWhere('t0.createdAt >= CURRENT_DATE')

I am trying to query all records from today but I get nothing, so My question is: How to query with date validations in TypeORM?

My code:

all = await connection
                .createQueryBuilder(Earnings, 't0')
                .addSelect('t3.id', 't3_id')
                .addSelect('t3.UID', 't3_UID')
                .addSelect('t3.name', 't3_name')
                .addSelect('t3.chips', 't3_chips')
                .addSelect('t3.tickets', 't3_tickets')
                .addSelect('t3.masteredLevel', 't3_masteredLevel')
                .addSelect('t2.UID', 't2_UID')
                .addSelect('t2.name', 't2_name')
                .addSelect('t2.rule', 't2_rule')
                .addSelect('t1.id', 't1_id')
                .innerJoin(Questions, 't1', 't0.questionID = t1.id')
                .innerJoin(Lessons, 't2', 't1.lessonUID = t2.UID')
                .innerJoin(Topics, 't3', 't2.topicUID = t3.UID')
                .where('t0.challenge = 1')
                .andWhere('t0.createdAt = :today')
                .andWhere('t0.userID = :userID')
                .setParameters({ userID: user.id })
                .setParameters({ today: moment() })
                .getRawMany()

I am trying to query all records from today but I get nothing, so My question is: How to query with date validations in TypeORM?

My code:

all = await connection
                .createQueryBuilder(Earnings, 't0')
                .addSelect('t3.id', 't3_id')
                .addSelect('t3.UID', 't3_UID')
                .addSelect('t3.name', 't3_name')
                .addSelect('t3.chips', 't3_chips')
                .addSelect('t3.tickets', 't3_tickets')
                .addSelect('t3.masteredLevel', 't3_masteredLevel')
                .addSelect('t2.UID', 't2_UID')
                .addSelect('t2.name', 't2_name')
                .addSelect('t2.rule', 't2_rule')
                .addSelect('t1.id', 't1_id')
                .innerJoin(Questions, 't1', 't0.questionID = t1.id')
                .innerJoin(Lessons, 't2', 't1.lessonUID = t2.UID')
                .innerJoin(Topics, 't3', 't2.topicUID = t3.UID')
                .where('t0.challenge = 1')
                .andWhere('t0.createdAt = :today')
                .andWhere('t0.userID = :userID')
                .setParameters({ userID: user.id })
                .setParameters({ today: moment() })
                .getRawMany()

• javascript
• mysql
• typeorm

Share Improve this question Follow asked Feb 25, 2021 at 16:04 I'm not humanI'm not human 55411 gold badge1111 silver badges3131 bronze badges

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 4

Your .andWhere('t0.createdAt = :today') only selects rows created at the instant of today parameter. You have set this parameter as moment(), which is not a date.

Since we can safely assume no rows will be created in the future, your simplest solution here is: .andWhere('t0.createdAt >= :today'), which selects rows created AFTER 00:00 today.

You can bine the addWhere and setParameters into one:

.andWhere('t0.createdAt >= :today', { today: moment().toDate() }) // note moment.toDate()

Alternatively use the MySql CURDATE() function to get the current date:

.andWhere('t0.createdAt >= CURDATE()')

When you test this, I remend that you turn on TypeOrm full logging so you can see the actual generated SQL and you will be able to quickly solve any problems. See TypeOrm logging.

For TypeORM + PSQL

Sort items by today's date

.andWhere('t0.createdAt >= CURRENT_DATE')

本文标签： javascriptTypeORM query all records from todayStack Overflow