mySQL Query w/ fulltext index search, return X number of results IF they have data in another table

Question

so, it's a hard to phrase question:

I would like to be able to limit the amount of results returned while searching in a fulltext index.

simply using LIMIT 5 does not do what I need it to.

I am searching with this query:

SELECT * FROM people 
WHERE MATCH (name, email) AGAINST ('$val*' IN BOOLEAN MODE) 
ORDER BY MATCH(name, email) AGAINST('$val*') 
DESC

I need to add some sort of IF statement to this query to return proper rows. Right now this returns the name and email of (let's say) 10 people. Only 6 of those people have "spots", meaning they've actually done something on the site. The spots are counted in another table. If those people have done something (have 1+ spots) I'd like them to show in the result, whereas the people who have not done anything, I'd like to eliminate them from the search.

So, in lamens terms (because I don't know how to technically do this) I need the search to say

SELECT * FROM people 
WHERE MATCH (name, email) AGAINST ('$val*' IN BOOLEAN MODE) 
ORDER BY MATCH(name, email) AGAINST('$val*') 
---as long as this person has at least 1 spot---
DESC

The tables are set up like this:

people
|id | name           | email               |
|---|----------------|---------------------|
|01 | John Smith     | jsmith@domain.com   |
|02 | Johnny Johnson | Ejohnson@domain.com |
|03 | Jon Aten       | name@domain.com     |
 ...


spots
|id | user_id | spot_number|
|---|---------|------------|
|01 |      01 |         01 |
|02 |      01 |         03 |
|03 |      01 |         06 |
|04 |      01 |         12 |
|05 |      01 |         04 |
|06 |      03 |         02 |
|07 |      03 |         15 |
|08 |      03 |         31 |
|09 |      01 |         12 |
|10 |      01 |         10 |

As you can see, Johnny (user ID 2) does not have any spots in the spots table. So I would like to be able to search for jo and only return user 1 and 3. Would this just create a slower query? should I limit the number of results afterwards? I will be searching through potentially millions of records eventually. I'm not sure where to go from here - any help is appreciated!

Answer 1

you can join the tables together based on the user_id in the query, that will get you the list of people common to both tables:

SELECT * FROM people p, spots s
WHERE MATCH (p.name, p.email) AGAINST ('$val*' IN BOOLEAN MODE) 
AND
p.id = s.user_id  
ORDER BY MATCH(name, email) AGAINST('$val*') 
DESC
LIMIT 5

if you want the list of users with more than a number of spots (regardless of spot_number value) you can do a 'group by' query on the rows in the people table with a count of the corresponding rows in the spots table, then use sql's 'having' clause to filter on the number of counted rows.

For example to find all the names of people with more than one spot (aka greater than 1 row in the spots table):

SELECT p.name, count(s.spot_number) c FROM people p, spots s 
WHERE MATCH (p.name, p.email) AGAINST ('$val*' IN BOOLEAN MODE) 
AND p.id = s.user_id 
group by p.name having c > 1
ORDER BY MATCH(name, email) AGAINST('$val*') DESC 
LIMIT 5

Yes doing joins will impact performance, make sure you have spots.user_id column indexed.

Answer 2

INNER JOIN is your hero:

SELECT * FROM people 
INNER JOIN spots ON people.id = spots.user_id
WHERE MATCH (name, email) AGAINST ('$val*' IN BOOLEAN MODE) 
ORDER BY MATCH(name, email) AGAINST('$val*') 
DESC

Memorizing your SQL join Venn diagrams is super useful: sql joins as venn diagram