asked on

need assistance adding textual search to my generic search query

I have a basic search query on my site that allows users to find folks that are near them. The search also contains pieces of info about the user (this is for a bartering website) such as what they have to offer. It works great, but now I want to add onto it that allows for a search string. So if I want to search for the term "books", I should get all users that are offering books. I have two tables that handles offer data, tblOffers, which contains generic data for each offer (offerID is the ID field, offer is the name of the offer) and tblUserOffers, which links an offer to an individual user (offerID matches the tblOffers.offerID and userID links the offer to a user). I've built the following sql with help from a few experts here on EE, and I essentially want to add in logic along the lines of

where tblOffers.Offer LIKE '%chair%'

However, I'm unsure of the best place to put it to maintain efficiency. Here is the full query, any help is greatly appreciated :)

select distinct u.userID, fullName = u.firstName + ' ' + u.lastName, u.displayName, u.city, u.stateProvince, Round( dbo.CalculateDistance(-71.43520, 42.32320, z.Longitude, z.Latitude), 2 ) As Distance, 
	profilePicture = case when isNull( u.profilePicture, '' ) = '' then '/images/person.png' else u.profilePicture end, 
	confirmed = case u.confirmed when 1 then '<some text here>' else '' end, 
	u.lastLogin, oa1.offers, uc.offerCount, totalUsers = COUNT( * ) OVER() 
From tblUsers u 
	OUTER APPLY ( 
		SELECT STUFF( (
			SELECT ', ' + offer  	
			FROM tblOffers o 		
				inner join tblUserOffers uo on uo.offerID = o.offerID 	
			WHERE u.userID = uo.userID 
			FOR XML PATH ('') 
		)  , 1, 1, '') 
	) AS OA1 (offers) 
inner join ( SELECT uo.userID , count(*) offerCount FROM tblUserOffers uo GROUP BY uo.userID )  uc ON u.userID = uc.userID 
inner join ZipCodes z on z.zipCode = u.zipCode and 
	u.userID <> 100 and 
	Longitude Between -72.41289 And -70.45751 And 
	Latitude Between 41.59901 And 43.04739 And 
	dbo.CalculateDistance(-71.43520, 42.32320, Longitude, Latitude) <= 50 and 
	status = 1  
order by Distance, u.lastLogin 
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Open in new window

Ares Kurklu

Search is a complex subject if you just want to use sql, would be hard to do fuzzy search, but the 'like' approach should work for a simple search if you want to consider user typos you may want to consider functions like
soundex as well:

https://technet.microsoft.com/en-us/library/ms189282(v=sql.105).aspx

Big Monty

ASKER

For now I just want to stick to using LIKE

Nitin Sontakke

You can add below between line 11 and 12 of your posted code above.

     and o.Offer LIKE '%' + isnull(@searchTerm, '')  + '%'

Open in new window

Here the @searchTerm is a parameter which needs to be passed. I have no idea how exactly you are calling this query. Is this part of a stored procedure? Built dynamically in middle layer and passed on to the database?

As indicated already, searches can become very complicated and eventually you may wish to look into anything between Full Text Search to ElasticSearch!

Big Monty

ASKER

@Nitin:

Thanks for the suggestion. I forgot to mention my initial attempt was exactly what you suggested, and I didn't get the results back I was expecting. My apologies. To answer your other questions, this is part of a stored procedure that I call from my web app. I am eventually going to move to full text search, but for now I just want to have something that's "quick & dirty", that's why I am going this route.

Nitin Sontakke

That's okay. What precise changes did you make and what unit tests did you execute to conclude - didn't get the results back I was expecting...details are very important as the change is really very minor.

Did you insert the exactly same piece of code as it is given by me? You added the parameter? Executed the code in SQL Server Management Studio? Bypass the web as it is not quire required for the testing purpose.

Hope you are with me so far.

Nitin Sontakke

On second thought, copying and pasting sql statement above in SSMS is also fine for testing purpose until proven okay to put in sp.

Big Monty

ASKER

my apologies, I should know better as I am usually the one answering questions...need more caffeine :)

I ran the following sql directly in SSMS:

