How to achieve GROUP_CONCAT using goqu and golang

user_5228669 0
Viewed 139

I am trying to write a pretty simple query using goqu. I am trying to write the SQL equivalent of:

SELECT `account_collections`.`id`,
       `account_collections`.`portfolio_id`,
       `account_collections`.`name`,
       `account_collections`.`description`,
       `account_collections`.`total`,
       `account_collections`.`updated_at`,
       `account_collections`.`created_at`,
       `account_collections`.`deleted_at`,
    GROUP_CONCAT(account_collections_account.account_id)
FROM   `account_collections`
LEFT JOIN account_collections_account ON account_collections_account.`account_collections_id` = account_collections.id
WHERE  `account_collections`.`portfolio_id` = 872466922 
         AND account_collections.`deleted_at` IS NULL
         GROUP BY account_collections.id

However, I see nothing about GROUP_CONCAT in the documentation. I have tried variation on the below, basically trying to write a raw SELECT statement, but obviously GROUP_CONCAT isn't interpreted correctly whether in the ticks or not. I have shortened the example just to illustrate the point below.

query := r.dbCore.
    Select(`account_collections.id`, GROUP_CONCAT(`account_collections_account.account_id`)).
    From(goqu.T(`account_collections`)).
    LeftJoin(goqu.T(`account_collections_account`),
    goqu.On(
        goqu.Ex{
            `account_collections.id`: goqu.Op{"eq": goqu.I(`account_collections_account.account_collections_id`)},
            
        },
    ),
).
Where(goqu.And(
    goqu.T(`account_collections`).Col(`portfolio_id`).Eq(f.PortfolioID),
)).GroupBy(`account_collections.id`)
go mysql
1 Answers

Only to clarify, the usage of goqu.L and goqu.I is explained below:

goqu.L: Creates a new SQL literal with the provided arguments; for example:

L("a = ?", "b") -> a = 'b'

goqu.I: Creates a new Identifier, the generated SQL will use adapter specific quoting or '"' by default, this ensures case-sensitivity and in certain databases allows for special characters.

So for GROUP_CONCAT following code could be used:

goqu.L("GROUP_CONCAT(?)", goqu.I("column name"))

The full code looks like this:

query := r.dbCore.
    Select(`account_collections.id`, goqu.L("GROUP_CONCAT(?)", goqu.I("account_collections_account.account_id"))).
    From(goqu.T(`account_collections`)).
    LeftJoin(goqu.T(`account_collections_account`),
        goqu.On(
            goqu.Ex{
                `account_collections.id`: goqu.Op{"eq": goqu.I(`account_collections_account.account_collections_id`)},
            },
        ),
    ).
    Where(goqu.And(
        goqu.T(`account_collections`).Col(`portfolio_id`).Eq(f.PortfolioID),
    )).GroupBy(`account_collections.id`)
user_155640950
Related