CODEWITHSUNDEEP
mysql
Steve Taylor
Steve Taylor

Reputation: 301

Better solution to MySQL nested select in's

Currently I have two MySQL tables

Properties

id    name 
1     Grove house
2     howard house
3     sunny side

Advanced options

prop_id    name
1          Wifi
1          Enclosed garden
1          Swimming pool
2          Swimming pool

As you can see table two contains specific features about the properties

When I only have max 3 options the query below worked just fine. (maybe a little slow but ok) now things have expanded somewhat and i have a max of 12 options that it is possible to search by and its causing me some major speed issues. The query below is for 8 options and as you can see its very messy. Is there a better way of doing what I'm trying to achieve?

SELECT * FROM properties WHERE id in (
select prop_id from advanced_options where name = 'Within 2 miles of sea or river' and prop_id in ( 
    select prop_id from advanced_options where name = 'WiFi' and prop_id in ( 
        select prop_id from advanced_options where name = 'Walking distance to pub' and prop_id in ( 
            select prop_id from advanced_options where name = 'Swimming pool' and prop_id in ( 
                select prop_id from advanced_options where name = 'Sea or River views' and prop_id in ( 
                    select prop_id from advanced_options where name = 'Pet friendly' and prop_id in ( 
                        select prop_id from advanced_options where name = 'Open fire, wood burning stove or a real flame fire-place' and prop_id in ( 
                            select prop_id from advanced_options where name='Off road parking') 
                        ) 
                    ) 
                ) 
            ) 
        ) 
    ) 
)

Upvotes: 0

Views: 103

Answers (4)

Michael
Michael

Reputation: 2631

Like Mike Brant suggest I would consider altering your datamodel to a limit to set and creating a column for each of these in your properties table. But some times the boss comes: "We also need 'flatscreen tv'" and then you have to go back to the DB and update the scheme and your data access layer.

A way to move this logic somehow out if the database it to use bitwise comparison. This allows you to make simple queries, but requires a bit of preprocessing before you make your query.

Judge for yourself.

I've put everything in a test suite for you here sqlfiddle

The basic idea is that each property in your table has an id that is the power of 2. Like this:

INSERT INTO `advanced_options` (id, name)
VALUES
 (1, 'Wifi'),
 (2, 'Enclosing Garden'),
 (8, 'Swimming Pool'),
 (16, 'Grill');

You can then store a single value in your properties table buy adding up the options:

Wifi + Swimming Pool = 1 + 8 = 9

If you want to find all properties with wifi and a swimming pool you then do like this:

SELECT * FROM `properties` WHERE `advanced_options` & 9 = 9

If you just wanted swimming pool this would be it:

SELECT * FROM `properties` WHERE `advanced_options` & 8 = 8

Go try out the fiddle

Upvotes: 3

Adolfo Perez
Adolfo Perez

Reputation: 2874

Could you do something like this?:

select p.*,count(a.prop_id) as cnt
from properties p
inner join advanced_options a on a.prop_id = p.id
where a.name in ('Enclosed garden','Swimming pool')
group by p.name
having cnt = 2

That query would get all the properties that have ALL of those advanced_options...

I would also suggest normalizing your tables by creating a separate table Called Advanced_option (id,name) where you store your unique Option values and then create a junction entity table like Property_x_AdvancedOption (fk_PropertyID, FK_AdvancedOptionID) that way you use less resources and avoid data integrity issues.

Upvotes: 0

Alain Collins
Alain Collins

Reputation: 16362

Join back to the advanced_options table multiple times. Here's a sample with 2 (lather, rinse, repeat).

select o1.prop_id
from advanced_options o1
inner join advanced_options o2 on o1.prop_id = o2.prop_id and o2.name = "WiFi"
where o1.name = 'Within 2 miles of sea or river'

Upvotes: 0

Mike Brant
Mike Brant

Reputation: 71384

You really need to consider a schema change to your table. It seems that advanced options in and of themselves don't have any properties, so instead of an advanced_options table that is trying to be a many-to-many JOIN table, why not just have a property_options table with a field for each "options". Something like this

|prop_id  | wifi |  swimming_pool  |  etc..
-----------------------------------
|   1     |  0   |        1        |
|   2     |  1   |        0        |

Here each field is a simple TINYINT field with 0/1 boolean representation.

To where you could query like:

SELECT * FROM properties AS p
INNER JOIN property_options AS po ON p.id = po.prop.id
WHERE wifi = 1 AND swimming_pool = 1 ....

Here you would just build your WHERE clause based on which options you are querying for.

There actually wouldn't be any need to even have a separate table, as these records would have a one-to-one relationship with the properties, so you could normalize these fields onto you properties table if you like.

Upvotes: 0

Related Questions