SQL Join with count

For the real estate site I've been working on, I needed to get a list of real estate offices and how many active listings they had on the MySQL DB we are using. I had a table of offices and a table of listings. I needed each office represented even if that office had no active listings. It seemed like an easy application of the left join command. Not quite as easy as I thought. Here's my first attempt:


SELECT
COUNT(listings.id), offices.id
FROM offices LEFT JOIN listings ON (offices.id = listings.office_id)
WHERE
listings.status = 1


However, this only returned office/count pair where the count was greater than 0. It seemed like the WHERE condition wasn't being applied correctly, like it was being applied AFTER the tables were joined. I wanted the WHERE condition to be applied to the listings table and then have the results joined to the offices table. So, here's the solution I found: in situations like this, the WHERE clause needed to be added to the ON clause in the LEFT JOIN like this:


SELECT
COUNT(listings.id), offices.id
FROM offices LEFT JOIN listings ON (offices.id = listings.office_id AND listings.status = 1)


Works like a charm.

Dynamic Credentials in Symfony

For a long time now I've been using dynamic credentials in Symfony as laid out in this code snippit: http://www.symfony-project.org/snippets/snippet/18.




// to put in the actions.class.php file
function getCredential()
{
  $this->post = $this->_retrievePost(); // retrieving the object based on the request parameters
  if ($this->getUser()->isOwnerOf($this->post))
    $this->getUser()->addCredential('owner');
  else
    $this->getUser()->removeCredential('owner');

  // the hijack is over, let the normal flow continue:
  return parent::getCredential();
}

However, it seems like this isn't really the proper way to add dynamic credentials. The getCredential() method is called by the Symfony FilterChain to discover what credentials that module requires, not to add credentials to the user.

For me, the problem cam to a head when I was trying to show a link if a user had a particular credential. The problem was that the page with the link was not a secure page. Since the page was not secute (is_secure: off in the config/security.yml file) the getCretential() method was never called.

The solution is to use the preExecute() method instead of getCredential()




// to put in the actions.class.php file
function preExecute()
{
  $this->post = $this->_retrievePost(); // retrieving the object based on the request parameters
  if ($this->getUser()->isOwnerOf($this->post))
    $this->getUser()->addCredential('owner');
  else
    $this->getUser()->removeCredential('owner');

  // the hijack is over, let the normal flow continue:
  return parent::getCredential();
}