Databases
StackOverflow2013
Postsdbo
POfull text search, filter results for best match
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POfull text search, filter results for best match
    primarykey
    data
    text
    <p>I have 3 tables that link up. Restaurants, Cuisines and Cuisine Type. A Restaurant can sell many cuisines of food (that's bad wording but you get idea?)</p> <p>So I have Full text setup on Restaurant:Name, CityTown, Postcode and on CuisineType:Name</p> <p>I have one searchbox on my home page and as the user types results are filtered to best match.</p> <p>Here are a few examples: SearchText= "Royal D Ed" Now there is a row in Restaurant for a place named Royal Dynasty and the town is Edinburgh. But my top result back is for somewhere wher town starts with D aswell as name. This is not the best match.</p> <p>I will show you my stored proc, prototype so deriveed names are a bit hackish.</p> <pre><code>ALTER PROCEDURE [dbo].[RestaurantsFullText] @searchText nvarchar(255) AS SELECT b.*, COALESCE(akt2.[Rank],0) / 30 + COALESCE(akt1.[Rank],0) / 30 + COALESCE(akt.[Rank],0) / 30 + COALESCE(bkt.[Rank],0) as rankCount FROM Restaurants b left JOIN Cuisines c on b.Id = c.RestaurantId left join CuisineType a ON c.CuisineId = a.id left JOIN containstable(Restaurants, Name, @searchText) bkt ON b.id = bkt.[Key] left JOIN containstable(CuisineType, Name, @searchText) akt ON a.id = akt.[Key] left JOIN containstable(Restaurants, Postcode, @searchText) akt1 ON b.id = akt1.[Key] left JOIN containstable(Restaurants, citytown, @searchText) akt2 ON b.id = akt2.[Key] where COALESCE(akt2.[Rank],0) / 30 + COALESCE(akt1.[Rank],0) /30 + COALESCE(akt.[Rank],0) / 30 + COALESCE(bkt.[Rank],0) &gt; 5 ORDER BY COALESCE(akt2.[Rank],0) / 30 + COALESCE(akt1.[Rank],0) / 30 + COALESCE(akt.[Rank],0) / 30 + COALESCE(bkt.[Rank],0) asc </code></pre> <p>I think the problem lies in the joins and the way rank is calculated.</p> <p>I want it so that if I pass in "Royal Dynasty Edinburgh d" then Royal Dynasty is still the best match.</p> <p>Since it is a filter then matches for towns in other cities starting with d should not be returned.</p> <p>I would be extremely greatrful for help on this.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMilen A. Radev
    1. USddd
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POfull text search, filter results for best match
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POfull text search, filter results for best match
      1. USblu
      1. VTFavorite
    3. VO
      singulars
      1. POfull text search, filter results for best match
      1. USMatthew Dresser
      1. VTFavorite
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload