Get data from relational table with multiple rows

Question

I have this tables:

blogs

id
name
user_id // author - relational to "users"

users

id
nickname
avatar

suscriptions

id
blog_id // relational to "blogs"
user_id // relational to "users"

The idea is to show on the site something like this:

Blogname
Created By Stack
Suscriptions: Overflow, Easy, Robinson, Porter

I was fetching all data from blogs

SELECT
    b.id,
    b.name,
    b.user_id,
    u.nickname as user_nickname,
    u.avatar as user_avatar
FROM blogs b
LEFT JOIN users u ON b.user_id = u.id

With PHP I created an array with all the blogs ID, so I can use them with IN operator, like this:

SELECT
    s.id,
    s.blog_id,
    u.nickname,
    u.avatar
FROM suscriptions s
LEFT JOIN users u ON s.user_id = u.id
WHERE s.blog_id IN (1, 2, 3)

And then PHP again for sorting the result.

But, is there a better way? I was thinking about using MySQL only and not PHP. Maybe fetch all the data in just one query? Or what would be the best way to get this?

It's important to have in mind that a blog can have several subscriptions.

I found this question from 2012 that is a little similar: JOIN three tables and aggregate data from multiple rows for every DISTINCT row in separate column But the OP says it's really slow, so I'm a little lost right now.

Do you use core php or some frameworks ? – Sulthan Allaudeen Apr 28 '16 at 14:40 — Sulthan Allaudeen, Apr 28 '16 at 14:40
@SulthanAllaudeen only PDO for database handling – Jose Carlos Ramírez Vásquez Apr 28 '16 at 14:55 — Jose Carlos Ramírez Vásquez, Apr 28 '16 at 14:55

score 1 · Answer 1 · answered Apr 28 '16 at 14:35

1

I think what you need is to group all lines generated by the 'join suscriptions' and concatenate them with a comma for presentation.

SELECT
    b.id AS blog_id,
    b.name AS blog_name,
    u.nickname AS user_create_nickname,
    u.avatar AS user_create_avatar,
    GROUP_CONCAT(us.nickname SEPARATOR ', ') AS users_suscritions
FROM blogs b
INNER JOIN users u ON b.user_id = u.id
LEFT JOIN suscriptions s ON s.blog_id = b.id
LEFT JOIN users us ON s.user_id = us.id
GROUP BY b.id;

answered Apr 28 '16 at 14:35

Doglas

642
1
11
22

Really helpful, thank you! But I also would like to have the info of the suscription, because it will have others columns like date, etc. Sorry for not explain this on the question. – Jose Carlos Ramírez Vásquez Apr 28 '16 at 14:47
I understand, in this case, as far as I know, you really need two queries – Doglas Apr 28 '16 at 14:55
The `GROUP_CONCAT` is working well by now, thank you! – Jose Carlos Ramírez Vásquez Apr 29 '16 at 09:24

score 0 · Answer 2 · edited May 23 '17 at 12:23

You can do any of the two. Several small queries or one big query with more joins or (even sub-queries) to get all the data at once. You won't notice any difference in performance in either case until you get to huge amounts of data.

If you are just getting started with PHP I would suggest to stick to having smaller queries that are easier to debug and understand. After all MySQL is made to perform well for small quick queries.

Soon enough, You move to an ORM (or a Framework) that will write the queries for you. You will only need to focus on the logic of the problem you're trying to solve.

For example in CakePHP you would only need to do:

$blogs = $this->Blog->find('all')

And given that a Blog belongs to an User and an User can have many Subscriptions, all the data will be retrieved "automagically" (It would generate the SQL for the join tables on its own like the one in Doglas's answer).

So for now, I'd advise to focus on building small queries that are easy to understand, debug, optimize and maintain.

Here is a list of ORMs in PHP. And these are pretty common:

Eloquent from the Laravel team.
Propel

And here is a post about ORMs from the same blog ;)

I didn't know of this, It seems really practical, thank you, I will take a look soon! — Jose Carlos Ramírez Vásquez, Apr 28 '16 at 15:02

Get data from relational table with multiple rows

2 Answers2