Integrity constraint violation: 1052 Column 'id' in field list is ambiguous

1

Hii there,

I’m trying to obtain a count of comments, but need to only get those whose user accounts are “normal” (rec_status = 1)
For that, I have this piece of code:

            ->contain([
                'Users',
                'ClipsComments' => function($q) {
                    return $q->select([
                        'counter' => $q->func()->count('id'),
                        'clip_id'
                    ])
                    // here there be error
                    ->innerJoinWith('Users', function(Query $q) {
                        return $q->where(['Users.rec_status' => 1]);
                    })
                    ->group(['ClipsComments.clip_id'])
                    ->where(['ClipsComments.rec_status' => 1]);
                }
            ])

The problem here is, that since both Users and ClipsComments have a field id, it throws the error Column 'id' in field list is ambiguous .
My question is, how would I get around this issue?

2

$this->Users->ClipsComments->find(‘list’, [‘keyField’=>‘id’, ‘valueField’=>‘id’])->where([‘ClipsComments.rec_status’ => 1])->count();

Hope i will be correct

3

That would probably normally work, however, as this happens within a contain statement itself, it sadly won’t :\

4

Is it the count('id') that’s causing the problem? Just make that count('ClipsComments.id')?

1 Like
5

Thanks, that did the trick!

6

I try to always include the table alias in all field references, to avoid such situations. Even if the code is working, you might add a new association later, or a new field to a different table, which suddenly causes such an ambiguity. Can make the code a bit clearer too, as it’s obvious from the line in question what’s being referenced, instead of needing to look at context from nearby lines. And I don’t know of any downside to always including them.

1 Like
7

Thanks for the explanation, I’ll take note of it and do this as much as I can as well :slight_smile: