How to limit MySQL results based on a user quota

Question

today my SQl problem is how to limit some query results based on a user quota.

here is the use case :

a user owns 1...N websites which have 1...N webpages
a user has a scan_quota which allow him (or not) to visit his webpages

users

+-----+------------+
| id  | scan_quota |
+-----+------------+
|   1 |          0 |
|   2 |         10 |
|   3 |         20 |
+-----+------------+

websites

+-----+---------+------------------------------------------------+
| id  | user_id | url                                            |
+-----+---------+------------------------------------------------+
|   1 |       1 | http://www.site1.com                           |
|   2 |       2 | http://www.site2.com                           |
|   3 |       3 | http://www.site3.com                           |
+-----+---------+------------------------------------------------+

webpages

+-------+------------+--------------------------------------+---------------------+
| id    | website_id | url                                  | last_scan_date      |
+-------+------------+--------------------------------------+---------------------+
| 1     |          1 | http://wwww.site1.com/page1          | 2015-07-02 21:00:56 |
| 2     |          2 | http://wwww.site2.com/page1          | 2015-07-02 21:01:36 |
| 3     |          3 | http://wwww.site3.com/page1          | 2015-07-02 21:00:32 |
+-------+------------+--------------------------------------+---------------------+

Every week I want to get a list of webpages urls who have to be scanned based on user scan_quota.

With this simple query, I can get ALL pages :

SELECT us.id, ws.user_id, wp.id, wp.website_id, wp.url 
FROM users us, webpages wp, websites ws 
  WHERE us.id = ws.user_id 
    AND wp.last_scan_date < '2015-10-08' 
    AND ws.id = wp.website_id 
  ORDER BY wp.website_id ASC;

But as soon as I want to limit results based on user scan_quota I get lost because a global LIMIT won't acheive what I want and I don't know how I could use JOIN (INNER or LEFT) to reach my goal.

I've created a SQL Fiddle to play easily with my use case.

Thanks for your suggestions and help !

SOLUTION NOT OPTIMIZED

In a first query I extract user id and scan quota and then loop over them to build my final query using union all (which allows to use a LIMIT per user):

$query .= "(SELECT ws.user_id, wp.id, wp.website_id, wp.url FROM webpages wp, websites ws WHERE ws.user_id = ".$user_id." 
                    AND wp.last_scan_date < '2015-10-08' 
                    AND ws.id = wp.website_id LIMIT ".$scan_pages.") union all ";

If you have a way to group those 2 queries in one or an optimized one, let's share.

I'm also trying to use variables and sub queries (like i this example : https://stackoverflow.com/a/13187418/2295192) but with no luck for now ...

See http://stackoverflow.com/questions/2129693/using-limit-within-group-by-to-get-n-results-per-group for how to get `N` results per group. The only difference in your case is that `N` is not a hard-coded number, you get it by joining with the `users` table and using `users.scan_quota`. — Barmar, Oct 08 '15 at 08:14

Dhvani Purohit · Answer 1 · 2015-10-13T09:06:26.307

1

Please Use this Query and you will get your desired Result. You don't need to add your userID. For each user you would be able to set limit as per Scan_quota.

SELECT
  ws.user_id,
  wp.id AS webPageID,
  wp.website_id,
  @page_counter :=IF (
               (wp.website_id != "") ,
                @page_counter + 1,
                @page_counter
               ) AS totalwebpages,
  wp.url
FROM
  (SELECT @page_counter := 0) p,
  webpages AS wp
  LEFT JOIN websites AS ws ON ws.id = wp.website_id
  LEFT JOIN users AS us ON us.id = ws.user_id
  LEFT JOIN (
    SELECT
    @limit_scanQuota := @limit_scanQuota +sum(users.scan_quota) as limitScanQuota,
    users.id
   FROM
    (SELECT @limit_scanQuota := 0) s,
    users
     GROUP BY
    users.id
    ) AS limitQuota ON limitQuota.id = us.id    
WHERE
   date(wp.last_scan_date) < '2015-10-08'
  AND @page_counter<limitQuota.limitScanQuota
GROUP BY
  wp.id
ORDER BY
  wp.id ASC

edited Oct 13 '15 at 09:06

answered Oct 08 '15 at 13:15

Dhvani Purohit

36
4

Thanks for giving me a query structure more appropriate but I still don't see how to limit results per user based on scan quota ... – hugsbrugs Oct 09 '15 at 10:09
You want to set limit for user based on scan quota.. you mean if scan quota would be 10 for that user then you want to set limit = 10 for that user ? do you want like that? – Dhvani Purohit Oct 09 '15 at 10:43
Yes that's it ! if a user has a scan quota set to 0 then no results should be returned for this user. If a user has a scan quota set to 10 but have 20 webpages then only 10 should be returned. – hugsbrugs Oct 09 '15 at 11:40
Okay, I understood.. I have Edited my Answer.. Please check that i think you will get your result. – Dhvani Purohit Oct 09 '15 at 12:20
Yes ! your query gives expected answer for user_id = 2, but what if I want to get results for all users in one query ? Do we need to LEFT JOIN on users table since we fix user_id ? What would you think about wrapping your answer in another select on users table, where user_id will be another variable ? Thanks for helping ! – hugsbrugs Oct 09 '15 at 13:00
Yes.. we can do that.. :) Please check my Edited Query.. With this you can get all results per user according to Scan_quota.. – Dhvani Purohit Oct 13 '15 at 09:07

How to limit MySQL results based on a user quota

1 Answers1