Groupby in Lovefield

133 views Asked by At

hello i tried to use innerjoin in lovefield and tried to use groupBy like this:

    var userCompanies = '', company = '';
                user = db.getSchema().table('user');
                userCompanies = db.getSchema().table('userCompanies');
                company = db.getSchema().table('company');
                db.select().
                from(user).
                innerJoin(userCompanies, userCompanies.UserId.eq(user.Id)).
                innerJoin(company, company.Id.eq(userCompanies.CompanyId)).
                where(userCompanies.UserId.eq(row["Id"])).exec()
                // groupBy(user, user.Id).exec()
                .then(function(results) {
                  results.forEach(function(row) {
                    console.log(row['user']);
                  });
                })

but it's only giving me this error: Syntax error: (525) Invalid projection list or groupBy columns.

1

There are 1 answers

0
Angeldev On

the userCompanies.Id and company.Id should appear in the groupBy() statement or it should is an aggregated columns

for example

  var userCompanies = '',
                company = '';
            user = db.getSchema().table('user');
            userCompanies = db.getSchema().table('userCompanies');
            company = db.getSchema().table('company');
            db.select().
            from(user).
            innerJoin(userCompanies, userCompanies.UserId.eq(user.Id)).
            innerJoin(company, company.Id.eq(userCompanies.CompanyId)).
            where(userCompanies.UserId.eq(row["Id"])).exec()
            groupBy(user.Id).
            groupBy(userCompanies.Id).
            groupBy(company.Id).exec()
                .then(function(results) {
                    results.forEach(function(row) {
                        console.log(row['user']);
                    });
                })