How to extract Sphinx group by numbers from result set?

Question

I need some help on Sphinx MySql index group by.

I have a product table where I have product ids and corresponding retailer ids. What I want to do is to create a simple list to show the retailers' names along with how many products it has in the table:

standard SQL: select retailerid, count(productid)as cnt from tblproducts Sphinxs: select * from tblproducts1 group by retailerid

I am using Sphinx 2.0.1-beta

The reason I want to use Sphinx is, because the number of products will be filtered by a complex match full text query, on product name and description etc.

Now my question is, how do I get these numbers/ids from the result returned by Sphinx? As I am using php to display results, I would like if somebody can show me some useful php code to extract these numbers from the result set. The result array returned by Sphinx is confusing me even more; I do not see any reference to the product count anywhere!

I am sure that Sphinx returns these numbers as it supports group by and count, but how do I extract the numbers from the result set?

Below is from my config file:

sql_query       = \
    SELECT ProductId, ProductName, ProductModel, ProductDesc, ProductManf, ProductHeader, \
    ProductPrice, ProductPrePrice, ProductFetchDate, m.MerchantId, m.MerchantActive FROM tblproducts p \
    inner join tblmerchantlist m on p.MerchantId=m.MerchantId


sql_attr_uint       = MerchantActive
sql_attr_float      = ProductPrice
sql_attr_float      = ProductPrePrice
sql_group_column    = MerchantId

sql_query_info      = SELECT * FROM tblproducts WHERE ProductId=$id

UPDATE

I would expect so, but I don't see the reference to count in the below resultset. I must be making some mistakes somewhere:

Array ( [error] => [warning] => [status] => 0 [fields] =>  
   Array ( [0] => productname [1] => productmodel [2] => productdesc [3] => productmanf [4] => productheader [5] => productfetchdate ) [attrs] =>
   Array ( [productprice] => 5 [productpreprice] => 5 [merchantid] => 1 [merchantactive] => 1 ) [matches] =>
   Array ( [0] =>
   Array ( [id] => 694173 [weight] => 396305 [attrs] =>
   Array ( [productprice] => 1568.48999023 [productpreprice] => 0 [merchantid] => 12 [merchantactive] => 1 ) ) [1] =>
   Array ( [id] => 901921 [weight] => 396305 [attrs] =>
   Array ( [productprice] => 1533.48999023 [productpreprice] => 1536.98999023 [merchantid] => 12 [merchantactive] => 1 ) ) [2] =>
   Array ( [id] => 302573 [weight] => 249249 [attrs] =>
   Array ( [productprice] => 1059.98999023 [productpreprice] => 0 [merchantid] => 12 [merchantactive] => 1 ) ) [3] =>
   Array ( [id] => 302579 [weight] => 249249 [attrs] =>
   Array ( [productprice] => 1179.98999023 [productpreprice] => 0 [merchantid] => 12 [merchantactive] => 1 ) ) [4] =>
   Array ( [id] => 302592 [weight] => 249249 [attrs] =>
   Array ( [productprice] => 1429.48999023 [productpreprice] => 0 [merchantid] => 12 [merchantactive] => 1 ) ) [5] =>
   Array ( [id] => 302595 [weight] => 249249 [attrs] =>
   Array ( [productprice] => 1592.98999023 [productpreprice] => 0 [merchantid] => 12 [merchantactive] => 1 ) ) [6] =>
   Array ( [id] => 302597 [weight] => 249249 [attrs] =>
   Array ( [productprice] => 1129.98999023 [productpreprice] => 0 [merchantid] => 12 [merchantactive] => 1 ) ) [7] =>
   Array ( [id] => 406798 [weight] => 249249 [attrs] =>
   Array ( [productprice] => 2419.98999023 [productpreprice] => 0 [merchantid] => 12 [merchantactive] => 1 ) ) [8] =>
   Array ( [id] => 407480 [weight] => 249249 [attrs] =>
   Array ( [productprice] => 1287.48999023 [productpreprice] => 0 [merchantid] => 12 [merchantactive] => 1 ) ) [9] =>
   Array ( [id] => 693715 [weight] => 249249 [attrs] =>
   Array ( [productprice] => 1234.98999023 [productpreprice] => 0 [merchantid] => 12 [merchantactive] => 1 ) ) ) [total] => 29301 [total_found] => 29301 [time] => 0.137 [words] =>
   Array ( [select] =>
      Array ( [docs] => 390 [hits] => 462 ) [from] =>
      Array ( [docs] => 4332 [hits] => 4637 ) [tblproducts1] =>
      Array ( [docs] => 0 [hits] => 0 ) [where] =>
      Array ( [docs] => 395 [hits] => 448 ) [match] =>
      Array ( [docs] => 108 [hits] => 111 ) [cyberpowerpc] =>
      Array ( [docs] => 66 [hits] => 132 ) [gamer] =>
      Array ( [docs] => 307 [hits] => 715 ) [xtreme] =>
      Array ( [docs] => 410 [hits] => 725 ) [1310lq] =>
      Array ( [docs] => 2 [hits] => 6 ) [in] =>
      Array ( [docs] => 16196 [hits] => 19786 ) [canada] =>
      Array ( [docs] => 1146 [hits] => 1200 ) [group] =>
      Array ( [docs] => 5716 [hits] => 5732 ) [by] =>
      Array ( [docs] => 2143 [hits] => 2289 ) [merchantid] =>
            Array ( [docs] => 0 [hits] => 0 ) 
   )
)

Seems that you query your index via API not SphinxQL. There could be different cases local vs distributed index, empty select list and so on. It hard to suggest something without full example ie config, script that query index. — tmg_tt

tmg_tt tmg_tt · Accepted Answer · 2011-06-09T05:09:12

There is a magic attribute @count that added to your result set for that query

select * from tblproducts1 group by retailerid

The attribute list in result set looks like

id, weight, MerchantActive, ... @groupby, @count

In case you use compat_sphinxql_magic=0 option in searchd section you have explicitly define and alias all needed attributes like

select *, weight(*) as w, count(*) as c from tblproducts1 group by retailerid

The attribute list in result set would look like

id, MerchantActive, ... w, c

How to extract Sphinx group by numbers from result set?

1 Answers