markguinn/silverstripe-shop-search

ShopSearchMysql: Column 'Title' in where clause is ambiguous

Closed this issue · 15 comments

hex0id commented

I am trying hard to set this up, but with no success. Here's configuration:

Tour:
  extensions:
    - HasStaticAttributes
    - ProductCategoryHelperMethods
    - VirtualFieldIndex

ShopSearch:
  adapter_class: ShopSearchMysql
  searchable:
    - Tour
  facets:
    Price:
      Label: Price
      Type: range
      MinValue: 0
      MaxValue: 2000
      RangeMin: 0
      RangeMax: 2000
      LabelFormat: Currency
  sort_options:
    'score desc': 'Relevance'
    'SiteTree_Title asc': 'Alphabetical'

Destination_Controller:
  extensions:
    - FacetedCategory

VirtualFieldIndex:
  vfi_spec:
    Tour:
      Price:
        Type: simple
        Source: getPerPersonPrice
        DependsOn: all
        DBField: Currency
      Categories:
        Type: list
        DependsOn: all
        Source:
          - ParentID
          - ProductCategories.ID

FacetedCategory:
  facets:
    Price:
      Label: Price
      Type: range
      MinValue: 0
      MaxValue: 2000
      RangeMin: 0
      RangeMax: 2000
      LabelFormat: Currency
  auto_facet_attributes: true
  products_method: ToursShowable

Tour is direct subclass of Product.

Tour has necessary things:

    private static $indexes = array(
        'Featured' => true,
        'AllowPurchase' => true,
        'InternalItemID' => true,
        'SearchFields' => array(
            'type' => 'fulltext',
            'name' => 'SearchFields',
            'value' => 'Title'
        )
    );

and

    private static $searchable_fields = array(
        'Title', 'Content', 'InternalItemID','Featured'
    );

All I get is:

Couldn't run query: SELECT DISTINCT "SiteTree_Live"."ClassName", "SiteTree_Live"."Created", "SiteTree_Live"."LastEdited", "SiteTree_Live"."URLSegment", "SiteTree_Live"."Title", "SiteTree_Live"."MenuTitle", "SiteTree_Live"."Content", "SiteTree_Live"."MetaDescription", "SiteTree_Live"."ExtraMeta", "SiteTree_Live"."ShowInMenus", "SiteTree_Live"."ShowInSearch", "SiteTree_Live"."Sort", "SiteTree_Live"."HasBrokenFile", "SiteTree_Live"."HasBrokenLink", "SiteTree_Live"."ReportClass", "SiteTree_Live"."CanViewType", "SiteTree_Live"."CanEditType", "SiteTree_Live"."Locale", "SiteTree_Live"."ProvideComments", "SiteTree_Live"."Version", "SiteTree_Live"."ParentID", "Page_Live"."InheritSidebar", "Page_Live"."InheritFooterColumn1", "Page_Live"."InheritFooterColumn2", "Page_Live"."InheritFooterColumn3", "Page_Live"."InheritFooterColumn4", "Page_Live"."SidebarID", "Page_Live"."FooterColumn1ID", "Page_Live"."FooterColumn2ID", "Page_Live"."FooterColumn3ID", "Page_Live"."FooterColumn4ID", CASE WHEN "SiteTree_Live"."ClassName" IN ('Product', 'Tour') THEN "Product_Live"."InternalItemID" WHEN "SiteTree_Live"."ClassName" IN ('Tour') THEN "Tour_Live"."InternalItemID" ELSE NULL END AS "InternalItemID", "Product_Live"."Model", CASE WHEN "SiteTree_Live"."ClassName" IN ('Product', 'Tour') THEN "Product_Live"."CostPrice" WHEN "SiteTree_Live"."ClassName" IN ('Tour') THEN "Tour_Live"."CostPrice" ELSE NULL END AS "CostPrice", CASE WHEN "SiteTree_Live"."ClassName" IN ('Product', 'Tour') THEN "Product_Live"."BasePrice" WHEN "SiteTree_Live"."ClassName" IN ('Tour') THEN "Tour_Live"."BasePrice" ELSE NULL END AS "BasePrice", "Product_Live"."Weight", "Product_Live"."Height", "Product_Live"."Width", "Product_Live"."Depth", CASE WHEN "SiteTree_Live"."ClassName" IN ('Product', 'Tour') THEN "Product_Live"."Featured" WHEN "SiteTree_Live"."ClassName" IN ('Tour') THEN "Tour_Live"."Featured" ELSE NULL END AS "Featured", CASE WHEN "SiteTree_Live"."ClassName" IN ('Product', 'Tour') THEN "Product_Live"."AllowPurchase" WHEN "SiteTree_Live"."ClassName" IN ('Tour') THEN "Tour_Live"."AllowPurchase" ELSE NULL END AS "AllowPurchase", CASE WHEN "SiteTree_Live"."ClassName" IN ('Product', 'Tour') THEN "Product_Live"."Popularity" WHEN "SiteTree_Live"."ClassName" IN ('Tour') THEN "Tour_Live"."Popularity" ELSE NULL END AS "Popularity", "Product_Live"."ImageID", "Tour_Live"."Lat", "Tour_Live"."Lng", "Tour_Live"."Address", "Tour_Live"."Suburb", "Tour_Live"."State", "Tour_Live"."Postcode", "Tour_Live"."Country", "Tour_Live"."VFI_Price", "Tour_Live"."VFI_Categories", "SiteTree_Live"."ID", CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName" ELSE 'SiteTree' END AS "RecordClassName" FROM "SiteTree_Live" LEFT JOIN "Page_Live" ON "Page_Live"."ID" = "SiteTree_Live"."ID" LEFT JOIN "Product_Live" ON "Product_Live"."ID" = "SiteTree_Live"."ID" LEFT JOIN "Tour_Live" ON "Tour_Live"."ID" = "SiteTree_Live"."ID" WHERE (MATCH (Title) AGAINST ('fcusa')) AND ("SiteTree_Live"."ParentID" = '48') AND ("SiteTree_Live"."ClassName" IN ('Tour')) AND ("SiteTree_Live"."Locale" = 'en_US') ORDER BY "SiteTree_Live"."Title" ASC Column 'Title' in where clause is ambiguous
markguinn commented

