0

Ok. I want these info from each user record.

user.username
user.fullname
report_qty

so the first two fields exist in the user table. but the last one should be computed from ther tables.

i have a table named reports and there are 3 kind of relation to it

timeline reports (WHERE violated_user_id = someUserId)

post reports (LEFT JOIN posts ON(posts.id = reports.post_id))

image reports (LEFT JOIN images ON(images.id = reports.image_id))

so I want something to get these kind of records and fields:

username         fullname        reports_qty
john_doe         john doe             0
jane_doe         jane doe             5

the reports_qty is the sum of timeline/posts/images reports so if jane doe has 2 timeline reports and 2 image reports and 0 post reports that would make a 5 for her reports_qty field

so far I could make it like this:

SELECT user.username, user.fullname, (
    SELECT SUM(cnt) FROM (
                        SELECT COUNT(report_id) as `cnt` FROM `report` WHERE violated_user_id = $userId
        UNION ALL
                        SELECT COUNT(report_id) as `cnt` FROM report LEFT JOIN blog_post ON (posts.id = report.post_id) WHERE blog_post.user_id = $userId
        UNION ALL
        SELECT COUNT(report_id) as `cnt` FROM report LEFT JOIN image ON (image.image_id = report.image_id) WHERE image.user_id = $userId
    ) as `q`
) as `report_qty` FROM user GROUP BY user.id;

as you can see there is this problem that I have to define the $userId which is wronge. it should take the current record's user.id and return me the results. how can I do this in mysql? and i am working in codeigniter right know if there is any workaround to this i would appreciate your advices.

EDIT

the table structures and sample data is like this: I actually removed some unnecessary fields from these fields but this should do.

REPORT TABLE:

CREATE TABLE `report` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `image_id` INT(11) NULL DEFAULT NULL,
    `post_id` INT(11) NULL DEFAULT NULL,
    `violated_user_id` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `violated_user_id` (`violated_user_id`),
    INDEX `image_id` (`image_id`),
    INDEX `post_id` (`post_id`),
    CONSTRAINT `FK__user` FOREIGN KEY (`violated_user_id`) REFERENCES `user` (`user_id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `FK_report_posts` FOREIGN KEY (`post_id`) REFERENCES `posts` (`id`) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT `FK_report_images` FOREIGN KEY (`image_id`) REFERENCES `images` (`image_id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=91
;

SAMPLE DATA RECORDS FOR REPORT:

TIMELINE REPORT

INSERT INTO `report` (`id`, `image_id`, `post_id`, `violated_user_id`) VALUES (1, NULL, NULL, 1);

POST REPORT

INSERT INTO `report` (`id`, `image_id`, `post_id`, `violated_user_id`) VALUES (2, NULL, 1, NULL);

IMAGE REPORT

INSERT INTO `report` (`id`, `image_id`, `post_id`, `violated_user_id`) VALUES (3, 1, NULL, 2);

IMAGES TABLE:

CREATE TABLE `images` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) NOT NULL,
    `image` VARCHAR(64) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `user_id` (`user_id`),
    CONSTRAINT `FK_image_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=5
;

IMAGES SAMPLE DATA RECORDS:

FIRST USER'S POST

INSERT INTO `images` (`id`, `user_id`, `image`) VALUES (1, 1, '8899c3fecc6afd8cd54a806fc8402bde.jpg');

POSTS TABLE:

CREATE TABLE `posts` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `user_id` INT(11) NULL DEFAULT NULL,
    `text` TEXT NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `user_id` (`user_id`),
    FULLTEXT INDEX `text` (`text`),
    CONSTRAINT `FK_post_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=61
;

USER'S POSTS SAMPLE DATA:

FIRST USER

INSERT INTO `posts` (`id`, `user_id`, `text`) VALUES (1, 1, 'aaaaaaaaaaaa');

