CODEWITHSUNDEEP
phpmysqldatabase
Khadreal
Khadreal

Reputation: 731

Left joining same table multiple times

I have the following tables and trying to get count of each user products in a category within a stipulated time

users (username and role are index)

id, username, role

products (user_id is index)
id, user_id, category_id, created_at

What I tried is 

select `users`.`id` as `u2_id`, `users`.`username` as `u2_username`,
count(DISTINCT p1.id) as p1_total, count(DISTINCT p2.id) as p2_total,
count(DISTINCT p3.id) as p3_total, count(DISTINCT p4.id) as p4_total,
count(DISTINCT p5.id) as p5_total, count(DISTINCT p6.id) as p6_total,
count(DISTINCT p7.id) as p7_total from `users` left join users AS u2 on
`u2`.`parent` = `users`.`id` left join `policies` as `p1` on `p1`.`user_id` =
`u2`.`id` and p1.category_id  = '2'
and `p1`.`created_at` between "2020-02-01 00:00:00" and "2020-04-02 23:59:59"
left join `policies` as `p2` on `p2`.`user_id` = `u2`.`id` and
p2.category_id  = '45' and
`p2`.`created_at` between "2020-02-01 00:00:00" and "2020-04-02 23:59:59" left
join `policies` as `p3` on `p3`.`user_id` = `u2`.`id` and
p3.category_id  = '34' and
`p3`.`created_at` between "2020-02-01 00:00:00" and "2020-04-02 23:59:59" left
join `policies` as `p4` on `p4`.`user_id` = `u2`.`id` and
p4.category_id  = '32' and
`p4`.`created_at` between "2020-02-01 00:00:00" and "2020-04-02 23:59:59" left
join `policies` as `p5` on `p5`.`user_id` = `u2`.`id` and
p5.category_id  = '1' and
`p5`.`created_at` between "2020-02-01 00:00:00" and "2020-04-02 23:59:59" left
join `policies` as `p6` on `p6`.`user_id` = `u2`.`id` and
p6.category_id  = '3' and
`p6`.`created_at` between "2020-02-01 00:00:00" and "2020-04-02 23:59:59" left
join `policies` as `p7` on `p7`.`user_id` = `u2`.`id` and
p7.category_id  = '67' and
`p7`.`created_at` between "2020-02-01 00:00:00" and "2020-04-02 23:59:59"
where `users`.`username` = 'user' and `users`.`role` = 'admin' group by
`users`.`id` limit 20 offset 0

But this take forever to return result and timeout when date chosen is more than a month, I'm not sure if left join is the problem.

Upvotes: 0

Views: 42

Answers (1)

Nick
Nick

Reputation: 147146

You can use conditional aggregation to simplify this query:

select `users`.`id` as `u2_id`, `users`.`username` as `u2_username`,
       count(distinct case when p1.category = '2'  then p1.id end) AS p1_total,
       count(distinct case when p1.category = '45' then p1.id end) AS p2_total,
       count(distinct case when p1.category = '34' then p1.id end) AS p3_total,
       count(distinct case when p1.category = '32' then p1.id end) AS p4_total,
       count(distinct case when p1.category = '1'  then p1.id end) AS p5_total,
       count(distinct case when p1.category = '3'  then p1.id end) AS p6_total,
       count(distinct case when p1.category = '67' then p1.id end) AS p7_total
from `users`
left join users AS u2 on `u2`.`parent` = `users`.`id` 
left join `policies` as `p1` on `p1`.`user_id` = `u2`.`id`
                            and `p1`.`created_at` between "2020-02-01 00:00:00" and "2020-04-02 23:59:59"
where `users`.`username` = 'user' and `users`.`role` = 'admin'
group by `users`.`id` 
limit 20 
offset 0

Upvotes: 1

Related Questions