KoolReport's Forum

Official Support Area, Q&As, Discussions, Suggestions and Bug reports.
Forum's Guidelines

Normal query or queryBuilder for query with group_concat #2707

Closed 50Grosh opened this topic on on Jun 6 - 2 comments

50Grosh commented on Jun 6

Hello

i want to make query like this


       SELECT 
            post_title,
            user_id, 
            name, 
            surname, 
            group_concat(additional_option_name separator ', ') additional_option_name,
            group_concat(ask_user separator ', ') ask_user,
            Sum(additional_option_price) additional_option_price
        FROM users
        LEFT JOIN event_items on users.id = event_items .id
        LEFT JOIN event_items_additional on users.id = event_items_additional .event_items_id
        LEFT JOIN event_items_ask_user on users.id = event_items_ask_user .event_items_id
        GROUP BY post_title, user_id, name, surname  
        ORDER BY `users`.`name` ASC;


this query works normally in my DB and returns this table

ID item_id name surname additional_option_name ask_user amount
1 1 john wawric car, car2blue, red 25
2 2 monicalosscar, planered 14

When i pass it like that



$this->src('skaleo')->query('
        SELECT 
            post_title,
            user_id, 
            name, 
            surname, 
            group_concat(additional_option_name separator ', ') additional_option_name,
            group_concat(ask_user separator ', ') ask_user,
            Sum(additional_option_price) additional_option_price
        FROM users
        LEFT JOIN event_items on users.id = event_items .id
        LEFT JOIN event_items_additional on users.id = event_items_additional .event_items_id
        LEFT JOIN event_items_ask_user on users.id = event_items_ask_user .event_items_id
        GROUP BY post_title, user_id, name, surname  
        ORDER BY `users`.`name` ASC;
        ')
        ->pipe($this->dataStore('test'));

but i get error " Uncaught TypeError: number_format(): Argument #1 ($num) must be of type float, string given "

also I try using QueryBulider

$this->src('skaleo')
            ->query(
                DB::table("users")
->leftJoin("event_items ",'users.id','=','event_items .id')          
->leftJoin("event_items_additional ",'users.id','=','event_items_additional .event_items_id')                    ->leftJoin("event_items_ask_user ",'users.id','=','event_items_ask_user .event_items_id')
  ->select(
                       "name",
                        "surname",
                        "post_title",
                        "additional_option_name",
                        "group_concat(additional_option_name) additional_option_name",
                       "group_concat(additional_option_price) additional_option_price",
                       "`event_items_additional`.`currency`",
                       "ask_user",
                       "`event_items_ask_user`.`answer`"
                    )->alias("additional_option_name")

            ) ->pipe($this->dataStore("asks"))

but this time its join all rows in one like that

ID item_id name surname additional_option_name ask_user amount
1 1 john wawric car, car2,car, plane, car2,car, plane blue,red,red,red,red 25 ,14

cant's see my mistake

Greetengs

Sebastian Morales commented on Jun 7

Can you pls post the error's full stack trace as well as your report view's php for us to know where in the code it happened? Tks,

50Grosh commented on Jun 20

I did it by used procedure. Thanks for help :)

Build Your Excellent Data Report

Let KoolReport help you to make great reports. It's free & open-source released under MIT license.

Download KoolReport View demo
None yet

QueryBuilder