PHQL GROUP_CONCAT SEPARATOR error

Question

PHQL GROUP_CONCAT SEPARATOR error

Stefan Apr '15

Created Apr '15	Last Reply Mar '19	Replies 3	Views 1228	Votes 0

Stefan 1.9k

Apr '15

I have phql query looking like this:

$query = $this->modelsManager->createQuery("SELECT Projects.id,FROM_UNIXTIME(Projects.created_at,' %h:%i%p %m/%d') as created_at,Projects.project_title,Projects.project_description,ProjectTypes.project_type_name,DeveloperLevel.level_name,DeveloperLevel.level_description,GROUP_CONCAT(Skill.skill_name SEPARATOR ', ' )  as skills
        FROM Projects
        Left Join ProjectTypes
        ON Projects.project_type = ProjectTypes.id
        Left Join DeveloperLevel
        ON Projects.developer_level = DeveloperLevel.id
        Left Join ProjectSkill
        ON Projects.id = ProjectSkill.pID
        Left Join Skill
        ON ProjectSkill.sID = Skill.id

        WHERE Projects.id IN({$ids}) GROUP BY Projects.id order by Projects.id DESC ");

        $respond = $query->execute()->toArray();

When I try to execute I am getting : Syntax error, unexpected token IDENTIFIER(SEPARATOR), near to ' ', '

I can remove the SEPARATOR and it works perfect, but I don't want to reformat data in frontend replacing ',' with ', '. Did I mess something with query or phql doesn't support yet separator ?

Julián Arturo Molina Castiblanco
3.5k

Accepted
answer

Apr '15

The error is here, -> GROUP_CONCAT(Skill.skill_name SEPARATOR ', ' )

Mahdi Mohammadi · Answer 1 · 2015-04-11T07:45:14-07:00


$query = parent::modelsManager()
                       ->createBuilder()
                       ->columns(array('Video.id',
                                       'Video.title',
                                       'Video.status',
                                       'Video.adminId',
                                       'Video.viewNum',
                                       'Video.commentRead',
                                       'Video.commentUnRead',
                                       'Video.published',
                                       "GROUP_CONCAT(DISTINCT Category.name SEPARATOR '+') as cc",
                       ))
                       ->addfrom('Multiple\Backend\Models\Video','Video')
                       ->leftjoin('Multiple\Backend\Models\VideoCategory','Video.id = VideoCategory.videoId','VideoCategory')
                       ->join('Multiple\Backend\Models\Category','VideoCategory.categoryId = Category.id','Category')
                       ->orderBy('Video.id desc')
                       ->groupBy('Video.id');

I try to execute but getting : Syntax error, unexpected token IDENTIFIER(SEPARATOR), near to ' '+'

and orginal SQL is:


 SELECT
    Video.id,
    Video.title,
    Video. STATUS,
    Video.adminId,
    Video.viewNum,
    Video.commentRead,
    Video.commentUnRead,
    Video.published,
    GROUP_CONCAT(
        DISTINCT Category. NAME SEPARATOR '+'
    ) AS cc
FROM
    video as Video
LEFT JOIN video_category  AS VideoCategory ON Video.id = VideoCategory.videoId
JOIN category AS Category ON VideoCategory.categoryId = Category.id
GROUP BY
    Video.id
ORDER BY
    Video.id DESC
LIMIT 20

how i can get true result?

heui · Answer 2 · 2019-03-31T21:55:31-07:00

I hava the same problem

Phalcon\Mvc\Model\Exception: Syntax error, unexpected token IDENTIFIER(SEPARATOR)