I'm sorry to hear this has been a frustrating experience. It wouldn't surprise me if the MySQL drivers are a little behind. I know they were working at one point but I've mostly used Solr on actual production sites and test coverage for the actual drivers is still not great. I'll look into this further as soon as I can. Thanks for the heads up.

markguinn commented

This looks like it may be less a problem with the module and more a problem with either your model setup and/or the way the ORM interacts with it. From your SQL above, it looks like the ORM at least THINKS that many of the fields are duplicated between the Product and Tour tables/classes - Title, InternalItemID, BasePrice, etc.

This module is just using the https://github.com/silverstripe-labs/silverstripe-fulltextsearch module, so it's also possible that there's something in the way the fields are defined that's confusing either my module or that module.

Would you be up for posting or emailing me the code for your Tour model so I can see what's going on there? In the mean time you should be able to use the ShopSearchSimple adapter instead of ShoSearchMysql.

hex0id commented

Here's a part of "Tour" model that should be relative to what you're asking (I feel I am wrong when I add same fields to the $db, but a while ago I got problems with not having them explicitly listed, don't remember what the problem was exactly, maybe it's safe to remove them from there):

   private static $db = array (
        'InternalItemID' => 'Varchar(30)', //ie SKU, ProductID etc (internal / existing recognition of product)
        'CostPrice' => 'Currency', // Wholesale cost of the product to the merchant
        'BasePrice' => 'Currency', // Base retail price the item is marked at.
        'Featured' => 'Boolean',
        'AllowPurchase' => 'Boolean',
        'Popularity' => 'Float', //storage for ClaculateProductPopularity task
    );

    private static $has_many = array (
        'Days' => 'TourPeriod'
    );

    private static $many_many = array(
        'Destinations' => 'Destination'
    );

    private static $belongs_many_many = array (
        'Activities' => 'Activity'
    );

    private static $defaults = array(
        'AllowPurchase' => true,
        'ShowInMenus' => false
    );

    private static $summary_fields = array(
        'InternalItemID','Title'
    );

    private static $searchable_fields = array(
        'InternalItemID','Featured'
    );

    private static $field_labels = array(
        'InternalItemID' => 'SKU',
        'Title' => 'Title'
    );

    private static $casting = array(
        'Price' => 'Currency',
        'PerPersonPrice' => 'Currency',
        'getPerPersonPrice' => 'Currency'

    );

    //private static $hide_ancestor = 'Product';

    private static $singular_name = "Tour";
    private static $plural_name = "Tours";
    private static $icon = 'shop/images/icons/package';
    private static $default_parent = 'Destination';
    private static $default_sort = '"Title" ASC';

    private static $global_allow_purchase = true;
    private static $allow_zero_price = false;
    private static $order_item = "Tour_OrderItem";
    private static $min_opengraph_img_size = 0;

    private static $indexes = array(
        'Featured' => true,
        'AllowPurchase' => true,
        'InternalItemID' => true,
        'SearchFields' => array(
            'type' => 'fulltext',
            'name' => 'SearchFields',
            'value' => 'Title',
        )
    );
markguinn commented

