MYSQL – how can use a field of current record as a where clause in a sub query

codeigniterMySQL

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');

Best Answer

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...

Best Answer

Related Solutions

MySql – alternate of auto_increment on multiple columns

MYSQL wont use index in “IN(SELECT ..)” sub clause

Related Question