select distinct u.userID, fullName = u.firstName + ' ' + u.lastName, u.displayName, u.city, u.stateProvince, Round( dbo.CalculateDistance(-71.43520, 42.32320, z.Longitude, z.Latitude), 2 ) As Distance, 
	profilePicture = case when isNull( u.profilePicture, '' ) = '' then '/images/person.png' else u.profilePicture end, 
	confirmed = case u.confirmed when 1 then '<some text here>' else '' end, 
	u.lastLogin, oa1.offers, uc.offerCount, totalUsers = COUNT( * ) OVER() 
From tblUsers u 
	OUTER APPLY ( 
		SELECT STUFF( (
			SELECT ', ' + offer  	
			FROM tblOffers o 		
				inner join tblUserOffers uo on uo.offerID = o.offerID 	
			WHERE u.userID = uo.userID and o.Offer LIKE '%chairs%'
			FOR XML PATH ('') 
		)  , 1, 1, '') 
	) AS OA1 (offers) 
inner join ( SELECT uo.userID , count(*) offerCount FROM tblUserOffers uo GROUP BY uo.userID )  uc ON u.userID = uc.userID 
inner join ZipCodes z on z.zipCode = u.zipCode and 
	u.userID <> 100 and 
	Longitude Between -72.41289 And -70.45751 And 
	Latitude Between 41.59901 And 43.04739 And 
	dbo.CalculateDistance(-71.43520, 42.32320, Longitude, Latitude) <= 50 and 
	status = 1  
order by Distance, u.lastLogin 
OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY

Open in new window

it doesn't matter what I put in for the search term, I always get the same results back. I verified that I only have ONE user with "chairs" listed as an offer.

Nitin Sontakke

my apologies, I should know better as I am usually the one answering questions...need more caffeine :)

Incidently just stubbled upon one thread where you have answered the question about ADODB, so I get that completely. Not an issue.

The query you / we are modifying is only impacting the contents of the offers column (line 4, second one). Are the contents of that are getting changed?

If yes, we can then modify and bring expected filter out. That will limit the rows. A join same as the one above will be required again.

Big Monty

ASKER

ya i typically hang out in the classic asp zone, that's my specialty :)

The query you / we are modifying is only impacting the contents of the offers column (line 4, second one). Are the contents of that are getting changed?

I'm not sure what you're asking here. If you're asking if the returned data is different from when I don't add in the LIKE code, then the answer is no, it's the same data being returned. If I've misunderstood, I'll need some clarification.

For some context, you can have a look at https://www.experts-exchange.com/questions/29017932/SQL-Query-that-uses-count-over-possible-to-use-distinct.html. Scroll down to where PortletPaul gets involved, and you'll see what the query is all about.

ASKER CERTIFIED SOLUTION

Nitin Sontakke

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nitin Sontakke

Had a look at that thread. Thank you! That helps!

You know there is one column called "offers" which is actually the combination of all offers text separated by comma. That's the one i am referring to. When you add the where clause (where we have been attempting so far) the number of rows WILL NOT affect, but the contents of the offers column MUST change.

You may wish try in two diff query windows and try.

Big Monty

ASKER

so this suggestion works, sort of. It's working for some search terms, but not others, where I expect it to. for example, if I run:

select * from tblUserOffers where offerid in ( select offerID from tbloffers where offer like '%wood%' )

Open in new window

I get 5 results returned. However, if I put '%wood%' in the original query, i get nothing returned. I'll have to inspect the data more, i'm guessing that those entries are probably outside of the 50 mile radius, I just want to verify that. Unfortunately I have to jump on something else for work (this is my side project), so I won't be back on it until later tonight.

I appreciate your help. I'll be back on around 7pm EST or so (while I watch my college alma matta hockey team compete in a city tourney called the Bean Pot). I don't expect you to be around, but I think I'm on the right track here and will report back then.

I very much appreciate the help :)

Cheers
Josh

Nitin Sontakke

Okay. Best of luck!

I am in India and was going to bed after this comment anyway, so thanks!

Big Monty

ASKER

many thanks for the assistance!