Hello,
is there any choice to make funtional Autocomplete module with Search API Database search?
Thanks

CommentFileSizeAuthor
#7 1836992-7--support_autocomplete.patch18.46 KBdrunken monkey

Comments

luo8’s picture

luo8 commented

Hello,
any update?

Thanks

adamtong’s picture

adamtong commented

Hi, I want to have the database server working too.

Thank you!

luo8’s picture

luo8 commented

Hello,
is anybody who can improve it?

Thanks

francoud’s picture

francoud commented

I subscribe.

luo8’s picture

luo8 commented

Hello,
any news?

Thanks.

chegor’s picture

chegor commented

Also interested in this.

drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
commented
Title: Autocomplete + Database search ? » Add autocomplete support
Project: Search API Autocomplete » Search API Database Search
Status: Active » Needs review
StatusFileSize
new 1836992-7--support_autocomplete.patch18.46 KB

Thanks for showing your support for and interest in this feature! With 17 followers I guess this really would be a popular addition.

I tried to implement this and came up with the following patch. However, I hit one bad problem with MySQL (I couldn't find out how, e.g., Postgres handles this): it seems you cannot use a temporary table more than once in a single query. Since I store the results of the base query in such a temporary table for performance reasons (very similar to facetting) but would need it matched against all fulltext field tables used in the autocomplete, I had to employ a rather dirty method for handling more than one autocomplete field (fetch the result into an array and use IN with that array instead of a subquery). I really can't say what the effect on performance will be.

So please, especially for that reason, please test the attached patch and see how it performs for the anticipated use cases with your data set.
Code reviews and bug reports are of course also welcome!

drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
commented

Could a few of you please test this?

luo8’s picture

luo8 commented

Hello drunken monkey,
I really appriatate your support, I will check performance during this weekend. I will let you know...
Thanks again

botris’s picture

botris
Primary language Dutch
Location Creuse, France
commented

Just tested with a small site (200 nodes), I see no noticeable difference between having the autocomplete on just the title or both title and body field. So I guess performance is ok at least for small sites.

luo8’s picture

luo8 commented

Hello drunken monkey,
thanks for the feature again..It's working :)
Anyway after checking...I have to compare with Autocomplete feature in Solr version:
-during typing -> long time reponse
-during typing -> sometimes couldn't catch words
-probably due to SQL limitations -> can't catch phrase if you start from second word
-probably due to SQL limitations -> searches can't recognized similar words (Trencin,Trenčín),really big disadvantage!

Is there any choice to solve somehow "SQL limitations" Issues?
Thanks

drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
commented

@ boris sondagh: Thanks for testing, good to hear!

@ luo8: Also thanks for testing, but not so good to hear … My comments:

-during typing -> long time reponse

That can hardly be changed. Since the database hasn't got such features integrated, we have to use a rather complicated way to get results, which is of course also slow.
However, it would be good to know a few things nonetheless:

  • How many nodes/items does your index have?
  • Did you activate both autocomplete methods in the server settings?
  • Did you use more than one field for autocompletion?

-during typing -> sometimes couldn't catch words
-probably due to SQL limitations -> can't catch phrase if you start from second word

What do you mean with those?

-probably due to SQL limitations -> searches can't recognized similar words (Trencin,Trenčín),really big disadvantage!

Try the Transliteration module and related processor. Otherwise I guess this doesn't work for normal searches either, right? Therefore, it's not really relevant to this issue.

luo8’s picture

luo8 commented

Dear drunken monkey,
thanks for answer.
Pls. see answer’s in strong…

-during typing -> long time reponse
That can hardly be changed. Since the database hasn't got such features integrated, we have to use a rather complicated way to get results, which is of course also slow.
However, it would be good to know a few things nonetheless:
• How many nodes/items does your index have?
I was used just 1 index with max 10nodes as example.
• Did you activate both autocomplete methods in the server settings?
Do you mean Database and Solr? Currently I was activated only database method. I don’t have installed Sorl in this version.
• Did you use more than one field for autocompletion?
In some case 2fields.I will check reletaion more depply later on…

-during typing -> sometimes couldn't catch words
-probably due to SQL limitations -> can't catch phrase if you start from second word
What do you mean with those?
I think that these both going about the same Issue. For Example…you have node with this name “Live in London” and if you will try to look for word “liv” there will not be any result(anyway should fine at least “live”) or in second case, if you will try to look for word “Lond” there will not be any result(anyway should fine at least “london”).

-probably due to SQL limitations -> searches can't recognized similar words (Trencin,Trenčín),really big disadvantage!
Try the Transliteration module and related processor. Otherwise I guess this doesn't work for normal searches either, right? Therefore, it's not really relevant to this issue.
I don’t think that this module could be related with this Issue, because If you will have node “Live in Trenčín”..this module will not automatically generated the similar examples to the Search table..

Thanks for your suport.

drunken monkey’s picture

drunken monkey
he/him
Primary language German
Location Vienna, Austria
commented
Status: Needs review » Fixed

I'm sorry this all doesn't work for you. However, I can't reproduce any of these errors, or have already given solutions. Since for boris sondagh and me, sites with 200 nodes work perfectly fine and words are found reliably, there is probably something wrong with your setup.
So, committed. Thanks all for testing!

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.