Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I have 2 models, business and documents in a 1:n relationship, i want to filter the business in two ways,

I want to make something like this

select
    A.name, B.due_balance, sum(B.due_balance) as total_due_balance
    business A
inner join documents B ON A.id = B.business_id
group by A.id
having total_due_balance > 0;
select
    A.name, B.due_balance, sum(B.due_balance) as total_due_balance
    business A
inner join documents B ON A.id = B.business_id
group by A.id
having total_due_balance = 0;
these will get me what i want, the problem, is that the previus code was made with sequelize ORM, and i can't change it, something like this
const businesses = await db.business.paginate({
    attributes: [
    where: {
    ... //bunch of where
    page: parseInt(params.page, 10) || 1,
    paginate: parseInt(params.limit, 10) || 10,
here is where the problem begins, i don't know how to join the tables and use the having to filter it, i have tried addind this
let toInclude;
if (params.contactability === 'with_balance') {
    toInclude = {
        include : [
                attributes: [
                    [db.Sequelize.fn('sum', db.Sequelize.col('due_balance')), 'total_due_balance'],
                model: db.document,
                as: 'documents',
                having: db.Sequelize.where(db.Sequelize.fn('sum', db.Sequelize.col('due_balance')), {
                    $gt: 0,
} else if(params.contactability === 'without_balance') {
    toInclude = {
        include : [
                attributes: [
                    [db.Sequelize.fn('sum', db.Sequelize.col('due_balance')), 'total_due_balance'],
                model: db.document,
                as: 'documents',
                having: db.Sequelize.where(db.Sequelize.fn('sum', db.Sequelize.col('due_balance')), {
                    $eq: 0,
} else {
    toInclude = {};
const businesses = await db.business.paginate({
    attributes: [
    where: {
    ...toInclude,
    page: parseInt(params.page, 10) || 1,
    paginate: parseInt(params.limit, 10) || 10,
but that does not work at all, how can i solve this problem?
I don't think HAVING will work without GROUP.
I would move the having clause outside the include section and use the AS aliases.
So, roughly: 
group: ['id'],  // and whatever else you need
 having : { 'documents.total_balance_due' : {$eq : 0 }}
(Making some guesses vis the aliases)

To filter the date from joined table which uses groupby as well, you can make use of HAVING Property, which is accepted by Sequelize.
So with respect to your question, I am providing the answer.
You can make use of this code:
const Sequelize = require('sequelize');
let searchQuery = {
    attributes: {
        // include everything from business table and total_due_balance as well
        include: [[Sequelize.fn('SUM', Sequelize.col('documents.due_balance')), 'total_due_balance']]
    include: [
            model: Documents, // table, which you require from your defined model
            as: 'documents', // alias through which it is defined to join in hasMany or belongsTo Associations
            required: true, // make inner join
            attributes: [] // select nothing from Documents table, if you want to select you can pass column name as a string to array
    group: ['business.id'], // Business is a table
    having: ''
if (params.contactability === 'with_balance') {
    searchQuery.having = Sequelize.literal(`total_due_balance > 0`);
} else if (params.contactability === 'without_balance') {
    searchQuery.having = Sequelize.literal(`total_due_balance = 0`);
Business // table, which you require from your defined model
    .findAll(searchQuery)
    .then(result => { 
        console.log(result);
    .catch(err => {
        console.log(err);
Note : Change model name or attributes according to your requirement.
Hope this will help you or somebody else!
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.