JDatabase subQuery not work [duplicate]

Question

I have products table and brads table, i want show record from brands table whit product count.

The query:

SELECT a.*, 
    (
       SELECT  COUNT(*)
       FROM    jos_products AS p
       WHERE   p.brand_id = a.id
    ) AS n
     FROM jos_brands AS a 
ORDER BY a.name ASC

Resoult on MySQLManager is correct.

protected function getListQuery()
{
    $db    = JFactory::getDbo();

    $query = $db->getQuery(true);
        $query->select          ('a.*');

            $query->subQuery();
                $query->select  ('COUNT(*)');
                $query->from    ('#__products AS p');
                $query->where   ('p.brand_id = a.id');
                $query->as      ('n');
            $query->endSubQuery();

        $query->from            ('#__brands AS a');
        $query->oderby          ('a.name ASC');

    return $query;
}

The error is "Undefined property: stdClass::$n" when i try to show the results.

I try this:

  protected function getListQuery()
    {
        $db    = JFactory::getDbo();

        $query = $db->getQuery(true);
            $query->select          ('a.*');

                $query->subQuery();
                    $query->select  ('COUNT(*) AS n');
                    $query->from    ('#__products AS p');
                    $query->where   ('p.brand_id = a.id');
                $query->endSubQuery();

            $query->from            ('#__brands AS a');
            $query->oderby          ('a.name ASC');

        return $query;
    }

But result not is correct.

How to implement my query?

Answer 1

Besides that this does not look like Joomla API (see my comment under your question) I don't think that you need a subquery here. Just something like this should work:

$query->select('a.*, COUNT(p.*) AS n');
    ->from('#__brands AS a, #__products AS p');
    ->where('p.brand_id = a.id');
    ->order('a.name ASC');

Otherwise see here how to implement sub queries in Joomla: Method for creating a subquery using JDatabase