Returning missing results from many to many table

问题:

I have a table structure like following:

Brands => BrandUser <= Users

I need to get brands which have corresponding record in the BrandUser table and the ones which don't have corresponding record in BrandUser table...

I have tried the following query:

public function getUserBrands($userId) {
        $select = new Select();
        $select->from(array('bu' => $this->table));
        $select->join(array('b' => 'brands'), 'bu.brandId = b.id', array('id','name'));
        $select->join(array('u' => 'users'), 'u.id = bu.userId', array('id','username'),Select::JOIN_LEFT);
        $where = new Where();
        $where->equalTo("bu.userId",$userId);
        $select->where($where);
        return $this->branduserTable->selectWith($select)->toArray();
    }

But I only get users which HAVE corresponding record in the BrandUser table... I need to get the rest of the brands which don't have the corresponding value in BrandUser... How can I do this??

回答1:

Mind you, I'm not familiar with Zend-Framework, so you may have to adapt this a bit. But you need to use Brands as the primary/first table, so that it can get all the records of that table first, then match it to the rest of the tables.

public function getUserBrands($userId) {
    $select = new Select();
    $select->from(array('b' => 'brands'));
    $select->join(array('bu' => $this->table), 'bu.brandId = b.id', array('id','name'),Select::JOIN_LEFT);
    $select->join(array('u' => 'users'), 'u.id = bu.userId', array('id','username'),Select::JOIN_LEFT);
    $where = new Where();
    $where->equalTo("bu.userId",$userId);
    $select->where($where);
    return $this->branduserTable->selectWith($select)->toArray();
}