Activerecord or Raw SQL

Question

I have some RAW sql and I'm not sure if it would be better as an Activerecord call or should I use RAW sql. Would this be easy to convert to AR?

select  *
from    logs t1
where
  log_status_id = 2 and log_type_id = 1
        and not exists
        (
        select  *
        from    logs t2
        where   t2.log_version_id = t1.log_version_id
                and t2.log_status_id in (1,3,4)
                and log_type_id = 1
        )
ORDER BY created_at ASC

So something like this?:

Log.where(:log_status_id=>2, log_type_id => 1).where.not(Log.where.....)

Marcus Ilgner · Accepted Answer · 2017-05-22T14:49:06.077

You could do this using AREL. See Rails 3: Arel for NOT EXISTS? for an example.

Personally I often find raw SQL to be more readable/maintainable than AREL queries, though. And I guess most developers are more familiar with it in general, too.

But in any case, your approach to separate the narrowing by log_states_id and log_type_id from the subquery is a good idea. Even if your .where.not construct won't work as written.

This should do the trick however:

Log.where(log_status_id: 2, log_type_id: 1)
   .where("NOT EXISTS (
    select *
    from    logs t2
    where   t2.log_version_id = logs.log_version_id
            and t2.log_status_id in (1,3,4)
            and t2.log_type_id = logs.log_type_id)")
   .order(:created_at)

The only constellation where this might become problematic is when you try to join this query to other queries because the outer table will likely receive a different alias than logs.

how would the NOT EXISTS in the where look? I assume i wouldn't have t2, t1 ? — user3437721, May 22 '17 at 14:45
The main table will usually (that's why I added the cautionary last sentence) keep its name so you can reference it from your subquery. I updated my answer to show the full query. — Marcus Ilgner, May 22 '17 at 14:50

Activerecord or Raw SQL

1 Answers1