CODEWITHSUNDEEP
javascriptmysqlsql
Jason
Jason

Reputation: 1817

SQL query bug - suggestions for fix?

I have an interesting bug and interested to see possible solutions. Here's the situation: I have a user table (user_id, first_name, last_name). I have an address table (user_id, address). It's possible for 1 user to have multiple addresses.

I want a set of results like: {'user':user_id, 'address':address_array} // if a user has more than 1 address, it's grouped.

I've done this by fetching everything sorted by user_id, then traverse the results.

for (every item in results)
{
    if (current result.user_id equal to previous one)
    {
        append the address to previous user
    }
    else
    {
        add the new one to my return array
    }
}

This seems to work fine, except now I want to introduce LIMIT. e.g, I only want the first 20 users. When the client presses NEXT, it skips the first 20 and grabs the next 20 records.

Issue is:

record 19: user 19 has address 1

record 20: user 19 has address 2

record 21: user 19 has address 3

record 22: user 19 has address 4

The way I've done it now, the first batch of 20 will only show that user 19 only has 2 addresses. Then, when the client asks for the next 20 results, user 19 will show up again with 2 other addresses.

Additionally, I can't do array.length to tell the database how many records to skip. I would need to count now many addresses there are. Seems like not an elegant way to do it as the code needs to understand how the database is laid out.

Any thoughts?

Upvotes: 0

Views: 419

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I think you want a group by with group_concat() and limit:

SELECT u.user_id, GROUP_CONCAT(a.address) as addresses
FROM user u JOIN
     address a
     ON u.user_id = a.userid
GROUP BY u.user_id
ORDER BY u.user_id
LIMIT 0, 20;

Upvotes: 2

Barmar
Barmar

Reputation: 782107

Join with a subquery that contains LIMIT:

SELECT userid, address
FROM (SELECT user_id
      FROM user
      ORDER BY user_id
      LIMIT 0, 20) AS u
JOIN address AS a ON u.user_id = a.userid

Replace 0 with 20 for the second group, and so on.

Upvotes: 2

Related Questions