0

I have 4 tables A, B, C & D as follows:
B belongs to A
B belongs to C
&
C has many D

As 'A' may or may not have any B's but if there is a B, then that B must have a C. And according to my logic, C will have at least one D or may be more.
Now, I want to get the list of A's with count(b) as well as count(d). Here count(b) and count(d) might be zero.

So what I was doing till now is as follows:
@a = A.joins(bs: {c: :ds}).select("a.*, count(b) as count_b, count(d) as count_d").group("a.id")
But eventually this is not working, as it INNER JOIN b with a. Which means, if there is no corresponding b for a, then that a will not be in the list @a. That's the problem.

So, is there any way to do it ?

Rohit Singh
  • 2,143
  • 3
  • 13
  • 16

3 Answers3

0

Try Left outer join:
@a = A.joins("LEFT JOIN B on A.id=B.a_id INNER JOIN C on C.id=B.c_id INNER JOIN CD on C.id=CD.c_id INNER JOIN D on D.id=CD.d_id") For more details check this question

Community
  • 1
  • 1
pshoukry
  • 755
  • 5
  • 14
  • You are right. It would have worked if there were only A and B. But I don't know, which join to apply b/w B-C, C-CD & CD-D. I hope, I have not been ambiguous in explaining. – Rohit Singh Oct 04 '15 at 11:14
  • Can you try the modified solution ( and tell me if didn't work what was the problem ) – pshoukry Oct 04 '15 at 11:38
  • Actually I tried it earlier but it didn't work. I tried `@a = A.joins("LEFT JOIN B on A.id=B.a_id LEFT JOIN C on C.id=B.c_id LEFT JOIN CD on C.id=CD.c_id LEFT JOIN D on D.id=CD.d_id")` instead which worked for me. – Rohit Singh Oct 04 '15 at 12:09
0

joins has a form where it accepts a string. In that string you can put 'LEFT OUTER join bs ON bs... = as...'

nimrodm
  • 23,081
  • 7
  • 58
  • 59
  • I tried writing `A.joins("LEFT JOIN B on A.id=B.a_id LEFT JOIN C on C.id=B.c_id LEFT JOIN CD on C.id=CD.c_id LEFT JOIN D on D.id=CD.d_id")`. But, that didn't work for me... – Rohit Singh Oct 04 '15 at 11:10
  • You are right. It would have worked if there were only A and B. But I don't know, which join to apply b/w B-C, C-CD & CD-D. I hope, I have not been ambiguous in explaining. – Rohit Singh Oct 04 '15 at 11:13
  • Sorry but where did you get a `CD` table? – nimrodm Oct 04 '15 at 11:42
  • Sorry, I forgot to mention. CD is join table b/w C and D, as C and D are many to many relationship. – Rohit Singh Oct 04 '15 at 12:08
0

I tried @a = A.joins("LEFT JOIN B on A.id=B.a_id LEFT JOIN C on C.id=B.c_id LEFT JOIN CD on C.id=CD.c_id LEFT JOIN D on D.id=CD.d_id") which worked for me.

Rohit Singh
  • 2,143
  • 3
  • 13
  • 16