Limit by condition count OR in MySQL

Question

I am creating a web application that uses geolocation, and I am whipping up the view of "nearby places." This is a fairly simple logic: it shows the next 5 places that are stored in the database. Perfect.

The trick is that I want it to return the next 5 places or all places within two miles, whichever is greater. In other words, I want the user to be able to see at least all the places within two miles, but if there are no 5 places in this radius, I want them to show the next 5.

Let them be used for sample data sets. Set 1:

| id | name | distance | +----+----------------------+-----------+ | 3 | Earl of Sandwich | 0.3 | | 4 | Nails 'n More | 0.8 | | 22 | City Hotel | 1.7 | | 5 | Mighty Medicine | 2.1 | | 25 | Wonder Wings | 2.5 | | 6 | Jean Warehouse | 2.7 | | 9 | Ship Safe & Speedy | 2.9 | | 2 | Bagel Bonus | 4.1 | +----+----------------------+-----------+ 

Install 2:

 | id | name | distance | +----+----------------------+-----------+ | 3 | Earl of Sandwich | 0.1 | | 4 | Nails 'n More | 0.2 | | 5 | Mighty Medicine | 0.5 | | 6 | Jean Warehouse | 0.7 | | 9 | Ship Safe & Speedy | 0.9 | | 2 | Bagel Bonus | 1.2 | | 22 | City Hotel | 1.7 | | 25 | Wonder Wings | 2.1 | +----+----------------------+-----------+ 

In the first set, I want to return lines 3, 4, 22, 5, and 25. In the second set, I want to show 3, 4, 5, 6, 9, 2, and 22.

I know that I can just limit the query to, say, 100 places, and go through the result set in PHP for filtering ... but I wonder if there is a more efficient way to do this directly in SQL.

Answer 1

So the way to do this is to run both queries and take the UNION from the sets. It. Actually there is very little performance loss because the first set (which can produce> 5 rows) is already required if the result is really more than 5 rows.

More details - original answer below

To illustrate, instead of using 2 datasets, I just use 2 columns in the same sample table, for which the query is shown below:

 drop table if exists tbl1; create table tbl1 ( id int, name varchar(100), distance1 float, distance2 float ); insert tbl1 values ( 3 , 'Earl of Sandwich ', 0.3, 0.1), ( 4 , 'Nails ''n More ', 0.8, 0.2), ( 22 , 'City Hotel ', 1.7, 1.7), ( 5 , 'Mighty Medicine ', 2.1, 0.5), ( 25 , 'Wonder Wings ', 2.5, 2.1), ( 6 , 'Jean Warehouse ', 2.7, 0.7), ( 9 , 'Ship Safe & Speedy ', 2.9, 0.9), ( 2 , 'Bagel Bonus ', 4.1, 1.2); 

And queries and results:

 /* query 1 */ select id, name, distance1 from ( select * from tbl1 where distance1 <= 2.0 order by distance1) a union select id, name, distance1 from ( select * from tbl1 order by distance1 limit 5) b; /* result 1 */ id;name;distance1 3;Earl of Sandwich ;0.3 4;Nails 'n More ;0.8 22;City Hotel ;1.7 5;Mighty Medicine ;2.1 25;Wonder Wings ;2.5 /* query 2 */ select id, name, distance2 from ( select * from tbl1 where distance2 <= 2.0 order by distance2) a union select id, name, distance2 from ( select * from tbl1 order by distance2 limit 5) b; /* result 2 */ id;name;distance2 3;Earl of Sandwich ;0.1 4;Nails 'n More ;0.2 5;Mighty Medicine ;0.5 6;Jean Warehouse ;0.7 9;Ship Safe & Speedy ;0.9 2;Bagel Bonus ;1.2 22;City Hotel ;1.7 

The performance of this query is as good as it gets.
The first part of the UNION chooses those that are 2 km away. This is necessary since you want all matches.
The next part selects the top five and assuming you have an index, this is trivial to collect. The combination (UNION) of both parts is very fast.

Answer 2

If it were me, I would have a request to return all places ordered by distance. Then in PHP you can go to position 5 in the array (this will be 5th place further because you ordered the request by distance) and check if it is within 2 miles. If he then returns the first 5, and if not, return them all.

Thus, your request will be simple:

 SELECT id, name, distance FROM places ORDER BY distance ASC 

Answer 3

 SELECT * FROM places ORDER BY distance ASC LIMIT 5 

The nearest places will be pulled out first (ascending order), so it does not matter.

LIMIT is the maximum results that can be extracted from the database, so the maximum places that the user gets is 5, maybe less, but no more.

Answer 4

You can definitely use mysql to limit (and recommended) with the order:

 SELECT * FROM `table` ORDER BY `distance` ASC LIMIT 5; 

"ASC" tells the request to sort the lines in ascending order (from minimum to highest).