AddSelect In searchQueryBuilder use condition

Question

I have to get my customers, And all customers Have a (customers groups) for example : customers/seller/guest/buyer.... And any customer Have a default Group , And I want to get the default group of this customer only when he is a Buyer but when he is a seller I want to get any other group (for sur when he is in this group) I should do this condition in mysql part . and this what I do but I get some problems

        $searchQueryBuilder->addSelect("CASE 
        WHEN cg.id_group = 4 THEN c.id_default_group 
        ELSE cg.id_group END as id_group");
        //dump($searchQueryBuilder);die;
        $searchQueryBuilder->leftJoin(
            'c',
            '' . pSQL(_DB_PREFIX_) . 'customer_group',
            'cg',
            'c.id_customer = cg.id_customer ' 
        );

        $searchQueryBuilder->leftJoin(
            'cg',
            '' . pSQL(_DB_PREFIX_) . 'group_lang',
            'gcl',
            'id_group = gcl.id_group AND gcl.id_lang = '.(int) $this->context->language->id
        );

I get a error when I use a alias column in LeftJoin

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'id_group' in on clause is ambiguous — Ahmed guesmi, May 12 '21 at 09:26
@Ahmed, please put any relevant details (such as the error message) inside the question itself, not the comments. Also it would be helpful if you can indicate what you have tried to fix the issue and what was the result of those experiments. — Jason D, May 12 '21 at 12:32

score 0 · Answer 1 · answered May 12 '21 at 09:41

It means there is more than one columns with " id_group " name.

For example when we use join between two table "users and subjects" now both tables have column "id", and we use "group by id"

system will error column "id" is ambiguous. so we need to tell from which table column id is used by "group by".

Something like this:

"GROUP BY users.id"

AddSelect In searchQueryBuilder use condition

1 Answers1