How to use of AND and OR operator in same query in sequelize?

Question

I execute the below query but i gives error. I want the result of my SQL query which i posted at end.

userServiceAppointmentModel.findAll({
        where: {
            technician_id: resultsFromAuthentication.technician_id,
            is_confirmed_by_user: 1,
            $or: {
                service_start_time: {
                    gte: curLocalDate
                },
                service_running_status: 1
            }
    },
        attributes: attributes
    }).complete(function (err, appointmentResponse) {
        if (err) {
            console.log(err);
        }


SELECT
    `id`, `technician_id`, `user_id`, `service_id`, `service_name`,
    `service_location_string`, `service_location_latitude`,
    `service_location_longitude`, `service_start_time`, `service_end_time`,
    `notes`, `total_cost`, `service_cost`, `is_confirmed_by_user`,
    `is_confirmed_by_technician`, `service_running_status`,
    `service_start_time_by_technician`,`service_complete_time_by_technician`
FROM `user_service_appointment` AS `user_service_appointment`
WHERE `user_service_appointment`.`technician_id`=154
AND `user_service_appointment`.`is_confirmed_by_user`=1
AND (`user_service_appointment`.`service_start_time` >='2015-02-26 01:07'
    OR `user_service_appointment`.`service_running_status`=1)

{ [Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'gte = '2015-02-26 01:07' AND user_service_appointment.$or 1' at line 1] [app-4 (out)] code: 'ER_PARSE_ERROR', [app-4 (out)] errno: 1064, [app-4 (out)] sqlState: '42000', [app-4 (out)] index: 0, [app-4 (out)] — Gurpinder
– Gurpinder, Commented Feb 26, 2015 at 8:46
WHERE user_service_appointment.technician_id=154 AND user_service_appointment.is_confirmed_by_user=1 AND user_service_appointment.$or gte = \'2015-02-26 01:07\' AND user_service_appointment.$or 1;' }, I think the problem in Where condition synatx — Gurpinder
– Gurpinder, Commented Feb 26, 2015 at 8:47
Joe all are working fine. The problem is i am unable to find the code to implement and or operator both in same query. If you see above the Where condition is not correct the $or operator is not correct. It should be (user_service_appointment.service_start_time >='2015-02-26 01:07' OR user_service_appointment.service_running_status=1) — Gurpinder
– Gurpinder, Commented Feb 26, 2015 at 9:21

dege · Accepted Answer · 2015-02-26 12:53:20Z

8

At least for the version 2.0.0 you could use Seuqlize.and and Sequelize.or

for your case

..
 where: {where: Sequelize.and(
    {technician_id: resultsFromAuthentication.technician_id},
    {is_confirmed_by_user: 1},
    Sequelize.or({
            service_start_time: {
                gte: curLocalDate
            }},
        {service_running_status: 1}
    )
)
..

edited Feb 26, 2015 at 12:53

answered Feb 26, 2015 at 11:09

dege

2,9592 gold badges31 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

dege Over a year ago

Sorry didn't really tested. I edited the answer, but you probably need a Sequelize.and first.

Gurpinder Over a year ago

Yes i did but the issue is still there.

Gurpinder Over a year ago

Thanks a lot i resolved the syntax mistake. Really appreciate your work.

amaster Over a year ago

@Gurpinder you could have mentioned what syntax mistake it was that you were making

RYS · Accepted Answer · 2017-07-06 03:10:22Z

in new version try like this

                model.update(
                req.body,
                {

                    where: { task_id: req.params.task_id,
                        $and: {id: 11}
                        $gt: {end_date: myDate}
                    } }
            )
                .then(function () {
                res.status(200).json({"message":"done"})
                }
    )
    .catch(function (err) {

        })

For more detail see the Documentation

Here i want to mention some of them

$and: {a: 5}           // AND (a = 5)
$or: [{a: 5}, {a: 6}]  // (a = 5 OR a = 6)
$gt: 6,                // > 6
$gte: 6,               // >= 6
$lt: 10,               // < 10
$lte: 10,              // <= 10
$ne: 20,               // != 20
$eq: 3,                // = 3
$not: true,            // IS NOT TRUE
$between: [6, 10],     // BETWEEN 6 AND 10
$notBetween: [11, 15], // NOT BETWEEN 11 AND 15
$in: [1, 2],           // IN [1, 2]
$notIn: [1, 2],        // NOT IN [1, 2]
$like: '%hat',         // LIKE '%hat'
$notLike: '%hat'       // NOT LIKE '%hat'
$iLike: '%hat'         // ILIKE '%hat' (case insensitive) (PG only)
$notILike: '%hat'      // NOT ILIKE '%hat'  (PG only)
$like: { $any: ['cat', 'hat']}       // LIKE ANY ARRAY['cat', 'hat'] - also works for iLike and notLike
$overlap: [1, 2]       // && [1, 2] (PG array overlap operator)
$contains: [1, 2]      // @> [1, 2] (PG array contains operator)
$contained: [1, 2]     // <@ [1, 2] (PG array contained by operator)
$any: [2,3]            // ANY ARRAY[2, 3]::INTEGER (PG only)

dege · Accepted Answer · 2019-04-04 10:49:02Z

0

    userServiceAppointmentModel.findAll({where: Sequelize.and(
            {technician_id: resultsFromAuthentication.technician_id},
            {is_confirmed_by_user: 1},
            Sequelize.or({
                    service_start_time: {
                        gte: curLocalDate
                    }},
                {service_running_status: 1}
            )

    )
}).complete(function (err, appointmentResponse) {

edited Apr 4, 2019 at 10:49

dege

2,9592 gold badges31 silver badges36 bronze badges

answered Feb 26, 2015 at 12:30

Gurpinder

2813 gold badges5 silver badges12 bronze badges

Collectives™ on Stack Overflow

How to use of AND and OR operator in same query in sequelize?

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related