0

We need to retrieve logs for customer comm record in our rails app. The condition in plain english is logs for customer_comm_record based on:

#1. the sales_id in customer comm record's customers is equal to current user id
#2. the sales_id in customer comm record's customer's project is equal to the current user id.

The SQL code for #1 could be (SQL#1):

Log.joins(:customer_comm_record =>:customer).where('customers.sales_id = ?', session[:user_id])

SQL code for #2 could be (SQL#2):

Log.joins(:customer_comm_record =>{ :customer => :projects}).where('projects.sales_id = ?', session[:user_id])

Here is what we come up (SQL#3) in one line:

Log.joins(:customer_comm_record =>{ :customer => :projects}).where('customers.sales_id = ? OR projects.sales_id = ?', session[:user_id], session[:user_id])

The SQL code returned by above in rails console is (replacing session[:user_id] with 1) :

 SELECT "logs".* FROM "logs" INNER JOIN "customer_comm_records" ON "customer_comm_records"."id" = "logs"."customer_comm_record_id" INNER JOIN "customers" ON "customers"."id" = "customer_comm_records"."customer_id" INNER JOIN "projects" ON "projects"."customer_id" = "customers"."id" WHERE (customers.sales_id = 1 OR projects.sales_id = 1)

The question is weather SQL#3 == SQL#1 UNION SQL#2. Can someone answer the question? If it is not, what's the right one? Thanks.

user938363
  • 9,990
  • 38
  • 137
  • 303

1 Answers1

2

The only differences are:

  • The first method will return duplicate entries where the sales_id for the customer and project both match the users id.
  • The second method might not return records where a customer with a matching sales_id does not have a project (no projects.customer_id for that customer).

The last difference only matters if a customer does not necessarily have any projects. Otherwise, the second method is better because it avoids dealing with duplicates.

If a customer does not always have a project, a simple workaround would be to use includes instead of joins:

Log.includes(:customer_comm_record =>{ :customer => :projects}).
    where('customers.sales_id = :id OR projects.sales_id = :id', id: session[:user_id])

This will force a LEFT JOIN and will return customers regardless of whether they are associated with a project.

PinnyM
  • 35,165
  • 3
  • 73
  • 81
  • Sorry, what's the first method and second method refer to? – user938363 Apr 25 '13 at 19:56
  • First method = SQL#1 UNION SQL#2. Second method = SQL#3. – PinnyM Apr 25 '13 at 19:57
  • OK. The difference makes sense. There may be case that the customer does not have project but may have logs (probably is very rare). Since there is no union in rails, is there a way to do SQL#1 UNION SQL#2 in rails? – user938363 Apr 25 '13 at 20:16
  • There is a way to make a UNION, but it's rather messy and unnecessary. You're better off using `includes` instead of `joins` which will use SQL#3 but with LEFT JOINs instead of INNER JOINs. That will avoid this problem. Updated to explain this. – PinnyM Apr 25 '13 at 20:26
  • The association between customer and project is customer has_many project and project belongs_to a customer. – user938363 Apr 25 '13 at 20:36
  • Updated to reflect this properly. – PinnyM Apr 25 '13 at 20:49
  • Thanks. We haven't used includes before. Is there performance issue with includes? – user938363 Apr 25 '13 at 21:12
  • In one sense, `includes` has more overhead because it actually returns the entire tree (instead of just the Log data) as eager loaded - which you might not want. On the other hand, it only hits the database once in this case (and faster than a SQL UNION too), so if you will be making use of the associated models, then this is the way to go. – PinnyM Apr 25 '13 at 21:21