Need Some Help Joining Category Name to Search Query

[philip937]

I am trying to amend my search code to include the category name in the criteria.

here's what I have done so far but it isn't working.

I added cd.categories_name to the end of the select string:

$select_str = "SELECT DISTINCT " . $select_column_list .
              " m.manufacturers_id, p.products_id, pd.products_name, p.products_price, p.products_tax_class_id, p.products_price_sorter, p.products_qty_box_status, p.master_categories_id, cd.categories_name "; 

I added a JOIN to the categories description table:

$from_str = "FROM " . TABLE_PRODUCTS . " p" .
            " LEFT JOIN " . TABLE_MANUFACTURERS . " m USING(manufacturers_id)" .
            " LEFT JOIN " . TABLE_PRODUCTS_DESCRIPTION . " pd on p.products_id = pd.products_id" .
            " JOIN " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c on p.products_id = p2c.products_id" .
            " JOIN " . TABLE_CATEGORIES . " c on p2c.categories_id = c.categories_id" .
            //added line below
            " JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd ON p.master_categories_id = cd.categories_id" .
            " LEFT JOIN " . TABLE_META_TAGS_PRODUCTS_DESCRIPTION . " mtpd ON mtpd.products_id= p2c.products_id AND mtpd.language_id = :languagesID" .
            ($filter_attr == true ? " JOIN " . TABLE_PRODUCTS_ATTRIBUTES . " p2a on p.products_id = p2a.products_id" .
            " JOIN " . TABLE_PRODUCTS_OPTIONS . " po on p2a.options_id = po.products_options_id" .
            " JOIN " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov on p2a.options_values_id = pov.products_options_values_id" .
            (defined('TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK') ? " JOIN " . TABLE_PRODUCTS_WITH_ATTRIBUTES_STOCK . " p2as on p.products_id = p2as.products_id " : "") : ''); 

and lastly added the OR check of the keyword matching the cd.categories_name :

        $where_str .= "(pd.products_name LIKE '%:keywords%'
                                         OR p.products_model
                                         LIKE '%:keywords%'
                                         OR m.manufacturers_name
                                         LIKE '%:keywords%'
                                         OR cd.categories_name
                                         LIKE '%keywords%'"; 

I thought that would do it.

page doesnt error so I assume its accepting the query Ok, maybe i'm joining wrong? Can anyone see what I am doing incorrect?

Thanks in advance.

Phil

[philip937]

anyone? i'm still stuck on this

[philip937]

I stil havent found a solution to this, any help would be appreciated.

[DrByte]

While it would be more syntactically correct to use the immediately-previous table's name in the join as shown below, if 'strict mode" isn't turned on, then it's moot.

            " JOIN " . TABLE_CATEGORIES . " c on p2c.categories_id = c.categories_id" .
            //added line below
            " JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd ON c.categories_id = cd.categories_id" .

However, at a high level view I would expect your posted changes to work fine. Just as you said, no errors.

So, I suspect your problem may be with your expectations. The build-in search system returns *products* in its results, agnostic of categories or other pages. It doesn't list categories or pages in search results. To do that requires rewriting the entire output logic.

[philip937]

While it would be more syntactically correct to use the immediately-previous table's name in the join as shown below, if 'strict mode" isn't turned on, then it's moot.

            " JOIN " . TABLE_CATEGORIES . " c on p2c.categories_id = c.categories_id" .
            //added line below
            " JOIN " . TABLE_CATEGORIES_DESCRIPTION . " cd ON c.categories_id = cd.categories_id" .

However, at a high level view I would expect your posted changes to work fine. Just as you said, no errors.

So, I suspect your problem may be with your expectations. The build-in search system returns *products* in its results, agnostic of categories or other pages. It doesn't list categories or pages in search results. To do that requires rewriting the entire output logic.

Not sure I follow doc, the expectations are that by joining the table I can return products that match the category name when searched where the master category ids name in the category description table matches that of a search term. For example

Category: Fruit
Products: apple, pear, bananana all with master category ID Fruits

When searching "fruit" products returned are
apple, pear, bananana

Does that make what I'm trying to achieve clearer?

Thanks
Phil

[DrByte]

I think you'll need a different query for that then. The JOIN syntax is essentially triggering an "and" condition, but you're looking for an "or" condition such as "or if the master category's name contains the search keyword then check all other products which have that master category id". So you probably want to do another initial query to get that information established, and then add that as "or" criteria to the main query.

[philip937]

I think you'll need a different query for that then. The JOIN syntax is essentially triggering an "and" condition, but you're looking for an "or" condition such as "or if the master category's name contains the search keyword then check all other products which have that master category id". So you probably want to do another initial query to get that information established, and then add that as "or" criteria to the main query.

Oh right, thanks for the pointer, ill have another stab then. I thought having the or like statements was making it or rather than and. I must be misunderstanding the structure of the query