anonymous user

Forums   Register   Login   Forgot your login/password?   Search

new help with structure I think with category count

Common forum | 1 | 2 | 3 | 4 | 5 | ... | 449 | 450 | 451 | 452 | next »» | Create new thread

likedon

Name: Andreas Pape
Posts: 6

2011-01-12 21:01:10 | reply!

Hi
I have the following:
[product table] (id, title, cat)
"1";"test_a";"1,2,3,4"
"2";"test_b";"2,4"
"2";"test_b";"4"


[category table] (id, title)
"1";"cat_a"
"2";"cat_b"
"3";"cat_c"
"4";"cat_d"

I want to know how many products are in each category who meet the search criteria.

The result of the above should be:
cat_a ->1
cat_b ->2
cat_c ->1
cat_d ->3

So how can I find out.
I guess I can't use:

$sp->SetGroupBy ( "cat", SPH_GROUPBY_ATTR, "@count desc" );

But then how.
I have 1 mill products and 100k categories, and loves Sphinx so far.
Do help me please.
barryhunter

Name: Barry Hunter
Posts: 5530

to: likedon, 2011-01-12 23:07:28 | reply!

> I guess I can't use:
> $sp->SetGroupBy ( "cat", SPH_GROUPBY_ATTR, "@count desc" );

You CAN! As long as you have

sql_query = SELECT id,title,cat FROM product_table
sql_attr_multi = uint cat from field



then once you run the query, look at the results, extract the cat from the attributes,
and use the ids to look up the titles in the category title - very easy (or it should be)
to do in your code.
likedon

Name: Andreas Pape
Posts: 6

to: barryhunter, 2011-01-13 08:20:00 | reply!

But how will that give me a navigation menu where it says how many result there are in
each?

like:
q: die hard

total results: 500


comedy(57)
action(25)
crime(2)
.
.
.
barryhunter

Name: Barry Hunter
Posts: 5530

to: likedon, 2011-01-13 15:35:58 | reply!

Try doing a
print_r($res['matches']);

(assumeing using PHP)

that should show you how the data is structured in the returned results.
likedon

Name: Andreas Pape
Posts: 6

to: barryhunter, 2011-01-13 21:30:18 | reply!

> Try doing a
> print_r($res['matches']);
>
> (assumeing using PHP)
>
> that should show you how the data is structured in the returned results.


as it is one field it will only give me one number.
that is why i labelled it Structural help :-)

How should i build the table to be able to do a thing like this.

What i'm after is something like this:
http://www.saxo.com/search/search.aspx?keyword=die

the left menu functions as filters for the search, and keeps updating.

I want that too :-)
barryhunter

Name: Barry Hunter
Posts: 5530

to: likedon, 2011-01-13 21:41:08 | reply!

Your matches will be something like

                        [217277] => Array
                                (
                                [weight] => 1
                                [attrs] => Array
                                (
                                [cat_id] => 4
                                [@groupby] => 4
                                [@count] => 1
                                )

                                )

so can just do something like:

$counts = array();
foreach ($res['matches'] as $doc_id => $row) {
        $counts[$row['attrs']['@groupby']] = $row['attrs']['@count'];
}

$ids = array_keys($counts);
$names = $db->getAssoc("SELECT cat_id,name FROM category WHERE cat_id IN
(".implode(',',$ids).")");

foreach ($counts as $cat_id => $count) {
        printf("%s : %d<br/>",$names[$cat_id],$count);
}


Another point to remember, probably want to set SetArrayResult to true.
likedon

Name: Andreas Pape
Posts: 6

to: barryhunter, 2011-01-14 09:05:19 | reply!

Thankyou Barry

I'll try to see if I can get it to work and post back.

Common forum | 1 | 2 | 3 | 4 | 5 | ... | 449 | 450 | 451 | 452 | next »» | Create new thread