About grouping results in SQL query

Question

Sorry if this is a really dumb question. I have a table with 3 columns: id, user_id and article_id that represents a many to many relationship between user and article tables. I'm using PHP and MySQL

Example records:

• id: 1 user_id: 5 article_id: 6 (it means that user 5 likes article 6)
• id: 2 user_id: 1 article_id: 6
• id: 3 user_id: 2 article_id: 1
• id: 4 user_id: 3 article_id: 3

i would like to group results by article and get an array like this one:

array[6] = {5,1};
array[1] = {2};
array[3] = {3};

so i can get the users who like every article in a convenient way. The question is, which is the best way to accomplish it? Any help will be really appreciated. Thanks

Answer 1

not being familiar with "Doctrine" syntax, if you are able to provide a query string, you should be able to get the results via

select article_id, group_concat( user_id ) as LikedByUsers
   from YourTable
   group by article_id

first column would have the article ID, and second column your comma separated list of users who like it.

Answer 2

You can iterate on PHP side over the results and group into array, or use GROUP_CONCAT and then explode