Using GROUP_CONCAT and additional query to grab values?

Databases
1 
FROM 
    (SELECT department_activity.*, date(department_activity.activity_date) groupby_date,
    COUNT(department_activity.id) AS number_of_clients_added,
    GROUP_CONCAT(department_activity.id) AS clients_comma_list 
    FROM department_activity
    INNER JOIN subscriptions 
    ON department_activity.user_id = subscriptions.subscribing_user
    WHERE subscriptions.user_id = '0'
    GROUP BY department_activity.user_id,
             date(department_activity.activity_date)
    ) As department_activities 
GROUP BY object_id,
      groupby_date
ORDER BY activity_date DESC
LIMIT 20;

GROUP_CONCAT(department_activity.id) AS clients_comma_list

Can I use clients_comma_list to pull columns from other tables, where ID IN (clients_comma_list) ?

2

what happened when you tested it?™ :wink:

i would probably use an actual join

3 Likes
3

I haven’t tested it. Because at the moment I am not sure where to put a JOIN or if I should use UNION?

I know that I can use IN (…) inside a loop PHP one, to grab results, but this appears to be hitting the DB very hard.

If you can suggest some tips I’d be happy! Thanks @r937

4

I made this, http://sqlfiddle.com/#!9/964ed/1

returns:

id  author_id   username    number_of_articles  articles_published
3   2           David       1                   3
2   1           Bob         2                   2,1

Can I somehow, use the articles_published column within the same query to get author information and article information, is this possible?

5

Expected result:

Bob's articles

Health Care
Health care or healthcare is the maintenance or improvement of health via the diagnosis, treatment, and prevention of disease, illness, injury, and other physical and mental impairments in human beings.

Science 101
Science is the concerted human effort to understand, or to understand better, the history of the natural world and how the natural world works, with observable physical evidence as the basis of that understanding1. It is done through observation of natural phenomena, and/or through experimentation that tries to simulate natural processes under controlled conditions.

------

David's articles

Physics 101
Physics is a natural science based on experiments, measurements and mathematical analysis with the purpose of finding quantitative physical laws for everything from the nanoworld of the microcosmos to the planets, solar systems and galaxies that occupy the macrocosmos.

-----
7

@oddz,

Given that structure you will need to do so in the application language. The authors articles should be separate table not a comma delimited list a single column. Using a comma delimited list has several limitations and is poor by design.

I have these two tables:

CREATE Table users (
  id BIGINT(100) AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL, 
  password VARCHAR(50) NOT NULL
);

CREATE Table articles (
  id BIGINT(100) AUTO_INCREMENT PRIMARY KEY,
  author_id BIGINT(100) NOT NULL, 
  title VARCHAR(50) NOT NULL, 
  content TEXT, 
  published DATETIME NOT NULL
);

Insert:

INSERT INTO users (username, password) 
VALUES ('Bob', '5f4dcc3b5aa765d61d8327deb882cf99');

INSERT INTO users (username, password) 
VALUES ('David', '630bf032efe4507f2c57b280995925a9');

INSERT INTO articles (author_id, title, content, published)
VALUES ('1', 'Science 101', 'Science is the concerted human effort to understand, or to understand better, the history of the natural world and how the natural world works, with observable physical evidence as the basis of that understanding1. It is done through observation of natural phenomena, and/or through experimentation that tries to simulate natural processes under controlled conditions.',
        '2015-11-30 09:18:43');
        
INSERT INTO articles (author_id, title, content, published)
VALUES ('1', 'Health Care', 'Health care or healthcare is the maintenance or improvement of health via the diagnosis, treatment, and prevention of disease, illness, injury, and other physical and mental impairments in human beings.',
        '2016-01-10 15:20:43');

INSERT INTO articles (author_id, title, content, published)
VALUES ('2', 'Physics 101', 'Physics is a natural science based on experiments, measurements and mathematical analysis with the purpose of finding quantitative physical laws for everything from the nanoworld of the microcosmos to the planets, solar systems and galaxies that occupy the macrocosmos.',
        '2016-01-17 14:18:43');

Query:

SELECT 
  articles.id, 
  articles.author_id,
  users.username,
  COUNT(articles.id) AS number_of_articles,
  GROUP_CONCAT(DISTINCT articles.id) AS articles_published
FROM articles 
LEFT JOIN users ON articles.author_id = users.id
GROUP BY articles.author_id 
ORDER BY articles.published DESC;

Result:

id  author_id   username    number_of_articles  articles_published
3   2           David       1                   3
2   1           Bob         2                   2,1

Expected result:

Bob's articles

Health Care
Health care or healthcare is the maintenance or improvement of health via the diagnosis, treatment, and prevention of disease, illness, injury, and other physical and mental impairments in human beings.

Science 101
Science is the concerted human effort to understand, or to understand better, the history of the natural world and how the natural world works, with observable physical evidence as the basis of that understanding1. It is done through observation of natural phenomena, and/or through experimentation that tries to simulate natural processes under controlled conditions.

------

David's articles

Physics 101
Physics is a natural science based on experiments, measurements and mathematical analysis with the purpose of finding quantitative physical laws for everything from the nanoworld of the microcosmos to the planets, solar systems and galaxies that occupy the macrocosmos.

-----
8

Disregard what I originally said. I thought that was the database schema.

9

It’s all fine @oddz. So what do you suggest or say I am able to do? Thanks!

10

if i go by your expected results, this is the query you need –

SELECT users.username , articles.title , articles.content FROM users INNER JOIN articles ON articles.author_id = users.id ORDER BY users.username , articles.title

then format the results using php

do you want any users to be included who have no articles?

also, if you’re going to extract all articles for each user, you can get the count of articles (which you omitted from your expected results) with php as well

1 Like
11

Explain how?

12

i feel confident that there’s an array function for this

but i don’t do php myself :wink:

13

I have made an algorithm (with loop) that sorts things out, but I usually have to use count(array) to get the size of it or more accurately the length of it 0-n. So I guess, my first SQL query (with GROUP_CONCAT) turns out to be useless, hmm.

14

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.