Limit Ignored in Query with Group_Concat

LIMIT ignored in query with GROUP_CONCAT

The LIMIT clause limits the number of rows in the final result set, not the number of rows used to construct the string in the GROUP_CONCAT. Since your query returns only one row in the final result the LIMIT has no effect.

You can solve your issue by constructing a subquery with LIMIT 3, then in an outer query apply GROUP_CONCAT to the result of that subquery.

LIMIT is ignored in query with into outfile

GROUP_CONCAT doens't work with LIMIT
Have a look at the documentaition : http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat

MySQL and GROUP_CONCAT() maximum length

CREATE TABLE some_table (
  field1 int(11) NOT NULL AUTO_INCREMENT,
  field2 varchar(10) NOT NULL,
  field3 varchar(10) NOT NULL,
  PRIMARY KEY (`field1`)
);

INSERT INTO `some_table` (field1, field2, field3) VALUES
(1, 'text one', 'foo'),
(2, 'text two', 'bar'),
(3, 'text three', 'data'),
(4, 'text four', 'magic');

This query is a bit strange but it does not need another query to initialize the variable; and it can be embedded in a more complex query.
It returns all the 'field2's separated by a semicolon.

SELECT result
FROM   (SELECT @result := '',
               (SELECT result
                FROM   (SELECT @result := CONCAT_WS(';', @result, field2) AS result,
                               LENGTH(@result)                            AS blength
                        FROM   some_table
                        ORDER  BY blength DESC
                        LIMIT  1) AS sub1) AS result) AS sub2; 

Mysql Query Group Concat Limit multiple record of child table in single query

Set up your relationships and then constrain them like so:

$albums = Album::with('albumMedia', 'albumMembers')
->whereHas('albumMembers', function($query) use ($userId) {
    $query->where('albumMembers.member_id', $userId);
})
->orderBy('album.updated_at')
->take($limit)
->skip($skip)
->get();

Lastly, you could then add a method to the Album model which will help you retrieve your concatenated string/link, based on the eagerly-loaded relationship.

Better yet, instead of doing a whereHas, you could so something like a scope method on the Album model which will create it for you. This would be much cleaner than the example above.

GROUP_CONCAT with limit

One somewhat hacky way to do it is to post-process the result of GROUP_CONCAT:

substring_index(group_concat(s.title SEPARATOR ','), ',', 3) as skills

Of course this assumes that your skill names don't contain commas and that their amount is reasonably small.

fiddle

A feature request for GROUP_CONCAT to support an explicit LIMIT clause is unfortunately still not resolved.

UPDATE: As user Strawberry points out, the table player_skills should have the tuple (player_id, skill_id) as its primary key, otherwise the schema allows for the same skill to be assigned to a player multiple times, in which case group_concat would not work as expected.

Making a GROUP_CONCAT query more efficient

NULL is placed first (could use COALESCE to replace NULL with something else too instead of using an additional sort column). The second thing is reducing the joins, because the last two were on the id on which we concat.

SELECT
   component_instances.*,
   GROUP_CONCAT(DISTINCT view_groups.id) AS view_group_ids,
   GROUP_CONCAT(DISTINCT edit_groups.id) AS edit_group_ids,
   GROUP_CONCAT(DISTINCT view_groups_users.user_id) AS view_user_ids,
   GROUP_CONCAT(DISTINCT edit_groups_users.user_id) AS edit_user_ids
FROM
   `component_instances`
   LEFT OUTER JOIN permissions
      ON permissions.component_instance_id = component_instances.id
   LEFT OUTER JOIN groups view_groups
      ON view_groups.id = permissions.group_id AND permissions.view = 1
   LEFT OUTER JOIN groups edit_groups
      ON edit_groups.id = permissions.group_id AND permissions.edit = 1
   LEFT OUTER JOIN groups_users view_groups_users
      ON view_groups_users.group_id = view_groups.id
   LEFT OUTER JOIN groups_users edit_groups_users
      ON edit_groups_users.group_id = edit_groups.id
GROUP BY
   component_instances.id
ORDER BY
   component_instances.ancestry, -- MySQL was sorting the NULL values already correctly
   position
;

---

Related Topics