Column 'id' in field list is ambiguous

160 Posts

insomnix Reply #1, 14 years, 3 months ago

I have a schema using xpdosimpleobject's, which of course gives me a primary key of id by default. When I try to build a join query, I get the "Column 'id' in field list is ambiguous" error. Is there a way around this, or do I just need to rebuild these tables with a custom pk field?

Example of my code:

        $c = $this->modx->newQuery('primaryObject');
        $c->select($this->modx->getSelectColumns('primaryObject'));
        $c->select(array(
            'secobj.item_qty'
            ,'secobj.id AS secobj_id'
        ));

        $c->leftJoin('secondObject','secobj','primaryObject.id=secobj.ref_id');
        $c->where(array(
            'active' => true
            ,'secobj.ref_id' => $ref_id
        ));
        //

        $items = $this->modx->getCollection('primaryObject',$c);

- MODX Staff
- 10,725 Posts
opengeek Reply #2, 14 years, 3 months ago
To understand the problem better, do this to see the actual query that will be executed output to the page:
```
$c->prepare();
$modx->log(modX::LOG_LEVEL_ERROR, $c->toSql(), 'HTML');

$items = $this->modx->getCollection('primaryObject', $c);
```
Jason Coward
Chief Architect @ MODX
http://www.jasoncoward.com | http://twitter.com/drumshaman | https://github.com/opengeek