Mysql Query limitation

I use a query like :

$querym = mysql_query("SELECT * FROM allmembers a LEFT JOIN favorites f ON (f.memberid=a.memberid) order by f.date desc LIMIT 10");

while($row = mysql_fetch_array($querym,MYSQL_ASSOC)) {
$dataArray[$row['memberid']][$row['favoriteid']]=$row;
} 

My purpose is getting 10 members with their last 5 favorites in an array. but as you can guess this query getting 10 row included with favorites. That means if a member have 15 favo开发者_运维问答rites it only gets one member with 10 favorites instead of 10 members with his favorites.

I couldn't find an easy way to limit getting favorites for each member in that query. How can I limit?

Thanks in advance

1. This is not MYSQL FETCH ARRAY LIMITATION, this is your QUERY limitation.

2. Try something of your own on these lines :

   SELECT memberid,group_concat(favorites_field) FROM allmembers a LEFT JOIN favorites f ON (f.memberid=a.memberid) group by a.memberid order by f.date desc LIMIT 10
   

This query may give you a better approach

SELECT * FROM favorites f where f.memberid in (SELECT * FROM allmembers limit 10) ORDER BY f.date desc 

although you should limit the 5 favorites later, in php or whatever. This could work if you don't expect to have 10.000 favorites for one member (basically because you will be getting 10.000+ ROWS just to display, at maximum, 10*5 = 50 rows.)

Alternatively, you can do a query for each member, limiting it to 5 results... (this would mean doing 11 queries in total...)

The following query will do what you want:

SELECT
    a.id as member_id, 
    a.name,
    f1.id as favorite_id, 
    f1.link
FROM 
    allmembers a
    JOIN favorites f1 ON f1.member = a.id
    LEFT JOIN favorites f2 ON f2.member = f1.member AND f2.date < f1.date
GROUP BY 
    a.id, a.name, f1.id, f1.link
HAVING 
    COUNT(f2.id) < 10
ORDER BY
    a.name, f1.id

It assumes the following database schema:

allmembers:
    id INT
    name VARCHAR

favorites:
    id INT
    member INT
    link VARCHAR

Obviously you need to update the query according to your own database schema.

You have many ways to do that, and they can all be performant up to a limit, what i'd do in this specific case is this:

Get the favorite id's in a multiple left join as a seperate field, you can then easily for(...) the row array for each field and retrieve the id. The Query would LOOK LIKE this. Adapt it to your own use:

SELECT 
    a.*,
    f1.favoriteid as f1id,
    f2.favoriteid as f2id,
    f3.favoriteid as f3id,
    f4.favoriteid as f4id,
    f5.favoriteid as f5id
FROM allmembers a 
    LEFT JOIN favorites f1 ON (f.memberid=a.memberid) 
    LEFT JOIN favorites f2 ON (f.memberid=a.memberid) AND f1.favoriteid <> f2.favoriteid
    LEFT JOIN favorites f3 ON (f.memberid=a.memberid) AND f1.favoriteid <> f3.favoriteid AND f2.favoriteid <> f3.favoriteid
    LEFT JOIN favorites f4 ON (f.memberid=a.memberid) AND f1.favoriteid <> f4.favoriteid AND f2.favoriteid <> f4.favoriteid AND f3.favoriteid <> f4.favoriteid
    LEFT JOIN favorites f5 ON (f.memberid=a.memberid) AND f1.favoriteid <> f5.favoriteid AND f2.favoriteid <> f5.favoriteid AND f3.favoriteid <> f5.favoriteid AND f4.favoriteid <> f5.favoriteid

order by f.date desc LIMIT 10

Using this method you can also read any information from the favorites table or even left join on another table X number of times to get more information regarding that favorite. As long as you setup correct indexes, this method is extremely fast even with thousands of members and nearly millions of favorites.

You can also apply this strategy to many other scenarios. For example, we work with WordPress here at work and lots of information for users are kept as meta fields, so selecting one big table is impossible unless you perform this method.

Good luck