Geek Answers Handbook

Complex group at mysql request

I have a table with the following rows:

id. user_id, type - link 1. 555, image - http://1 2. 555, image - http://2 3. 654, image - http://3 4. 245, video - http://.. 5. 555, image - http://.. 6. 878, text - http://..

I want to group the type (image) by date so that they appear as a single line. In this example, the first two images will be combined together, and the output will be similar to the following, also note that if he is not the same user, then he will not be grouped for this user.

Exit

 1. 555, image - http://1, http://2 ** GROUPED BY DATE, if they are same type and not break type after it. 2. 654, image - http://3 3. 245, video - http://.. 4. 555, image - http://.. 5. 878, text - http://.

btw im tring to make facebook as a news feed, if anyone has a better idea then please share.

+4
source share
5 answers
 SELECT `date`, `user_id`, `type`, GROUP_CONCAT(`link`) FROM `table` GROUP BY `date`, `user_id`, `type`
+2
source

Is there a reason you need to do this in SQL? I am trying to do data collection in SQL and format push into the code from which SQL is being called.

I would execute an SQL query that would be simple to get all image files but ordered by user_id / date. Then my code will go through the results, combining each image file in the same output line until the user ID / date changes, in which case we know that we need to start a new line with a new user ID / date.

It also makes it easy to do any funky formatting around the text, especially if you are going to generate HTML.

+1
source

This is not so easy to do in SQL, because it depends on the order whose SQL is not suitable.

The query is rather cumbersome, so I will give it in full first, and then a breakdown showing how it develops.

 SELECT @rownum: =@rownum +1 AS id, t.user_id, type, date, urls FROM (SELECT MIN(ID) AS original_id, user_id, type, date, GROUP_CONCAT(url) urls FROM (SELECT i1.*, IF(i1.type='image', IFNULL((SELECT MIN(i2.ID)-1 FROM Items i2 WHERE i2.ID>i1.ID AND (i2.type!=i1.type OR i1.user_id!=i2.user_id OR i1.date!=i2.date)), (SELECT MAX(id) FROM Items)), i1.ID) AS lastRow, IF (i1.type='image', IFNULL(SELECT MAX(i3.ID)+1 FROM Items i3 WHERE i3.ID<=i1.ID AND (i3.type!=i1.type OR i1.user_id!=i3.user_id OR i1.date!=i3.date)), (SELECT MIN(id) FROM Items)), i1.ID) AS firstRow) AS groupItems GROUP BY user_id, type, date, firstRow, lastRow) t, (SELECT @rownum:=0) r ORDER BY t.original_id;

The query uses a correlated subquery to find the start and end identifiers of each image group. A group boundary is an element that is not the same type, user, or date.

 SELECT i1.ID, IF(i1.type='image', IFNULL((SELECT MIN(i2.ID)-1 FROM Items i2 WHERE i2.ID>i1.ID AND (i2.type!=i1.type OR i1.user_id!=i2.user_id OR i1.date!=i2.date)), (SELECT MAX(id) FROM Items)), i1.ID) AS lastRow, IF (i1.type='image', IFNULL(SELECT MAX(i3.ID)+1 FROM Items i3 WHERE i3.ID<=i1.ID AND (i3.type!=i1.type OR i1.user_id!=i3.user_id OR i1.date!=i3.date)), (SELECT MIN(id) FROM Items)), i1.ID) AS firstRow

For each element, the firstRow / lastRow columns provide the beginning and end of the group. Then we can use GROUP_CONCAT to concatenate all the URLs. To maintain order, MIN (id) is printed, giving the first identifier for each group.

 SELECT MIN(id) AS original_id, user_id, type, date, GROUP_CONCAT(url) urls FROM (SELECT i1.*, IF(i1.type='image', IFNULL((SELECT MIN(i2.ID)-1 FROM Items i2 WHERE i2.ID>i1.ID AND (i2.type!=i1.type OR i1.user_id!=i2.user_id OR i1.date!=i2.date)), (SELECT MAX(id) FROM Items)), i1.ID) AS lastRow, IF (i1.type='image', IFNULL(SELECT MAX(i3.ID)+1 FROM Items i3 WHERE i3.ID<=i1.ID AND (i3.type!=i1.type OR i1.user_id!=i3.user_id OR i1.date!=i3.date)), (SELECT MIN(id) FROM Items)), i1.ID) AS firstRow) AS groupItems GROUP BY user_id, type, date, firstRow, lastRow

Finally, to get consecutive identifiers for the new table, use the variable to calculate the rank:

 SELECT @rownum: =@rownum +1 AS id, user_id, type, date, urls FROM (SELECT MIN(ID) AS original_id, user_id, type, date, GROUP_CONCAT(url) urls FROM (SELECT i1.*, IF(i1.type='image', IFNULL((SELECT MIN(i2.ID)-1 FROM Items i2 WHERE i2.ID>i1.ID AND (i2.type!=i1.type OR i1.user_id!=i2.user_id OR i1.date!=i2.date)), (SELECT MAX(id) FROM Items)), i1.ID) AS lastRow, IF (i1.type='image', IFNULL(SELECT MAX(i3.ID)+1 FROM Items i3 WHERE i3.ID<=i1.ID AND (i3.type!=i1.type OR i1.user_id!=i3.user_id OR i1.date!=i3.date)), (SELECT MIN(id) FROM Items)), i1.ID) AS firstRow) AS groupItems GROUP BY user_id, type, date, firstRow, lastRow) t, (SELECT @rownum:=0) r ORDER BY t.original_id;

SQL is best suited for working with unordered datasets, rather than sequences, as here. If you can do this in presentation code, or perhaps better at your application level, I believe it will be faster and more flexible. A manual coded solution will find the beginning and end of each group in one pass through the data. I doubt that the SQL query will execute as efficiently as this.

+1
source

this request will do the trick:

 select id, user_id, `type`, group_concat(link) from images group by user_id, `type`, date order by id
0
source

I could think of another data store like CouchDB or RavenDB. This would be much better for serving this type of content and would not need to handle foreign keys or associations.

Just use continuous replication for all friends' databases.

Ultimately, you will have to pre-query or abandon the relational model in order to get the speed back.

0
source

More articles:


All Articles