mysql search child & parent table fields with sequelize in nodejs

Question

In my Node application we have used Sequelize to connect with mysql Have two table User & UserOption

User table have following fields

user_id(pk)
user_email

UserOption table have following fields

option_id(pk)
user_id(fk)
first_name

I need to list all user by search text in user_email & first_name Is there any option to search both parent & child table fields in Sequelize?

UPDATE

User table

user_id  user_email
1          [email protected]
2          [email protected]
3          [email protected]

UserOption table

option_id   user_id  first_name
1             1        jhon
2             2        smith
3             3        david

If I search for "jhon", the result will be both user with id 1 and 2

piotrbienias · Accepted Answer · 2017-02-15 10:05:52Z

1

You need to include model UserOption in lookup on model User. This generates a JOIN clause with condition UserOption.user_id = User.user_id, as well as adds specified WHERE clause to perform text lookup on user_email and first_name columns of both tables

User.findAll({
    where: {
        user_email: { $like: '%text%' }
    },
    include: [
        {
            model: UserOption,
            where: {
                first_name: { $like: '%text%' }
            },
            attributes: []
        }
    ]
}).then((users) => {
    // result
});

EDIT

Basing on your updated question, I think that you can try using sequelize.literal method, but, according to the documentation:

Creates a object representing a literal, i.e. something that will not be escaped.

So it is necessary to escape desired values by yourself

let escValue = sequelize.escape(lookupText);

User.findAll({
    where: {
        $or: [
            { email: { $like: '%' + lookupText + '%' },
            { id: { $in: sequelize.literal(`(SELECT uo.user_id FROM user_options uo WHERE uo.first_name LIKE CONCAT('%', ${escValue}, '%'))`) }
        ]
    }
}).then((users) => {
    // result...
});

It would generate a query which selects users from users table where email LIKE '%text%' or where users.id is in specified query result.

I am very curious if this satisfies your needs, waiting for feedback.

edited Feb 15, 2017 at 10:05

answered Feb 14, 2017 at 17:19

piotrbienias

7,4211 gold badge32 silver badges33 bronze badges

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

1 Comment

Kepler Over a year ago

I want the results for both user_email & first_name. ie if either user_email or first_name match the search criteria,then get results

Collectives™ on Stack Overflow

mysql search child & parent table fields with sequelize in nodejs

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related