/** * Builds query to get records where the value of the field contains the value. * Setting caseSensitive to true will cause the regex to be case insensitive */ const buildContainsQuery = function({ field, value, caseSensitive = false }) { // TODO: contains is not working as expected, like is for string matching - doublecheck this if (caseSensitive) { return { name: field, value: { [Op.like]: value } }; } else { return { name: field, value: { [Op.iLike]: value } }; } }
async searchCategoryAndProducts(req, res) { const { q } = req.query; const categories = await Category.findAndCountAll({ where: { name: { [Op.iLike]: `%${q}%` } }, }); const products = await Product.findAndCountAll({ where: { name: { [Op.iLike]: `%${q}%` } }, include: [Category.include()], }); return res.json({ categories, products }); }
Object.entries(cols).forEach((and) => { const [col, val] = and; const found = OWN_COLUMNS.find((column) => column === col); if (found) andColumns[col] = { [Op.iLike]: `%${val}%` }; INCLUDE.find((model) => { const foundModel = model.attributes.include.some( (column) => column === col ); if (foundModel) { model.where = { [col]: { [Op.iLike]: `%${val}%` } }; // #1 model.required = true; // #1 } }); });
describe('buildContainsQuery Tests', () => { test('Should return case sensitive match regex query', () => { const expectedResult = { name: 'foo', value: { [Op.like]: 'bar' } }; let observedResult = sqlQB.buildContainsQuery({ field: 'foo', value: 'bar', caseSensitive: true, }); expect(observedResult).toEqual(expectedResult); }); test('Should return case insensitive match regex query', () => { const expectedResult = { name: 'foo', value: { [Op.iLike]: 'bar' } }; let observedResult = sqlQB.buildContainsQuery({ field: 'foo', value: 'bar', }); expect(observedResult).toEqual(expectedResult); }); });
const getVideoIds = async (q, channel_id) => { const videoIds = (await db.VideoComment.findAll({ attributes: ['video.id'], include: [ { association: 'video', attributes: [], ...channel_id && { where: { channel_id }, }, }, ], where: { message: { [Op.iLike]: `%${q}%` } }, group: 'video.id', order: [[db.VideoComment.associations.video, 'published_at', 'DESC']], raw: true, })).map(({ id }) => id); return videoIds; }
const getTotalCount = async (q, channel_id) => { const count = await db.VideoComment.count({ col: ['video_id'], distinct: true, where: { message: { [Op.iLike]: `%${q}%` } }, ...channel_id && { include: [ { association: 'video', attributes: [], where: { channel_id }, }, ], }, }); return count; }
/** * Returns a list of all groups that partially match the given name. */ async function getList(name, pagination) { // Fetch all groups that match the name const groups = await Group.findAll({ where: name && { name: { [Op.iLike]: `%${sanitizeName(name)}%` } }, order: [ ['score', 'DESC'], ['id', 'ASC'] ], limit: pagination.limit, offset: pagination.offset }); // Fetch and attach member counts for each group const completeGroups = await attachMembersCount(groups.map(format)); return completeGroups; }
attributes: RESPONSE_FIELDS.CHANNEL, where: { ...name && { name: { [Op.iLike]: `%${name}%` } }, }, order: [[sort, order]],
async index(req, res) { const { page = 1, name = '' } = req.query; const LIMIT = 20; const deliveryman = await Deliveryman.findAndCountAll({ where: { name: { [Op.iLike]: `%${name}%` } }, attributes: ['id', 'name', 'email', 'avatar_id'], include: [ { model: File, as: 'avatar', attributes: ['name', 'path', 'url'] } ], order: ['id'], limit: LIMIT, offset: (page - 1) * LIMIT }); return res.json(deliveryman); }
async show(req, res) { const users = await User.findAll({ attributes: ['name', 'email'], where: { email: { [Op.iLike]: '%@gmail.com' }, }, include: [ { association: 'addresses', where: { street: 'Rua Campo Grande'} }, { association: 'techs', required: false, where: { name: { [Op.iLike]: 'React%', } } }, ] }); return res.json(users); }
async index(req, res) { const { page = 0, filter = '' } = req.query; const { count } = await Recipient.findAndCountAll({ where: { name: { [Op.iLike]: `%${filter}%` } }, }); const recipients = await Recipient.findAll({ where: { name: { [Op.iLike]: `%${filter}%` } }, order: [['updated_at', 'DESC']], attributes: { exclude: ['createdAt', 'updatedAt'] }, limit: page ? 10 : 10000000, offset: page ? (page - 1) * 10 : 0, }); res.header('X-Total-Count', count); return res.json(recipients); }
/** * Builds query to get records where the value of the field contains the value. * Setting caseSensitive to true will cause the regex to be case insensitive */ const buildContainsQuery = function({ field, value, caseSensitive = false }) { // TODO: contains is not working as expected, like is for string matching - doublecheck this if (caseSensitive) { return { name: field, value: { [Op.like]: value } }; } else { return { name: field, value: { [Op.iLike]: value } }; } }
describe('buildContainsQuery Tests', () => { test('Should return case sensitive match regex query', () => { const expectedResult = { name: 'foo', value: { [Op.like]: 'bar' } }; let observedResult = sqlQB.buildContainsQuery({ field: 'foo', value: 'bar', caseSensitive: true, }); expect(observedResult).toEqual(expectedResult); }); test('Should return case insensitive match regex query', () => { const expectedResult = { name: 'foo', value: { [Op.iLike]: 'bar' } }; let observedResult = sqlQB.buildContainsQuery({ field: 'foo', value: 'bar', }); expect(observedResult).toEqual(expectedResult); }); });
async index(req, res) { const { page = 0, filter = '' } = req.query; const { count } = await Deliveryman.findAndCountAll({ where: { name: { [Op.iLike]: `%${filter}%` } }, }); const deliverymen = await Deliveryman.findAll({ where: { name: { [Op.iLike]: `%${filter}%` } }, order: [['updated_at', 'DESC']], attributes: ['id', 'name', 'email'], limit: page ? 10 : 10000000, offset: page ? (page - 1) * 10 : 0, include: [ { model: File, as: 'avatar', attributes: ['id', 'name', 'path', 'url'], }, ], }); res.header('X-Total-Count', count); return res.json(deliverymen); }