1

3 things: - Logistic has_many RentalRequests through Type_Logistic association table. - Logistic does not have to have a RentalRequest to exist (i.e., there's no Type_Logistic association present) - RentalRequest has a status_id column

I would like to find all the Logistic records where EITHER there are no associated RentalRequests OR the associated RentalRequest has a status_id of nil. My current solution is multi-step:

assoc_RR_has_nil_status = Logistic.includes(:rental_requests).where(rental_requests: {status_id: nil}).pluck(:id)
no_assoc_RR = Logistic.includes(:rental_requests).where(rental_requests: {id:nil}).pluck(:id)
inc_logistics_ids = assoc_RR_has_nil_status | no_assoc_RR
@Incomplete_logistics = Logistic.find(inc_logistics_ids.sort)

But I'm wondering if there's a way to do this in one single where or chain of wheres.

Thanks!

EDIT! Updated my code above because the original merge represents an AND not an OR.

james
  • 3,989
  • 8
  • 47
  • 102
  • Consider adding a `counter_cache` for RentalRequest to Logistic (see [section 4.1.2.3](http://guides.rubyonrails.org/association_basics.html#belongs-to-association-reference)). Then you can do `Logistic.includes(:rental_requests).where('logistics.rental_requests_count = 0 OR rental_requests.status_id IS NULL')` – AbM Oct 17 '15 at 03:38
  • interesting suggestion, have not thought about it, thanks! – james Oct 17 '15 at 16:50

1 Answers1

0

Don't be afraid to use SQL. It is a great tool!

Logistic.
  joins("LEFT OUTER JOIN type_logistics ON type_logistics.logistic_id = logistics.id").
  joins("LEFT OUTER JOIN rental_requests ON type_logistics.rental_request_id = rental_requests.id").
  where("rental_requests.id IS NULL OR rental_requests.status_id IS NULL")

If you must avoid SQL:

It is possible to generate the same query as above using just Ruby code (no SQL snippets). For example you could use ARel, such as is suggested in this SO discussion.

ARel is great for constructing queries at a high level of abstraction. For the majority of cases I've encountered it is much simpler to use SQL snippets than ARel. Choose whichever is best for your use case.

Community
  • 1
  • 1
Wizard of Ogz
  • 12,543
  • 2
  • 41
  • 43
  • you're right, I use pure SQL so rarely I never remember it lol. Here though, I get an error that something is wrong with the syntax `ActiveRecord::StatementInvalid: PG::Error: ERROR: syntax error at or near "WHERE"` – james Oct 17 '15 at 17:01
  • also please note I updated my solution a bit. I need whatever solution is better to match the new output. It was due to a misunderstanding on my part of `merge` – james Oct 17 '15 at 17:12
  • I've updated my answer to correct the joins, assuming that your table names and foreign keys follow rails conventions. Please change the table names and column names as needed. – Wizard of Ogz Oct 17 '15 at 19:43