Wow, this is confusing. You do have duplicated fields here as I thought, but Title is not one of them which is the one causing the error. Could you check your tables and see if SiteTree_Live, Page_Live, Product_Live, or Tour_Live have an extra Title field. There's got to be another place that field is showing up and if you remove it I think this will start working. Do you happen to have Title duplicated in Page or an extension on Product?

hex0id commented

Yes I know it's confusing, and I know why. I got so many different problems at the start, even before I went to report this one, I tried to fix them, so I got this somewhere on my way:

[User Error] Couldn't run query: CREATE TABLE "Tour" ( "ID" int(11) not null auto_increment, "Lat" decimal(9,5) not null default 0.00000, "Lng" decimal(9,5) not null default 0.00000, "Address" varchar(255) character set utf8 collate utf8_general_ci, "Suburb" varchar(64) character set utf8 collate utf8_general_ci, "State" varchar(64) character set utf8 collate utf8_general_ci, "Postcode" varchar(10) character set utf8 collate utf8_general_ci, "Country" varchar(2) character set utf8 collate utf8_general_ci, "VFI_Price" decimal(9,2) not null default 0.00, "VFI_Categories" varchar(255) character set utf8 collate utf8_general_ci, fulltext "SearchFields" (Title), index "VFI_Price" ("VFI_Price"), index "VFI_Categories" ("VFI_Categories"), primary key (ID) ) ENGINE=MyISAM Key column 'Title' doesn't exist in table

To solve it I thought the easiest would be just to add Title to my statics (and dev/build). Well in the end I just removed it back again and forgot, I am sorry, it really was in the DB at the moment of my report.

What I did now (in sake of a clean experiment) is dropped all the Product_* and Tour_* tables and ran a /dev/build. The error above is what made all this confusion...

BTW I've cleaned out all duplicate stuff from Tour class, here it is:

class Tour extends Product {

    private static $has_many = array (
        'Days' => 'TourPeriod'
    );

    private static $many_many = array(
        'Destinations' => 'Destination'
    );

    private static $belongs_many_many = array (
        'Activities' => 'Activity'
    );

    private static $summary_fields = array(
        'InternalItemID','Title'
    );

    private static $field_labels = array(
        'InternalItemID' => 'SKU'
    );

    private static $casting = array(
        'Price' => 'Currency',
        'PerPersonPrice' => 'Currency'
    );

    private static $singular_name = "Tour";
    private static $plural_name = "Tours";
    private static $icon = 'shop/images/icons/package';
    private static $default_parent = 'Destination';

    private static $order_item = "Tour_OrderItem";

