Knex: GROUP_CONCAT is not defined

[tshah09]

Hello.
Is there option to get selected data in group_concat way.

Mysql query -
SELECT sc.name, group_concat(jb.name) as jobname, group_concat(sj.job_availability) as jobsavail , ct.name as cityname FROM service_centers as sc
LEFT JOIN cities as ct on ct.id = sc.city_id
LEFT JOIN service_jobs as sj on sj.service_center_id = sc.id
LEFT JOIN jobs as jb on sj.job_id = jb.id
where sc.id = 184

Knex Query-
mysqlObj.knex('service_centers as sc')
.leftJoin('service_jobs as sj','sj.service_center_id','=','sc.id')
.leftJoin('jobs as jb', 'jb.id','=', 'sj.job_id')
.where('sc.id', '=', id)
.select('sc.id',
'sc.name',
'sbd.type as brandtype',
'jb.name as jobname',
GROUP_CONCAT('jb.job_availability as availjob')
)
.then(function(collection) {
console.log(collection);
callback({
data:collection
});
})
.catch(function(err) {
res.status(500).json({
error: true,
data: {
message: err.message
}
});
});
}

[chrisbroome]

You can use knex.raw:

knex('service_centers as sc')
  .leftJoin('service_jobs as sj','sj.service_center_id','=','sc.id')
  .leftJoin('jobs as jb', 'jb.id','=', 'sj.job_id')
  .where('sc.id', '=', id)
  .select('sc.id'
    , 'sc.name'
    , 'sbd.type as brandtype'
    , 'jb.name as jobname'
    , knex.raw('GROUP_CONCAT(??.?? AS ??)', ['jb', 'job_availability', 'availjob']
  )
)

Also, note that I used identifier placeholders for table/column/alias names so that they get properly escaped by knex.

As always, when using GROUP_CONCAT remember that there's a 1024 character limit on the results.