**USERS TABLE:**

    CREATE TABLE `user` (
    `user_id` INT(11) NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(32) NOT NULL COLLATE 'utf8mb4_bin',
    `fullname` VARCHAR(64) NOT NULL COLLATE 'utf8mb4_bin',
    `email` VARCHAR(128) NOT NULL COLLATE 'utf8mb4_bin',
    PRIMARY KEY (`user_id`),
    UNIQUE INDEX `user_name` (`username`),
    UNIQUE INDEX `email` (`email`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=8
;

USERS SAMPLE DATA:

FIRST USER'S RECORD

INSERT INTO `user` (`id`, `username`, `email`) VALUES (1, 'm-f-r', 'eagle.seeing@yahoo.com');
  • 2
    Add into the question text ("edit" link under it): 1) CREATE TABLE scripts for each table; 2) INSERT INTO scripts for a sample data for each table (2-3 base records and 2-3 reference records per each base one); 3) Desured result on THAT sample data. – Akina Feb 28 '19 at 9:31
  • @Akina I did as you said, please help me through this – mohammad Mar 1 '19 at 9:39
  • I did as you said You did it wrong. Tables creation order - errorneous. Syntax - errors. Fieldnames in references - errors. Data - FK violation. And I don't see desured result on THAT data. – Akina Mar 1 '19 at 10:10
  • dude, the queries are working for me, I copied them from MySQL itself, the orders are users, posts, images, report. – mohammad Mar 3 '19 at 7:03
  • Does it work to simply replace $userid by user.user_id? – Rick James Mar 7 '19 at 19:58
0

There are several inconsistencies in your code, for example:

CREATE TABLE user ( user_id INT(11) NOT NULL AUTO_INCREMENT,

and

CONSTRAINT FK_post_user FOREIGN KEY (user_id) REFERENCES user (id) ON UPDATE CASCADE ON DELETE CASCADE

I changed the id field of user table to be id in the user table and all the foreign key references.

Then you mention table blog_post in your attempt, which doesn't appear in your sample data. I'm assuming blog_post is the same as posts.

Once you fix all that, your query changes into:

SELECT `user`.username, `user`.fullname, (
    SELECT SUM(cnt) FROM (
        SELECT COUNT(`report`.`id`) AS `cnt` FROM `report`
         WHERE `violated_user_id` = `user`.id
        UNION ALL
        SELECT COUNT(`report`.`id`) AS `cnt` FROM `report`
          LEFT JOIN `posts` ON (`posts`.id = `report`.post_id)
         WHERE `posts`.user_id = `user`.id
        UNION ALL
        SELECT COUNT(`report`.`id`) AS `cnt` FROM `report`
          LEFT JOIN `images` ON (`images`.id = report.image_id)
         WHERE `images`.user_id = `user`.id
    ) as `q`
) as `report_qty` FROM `user` GROUP BY `user`.id;

... which doesn't work, because the inner-most query can't see the table user from the outer-most query.

Instead of doing so much nesting, you can get rid of the middle SELECT and add the three counts, making the query work:

SELECT u.username, u.fullname,
      ( SELECT COUNT(`report`.`id`) AS `cnt` FROM `report`
         WHERE `violated_user_id` = u.id
      ) + 
      ( SELECT COUNT(`report`.`id`) AS `cnt` FROM `report`
          LEFT JOIN `posts` ON (`posts`.id = `report`.post_id)
         WHERE `posts`.user_id = u.id
      ) +
      ( SELECT COUNT(`report`.`id`) AS `cnt` FROM `report`
          LEFT JOIN `images` ON (`images`.id = report.image_id)
         WHERE `images`.user_id = u.id
      ) as `report_qty`
  FROM `user` AS u GROUP BY u.id;

But then you can notice that all the tables refer to user.id, so you can get rid of the joins in the subqueries:

SELECT u.username, u.fullname,
      ( SELECT COUNT(`report`.`id`) AS `cnt` FROM `report`
         WHERE `violated_user_id` = u.id
      ) + 
      ( SELECT COUNT(`posts`.`id`) AS `cnt` FROM `posts`
         WHERE `posts`.user_id = u.id
      ) +
      ( SELECT COUNT(`images`.`id`) AS `cnt` FROM `images`
         WHERE `images`.user_id = u.id
      ) as `report_qty`
  FROM `user` AS u GROUP BY u.id;

Finally, you can rewrite this query using joins instead of subqueries:

SELECT `user`.username, `user`.fullname,
       count(`report`.`id`) + count(`posts`.`id`)
       + count(`images`.`id`) AS `report_qty`
FROM `user`
LEFT JOIN `report` ON  `report`.`violated_user_id` = `user`.`id`
LEFT JOIN `posts` ON `posts`.`user_id` = `user`.`id`
LEFT JOIN `images` ON `images`.`user_id` = `user`.`id`
GROUP BY `user`.`id`;

This fixes the problem of your query.

There is now a problem with your database design which has many flaws:

  • Is the user_id field necessary in images and posts?

  • What is the relationship between images records and report record?

  • etc...

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.