How to query One-to-Many entities?

Question

In my system (PHP/MySql) I have a User table and Images table. One User can have many Images.

I have a page where a need to list all Users(even if the user don't have Images) and all Images, for each user. How can I get this?

Solution 1

 $users = query('SELECT * FROM Users')

then

 foreach($users as $user)  
     $user->images = query('SELECT id, path, size FROM Images WHERE user_id = ' . $user->id)

Solution 2

$users = query('SELECT *, (SELECT GROUP_CONCAT(id,path,size) FROM Images) as images FROM Users')

then
 foreach($users as $user)

   list($id,$path, $size) = explode(',', $user->images)
   $user['images'] = ['id' => $id, 'path' => $path, 'size' => $size];

There is a better way to acomplish this?

P.S. 1: Using LEFT JOIN duplicate the results.

P.S. 2: Using INNER JOIN / JOIN , Users that don't have Images are ignored.

Inner join will ignore the user whihc doesnot have records in image table right??? — BetaDev, Mar 24 '17 at 19:21
see updated post, and the link http://stackoverflow.com/questions/22769641/left-join-without-duplicate-rows-from-left-table — BetaDev, Mar 24 '17 at 19:34

score 0 · Answer 1 · edited May 23 '17 at 10:30

0

Here, the example (Remember Inner join is the Intersection of both table)

SELECT
Users.id as user_id,
Images.id as image_id,
Images.path,
Images.size
FROM
Users
INNER JOIN Images ON Users.id = Images.user_id

IF you want to use INNER JOIN, But remember for large and many data, JOINs are slower and not recommended. Learn how to get data when you need, I recommend define REST endpoints and make ajax requests and get data when your web client need.

If you dont want to miss any user and also dont want duplicate then see following links.
Update:
I see your updated post. this Link will help you.
and more precisely the answer here Left Join Without Duplicate

and Two tables, whit one to many relationship. How to join values without duplicate rows?

edited May 23 '17 at 10:30

Community

1
1

answered Mar 24 '17 at 19:15

BetaDev

4,516
3
21
47

Thanks for the link! But using the query in the link results in only one image(I think it's the first found). – Bruno Portis Mar 24 '17 at 19:36
http://stackoverflow.com/questions/17818703/sql-query-one-to-many-relationship-join-without-duplicates – BetaDev Mar 24 '17 at 19:37
you can find solution by concatinating all the image's id to a field and left join here http://stackoverflow.com/questions/17695199/two-tables-whit-one-to-many-relationship-how-to-join-values-without-duplicate – BetaDev Mar 24 '17 at 19:41

Zeljka · Answer 2 · 2017-03-28T06:09:07.580

0

SELECT User.* Images.id as images_id, path, size
FROM Images
LEFT JOIN on User user.id = Images.user_id

edited Mar 28 '17 at 06:09

answered Mar 24 '17 at 19:18

Zeljka

376
1
10

With this I've got good results, but then I have to select * from users, and then mix the results? – Bruno Portis Mar 24 '17 at 19:24
you dont have mix the results, you can select other (or all) columns from User, I will edit post – Zeljka Mar 24 '17 at 19:26
Right. So to query for information about the User I query the Images table. Cool. But if User have another one-to-many? Like Groups? I'll query Images table or Groups Table? – Bruno Portis Mar 24 '17 at 19:31
SELECT User.* Images.id as images_id, path, size use this for select and you will have everyting – Zeljka Mar 24 '17 at 19:31
in that case User is betwen those two tables and its like relationships like many to many, Images and grup trough Users have many to many relationship – Zeljka Mar 24 '17 at 19:33
While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/low-quality-posts/15634614) – Michel Gokan Khan Mar 25 '17 at 18:44

b2ok · Answer 3 · 2017-03-24T19:28:31.070

Here is all posibility query on 2 table, students and payment.

Tables:   students                payment
    id_student  name        id  id_student datepayment
    1         Lisa         1    1        2017-01-01
    2                      2    1        2017-02-03
    3         Asher        3    2        2017-03-05
    4         Lee          4    1        2017-03-03

This query:

select students.name, payment.datepayment
FROM students, payment
where students.id_student = payment.id_student;

will give this result - all student's name which has id_student in table payment

name    datepayment
Lisa    2017-01-01
Lisa    2017-02-03
Lisa    2017-03-03
        2017-03-05

This query:

SELECT s.name, p.datepayment
FROM
students s
LEFT OUTER JOIN payment p ON s.id_student = p.id_student;

will give this result: all student's name from LEFT table (students) which has or has not id_student in table payment

name    datepayment
Lisa    2017-01-01
Lisa    2017-02-03
        2017-03-05
Lisa    2017-03-03
Asher       NULL
Lee         NULL

This query:

SELECT s.name, p.datepayment
FROM
students s
RIGHT OUTER JOIN payment p ON s.id_student = p.id_student;

will give this result - all from RIGHT table payment and only student's name which has id_student in table payment

name    datepayment
Lisa    2017-01-01
Lisa    2017-02-03
        2017-03-05
Lisa    2017-03-03

This query:

SELECT s.name, p.datepayment
FROM
students s
INNER JOIN payment p ON s.id_student = p.id_student;

will give this result - all student's name which has id_student in table payment

name    datepayment
Lisa    2017-01-01
Lisa    2017-02-03
Lisa    2017-03-03

How to query One-to-Many entities?

3 Answers3