如何从结果集中按数字提取Sphinx组?
我需要一些有关 Sphinx MySql 索引分组依据的帮助。
我有一个产品表,其中有产品 ID 和相应的零售商 ID。我想要做的是创建一个简单的列表来显示零售商的名称以及表中的产品数量:
标准 SQL: select Retailerid, count(productid)as cnt from tblproducts Sphinxs: select * from tblproducts1 group by Retailerid
我正在使用 Sphinx 2.0.1-beta
我想使用 Sphinx 的原因是,因为产品数量将通过复杂的匹配全文查询、产品名称和描述等进行过滤。
现在我的问题是,如何从 Sphinx 返回的结果中获取这些数字/id?由于我使用 php 来显示结果,我希望有人可以向我展示一些有用的 php 代码来从结果集中提取这些数字。 Sphinx 返回的结果数组让我更加困惑;我在任何地方都没有看到任何有关产品数量的参考!
我确信 Sphinx 返回这些数字,因为它支持分组依据和计数,但如何从结果集中提取数字?
以下来自我的配置文件:
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
更新
我希望如此,但我在下面的结果集中没有看到对计数的引用。我一定在某个地方犯了一些错误:
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 )
)
)
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 )
)
)
有一个神奇的属性 @count 添加到该查询的结果
集中 结果集中的属性列表看起来像
如果您在 searchd 部分中使用 compat_sphinxql_magic=0 选项,则您已显式定义所有需要的属性并为所有需要的属性添加别名,例如
结果中的属性列表设置看起来像
There is a magic attribute @count that added to your result set for that query
The attribute list in result set looks like
In case you use compat_sphinxql_magic=0 option in searchd section you have explicitly define and alias all needed attributes like
The attribute list in result set would look like