    private static $indexes = array(
        'SearchFields' => array(
            'type' => 'fulltext',
            'name' => 'SearchFields',
            'value' => 'Title',
        )
    );
...

Obviously those indexes must go into SiteTree class which originally has a Title field, I've wrote a small extension:

<?php

class CustomSiteTree extends DataExtension {
    private static $indexes = array(
        'SearchFields' => array(
            'type' => 'fulltext',
            'name' => 'SearchFields',
            'value' => 'Title',
        )
    );
}

Now previous problem is gone, new one tho:

[User Error] Uncaught Exception: Object->__call(): the method 'getresults' does not exist on 'ShopSearchForm'
GET /home/SearchForm?q=asd&f%5BParentID%5D=&action_results=Go&flushtoken=9d43e7913c55751ff7158bbe847f1c33&flush=all
Line 761 in .../framework/core/Object.php

and here's the trace:

Trace
Object->__call(getResults,Array) 
ContentControllerSearchExtension.php:44
ShopSearchForm->getResults() 
ContentControllerSearchExtension.php:44
ContentControllerSearchExtension->results(Array,ShopSearchForm,SS_HTTPRequest) 
call_user_func_array(Array,Array) 
Object.php:729
Object->__call(results,Array) 
Form.php:374
HomePage_Controller->results(Array,ShopSearchForm,SS_HTTPRequest) 
Form.php:374
Form->httpSubmission(SS_HTTPRequest) 
RequestHandler.php:288
RequestHandler->handleAction(SS_HTTPRequest,httpSubmission) 
RequestHandler.php:200
RequestHandler->handleRequest(SS_HTTPRequest,DataModel) 
RequestHandler.php:222
RequestHandler->handleRequest(SS_HTTPRequest,DataModel) 
Controller.php:153
Controller->handleRequest(SS_HTTPRequest,DataModel) 
ContentController.php:198
ContentController->handleRequest(SS_HTTPRequest,DataModel) 
ModelAsController.php:78
ModelAsController->handleRequest(SS_HTTPRequest,DataModel) 
Director.php:367
Director::handleRequest(SS_HTTPRequest,Session,DataModel) 
Director.php:153
Director::direct(/home/SearchForm,DataModel) 
main.php:176
markguinn commented

I'll look into this error above. Just as a side note - you can actually add those indexes to SiteTree without an extension using the yaml config. Again, really sorry you've had such a rough experience getting up and going! Thanks for pushing through.

markguinn commented

Could you make sure your shop search module is up to date (composer update markguinn/silverstripe-shop-search should do it as long as you're on dev-master)?

hex0id commented

Yeah, it was. Here's the log of last update:

Entars-MacBook:public alex$ composer update
Loading composer repositories with package information
Updating dependencies (including require-dev)
  - Updating burnbright/silverstripe-omnipay 1.2.x-dev (a63d39d => 4f91579)
    Checking out 4f9157926109c1598ca83c6cc2a97590a4f2e506

  - Updating silverstripe-australia/addressable dev-master (b305bcc => 3d4be6a)
    Checking out 3d4be6a68cbeea4ef4c10f655a07d0bc86cf39c8

  - Updating silverstripe-australia/gridfieldextensions dev-master (fe13e85 => 1d25be7)
    Checking out 1d25be7bdd286e589370f82f0526a309ddf226e0

Writing lock file
Generating autoload files
markguinn commented

It looks like you've overridden the results method on your HomePage_Controller. Is that true? I moved "results" into ShopSearchForm so that it wouldn't clash with $Results in the template.

I think your options are:

  1. Remove HomePage_Controller::results and use the extension hooks in ShopSearchForm::results (there should be one before the search and one after) to do any customization of the search results.
  2. Change your template to use $Matches instead of $Results (both are present and are identical in the data returned from results() so you can just rename the template variable everywhere it shows up).
hex0id commented
  1. unfortunately nothing is overridden neither in HomePage_Controller nor in Page_Controller
  2. even when I change $Results to $Matches, I still get the same error. here's the output when I do search on "Destination" page which is a direct subclass of ProductCategory:
[User Error] Uncaught Exception: Object->__call(): the method 'getresults' does not exist on 'ShopSearchForm'
GET /sports/SearchForm?q=asd&f%5BParentID%5D=&action_results=Go&flush=all&flushtoken=6f40624d205507fcb86b4b2ec478bf52
Line 761 in .../public/framework/core/Object.php

Trace:

Trace
Object->__call(getResults,Array) 
ContentControllerSearchExtension.php:44
ShopSearchForm->getResults() 
ContentControllerSearchExtension.php:44
ContentControllerSearchExtension->results(Array,ShopSearchForm,SS_HTTPRequest) 
call_user_func_array(Array,Array) 
Object.php:729
Object->__call(results,Array) 
Form.php:374
Destination_Controller->results(Array,ShopSearchForm,SS_HTTPRequest) 
Form.php:374
Form->httpSubmission(SS_HTTPRequest) 
RequestHandler.php:288
RequestHandler->handleAction(SS_HTTPRequest,httpSubmission) 
RequestHandler.php:200
RequestHandler->handleRequest(SS_HTTPRequest,DataModel) 
RequestHandler.php:222
RequestHandler->handleRequest(SS_HTTPRequest,DataModel) 
Controller.php:153
Controller->handleRequest(SS_HTTPRequest,DataModel) 
ContentController.php:198
ContentController->handleRequest(SS_HTTPRequest,DataModel) 
ModelAsController.php:78
ModelAsController->handleRequest(SS_HTTPRequest,DataModel) 
Director.php:367
Director::handleRequest(SS_HTTPRequest,Session,DataModel) 
Director.php:153
Director::direct(/sports/SearchForm,DataModel) 
main.php:176
markguinn commented

Ah, now I see. ContentControllerSearchExtension is part of cms and it's what you use for a traditional SS content search. Is it true that you have that enabled somewhere in your mysite/_config? If so I bet disabling it and flushing will fix your problem. Sorry, I was mistaking that for ShopSearchControllerExtension which essentially does the same thing.

hex0id commented

Mark,

I am happy to tell you that after I disabled:

FulltextSearchable::enable();

Search started to work! Thank you very much for this, it was not obvious at all :) Anyways, I think this is worth to add into docs, so everybody who use standard search functionality would never face this problem after me.

Thanks!

markguinn commented

Very very glad to hear! I agree about the docs.

bummzack commented

@hex0id Could you elaborate how you added Title to your SearchFields index when the product doesn't have a Title? If I add an index to SiteTree (where Title and Content) reside and an index to Product (to add the product specific fields), then the MySQL adapter only searches the indexes defined for Product.

adrian-stein commented

Just thought I would also add that I am having the same issue of the method 'getresults' does not exist on 'ShopSearchForm' and removing FulltextSearchable::enable(); from my config file returned results.

I still have an issue with my form having the following action action="/Page_Controller/SearchForm". Have you guys come across this before. It should be posting back to the current page name and not the controller name??