CODEWITHSUNDEEP
mysql
rkmax
rkmax

Reputation: 18133

Complex Query with related tables - Optimal Solution

The Schema:

I have 3 Tables:

initially all users has no features

Example data:

User:

| id | name |
| 1  | Rex  |
| 2  | Job  |

Feature:

| id | name |
| 1  | Eat  |
| 2  | Walk |

User_has_Feature:

| id | user_id | feature_id | have_feature |
| 1  | 1       | 1          | true         |
| 2  | 1       | 1          | true         |
| 3  | 2       | 2          | true         |
| 4  | 2       | 2          | false        |

The questions are:

Example:

| user_name | feature_name | feature_status |
| Rex       | Eat          | true           |
| Rex       | Walk         | true           |

Example:

| user_name | feature_name | feature_status |
| Job       | Eat          | true           |
| Job       | Walk         | false          |

Some conditions have to be attended

The idea is to export the result to a CSV file

Early Solution

thanks to the answers of (@RolandoMySQLDBA, @Tom Ingram, @DRapp) I found a solution:

SELECT u.name, f.name, IF(uhf.status=1,'YES','NO') as status
FROM user u
  JOIN user_has_feature uhf ON u.id = uhf.user_id
  JOIN feature f ON f.id = uhf.feature_id
  JOIN 
       (
         SELECT u.id as id
         FROM user u
           JOIN user_has_feature uhf ON uhf.user_id = u.id
         WHERE uhf.status = 1
         GROUP BY u.id
         HAVING count(u.id) <= (SELECT COUNT(1) FROM feature)
       ) as `condition` ON `condition`.id = u.id
ORDER BY u.name, f.id, uhf.status

For get records that do not have all the features and for get all record that have all features change:

but I want to know if this is an optimal solution?

Upvotes: 1

Views: 167

Answers (4)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

SELECT
    UNF.*,
    IF(
        (LENGTH(UNF.FeatureList) - LENGTH(REPLACE(UNF.FeatureList,',','')))
        = (FC.FeatureCount - 1),'Has All Features','Does Not Have All Features'
    ) HasAllFeatures
FROM
    (SELECT
        U.name user_name
        GROUP_CONCAT(F.name) Features
    FROM
        (SELECT user_id,feature_id FROM User_has_Feature
        WHERE feature_status = true) UHF
        INNER JOIN User U ON UHF.user_id = U.id
        INNER JOIN Feature F ON UHF.feature_id = F.id
    GROUP BY
       U.name
    ) UNF,
    (SELECT COUNT(1) FeatureCount FROM Feature) FC
;

The UNF subquery returns with all users listed in User_has_Feature and a comma-separated list of the features. The column HasAllFeatures is determined by the number of columns in UNF.FeatureList. In your case, there are two features. If the number of commas in UNF.FeatureList is FeatureCount - 1, then the user has all features. Otherwise, user does not have all features.

Here is a better version that shows all users and whether or not they have all, some or no features

SELECT
    U.name user_name,
    IFNULL(UsersAndFeatures.HasAllFeatures,
    'Does Not Have Any Features')
    WhatFeaturesDoesThisUserHave
FROM
    User U LEFT JOIN
    (
        SELECT
            UHF.user_id id,
            IF(
                (LENGTH(UHF.FeatureList) - LENGTH(REPLACE(UHF.FeatureList,',','')))
                = (FC.FeatureCount - 1),
               'Has All Features',
               'Does Not Have All Features'
            ) HasAllFeatures
        FROM
            (
                SELECT user_id,GROUP_CONCAT(Feature.name) FeatureList
                FROM User_has_Feature INNER JOIN Feature
                ON User_has_Feature.feature_id = Feature.id
                GROUP BY user_id
            ) UHF,
            (SELECT COUNT(1) FeatureCount FROM Feature) FC
    ) UsersAndFeatures
USING (id);

Upvotes: 1

T I
T I

Reputation: 9943

Here's my bash at it

  • create a view of the general information

    CREATE VIEW v_users_have_features AS
 SELECT usr.id, usr.name, feature.name, has_feature.status 
  FROM usr 
  JOIN has_feature ON usr.id = has_feature.user_id
  JOIN feature ON has_feature.feature_id = feature.id;

  • use the view for other queries

    SELECT v_users_have_features.id, v_users_have_features.u_name, v_users_have_features.f_name
 FROM v_users_have_features
 GROUP BY v_users_have_features.id
 HAVING COUNT( v_users_have_features.id ) = (SELECT COUNT( feature.id ) 
                                              FROM feature
                                              WHERE feature.name = v_users_have_features.f_name )

p.s. you may need to adapt (particularly the latter) to your exact requirements you could also omit creating the view and nest it in the FROM clause like in another answer it just seemed handier to create the view

Upvotes: 0

DRapp
DRapp

Reputation: 48169

select
      u.id,
      u.name as User_Name,
      f.name as Feature_Name,
      uhf.feature_Status
   from 
      ( select uhf.user_id, 
               sum( if( uhf.feature_status, 1, 0 ) ) as UserFeatureCount
           from user_has_feature uhf
           group by uhf.user_id ) AllUsersWithCounts

      join
      ( select count(*) as AllFeaturesCount
           from Feature ) AllFeatures
         on AllUsersWithCounts.UserFeatureCount = AllFeatures.AllFeaturesCount

      join user u
         on AllUsersWithCounts.user_id = u.ID

      join user_has_feature uhf
         on AllUsersWithCounts.User_id = uhf.user_id
         join feature f
            on uhf.feature_id = f.id

The above query should get all people that explicitly have ALL features. In order to get those that do NOT have all features, just change the one join from = to <

 on AllUsersWithCounts.UserFeatureCount < AllFeatures.AllFeaturesCount

Upvotes: 1

Tom Anderson
Tom Anderson

Reputation: 47243

Count the number of features. Write a query over users that uses a correlated subquery to find all the features a user has and count them. Make the restriction criterion in the top query the equality of that count and the global number of features.

Can MySQL do correlated subqueries? If not, you might need to use a better database.

Upvotes: -1

Related Questions