Convert SQL statement to Ruby on Rails

I've got two tables, and I want to compare the data between them, and pic开发者_JAVA技巧k out the data from one table which is not present in the other. I already have a code that works in SQL Server Management, but I need to convert it into Rails or I need to be able to use raw SQL code in my code. Here is the code:

select * from app_servers
where not exists 
( select app, environment, server 
            from stagings
            where stagings.server = app_servers.server_id
            AND
            stagings.environment = app_servers.environment_id 
            AND 
            app_servers.app_id = stagings.app
)

Thanks in advance

Why do you want to convert it? You might use it as it is

AppServer.where(" not exists 
      ( select app, environment, server 
        from stagings
        where stagings.server = app_servers.server_id
        AND
        stagings.environment = app_servers.environment_id 
        AND 
        app_servers.app_id = stagings.app
      )
")

If I understand correctly you have two models, AppServer and Staging and both have an environment, server and an application. You are looking for AppServer's that do not have a corresponding Staging.

So your models would look like:

class AppServer
  belongs_to :server
  belongs_to :environment
  belongs_to :app
end

class Staging
  belongs_to :server
  belongs_to :environment
  belongs_to :app
end

But what you actually would want, is something like

class AppServer 
  belongs_to :server
  belongs_to :environment
  belongs_to :app
  has_one :staging
end

which would be really easy to test. Something like:

AppServer.where(:staging_id => nil)

So you could consider reforming your datamodel to make this easier. This would not be too hard: add a single column, and for each app_server find the corresponding staging.

But suppose you do not have any control over your datamodel, you would need to write something like

class AppServer
  has_many :stagings, finder_sql => 'select * from stagings where server=#{server_id} and environment=#{environment_id} and app=#{app_id}'

Note: you must use single quotes!! This would at least allow you to access something like

app_server = AppServer.first
app_server.stagings

Unfortunately, it does not allow you to write something like

AppServer.where(:stagings => nil)

To find all AppServer without staging, and you cant convert the schema, you will need to do something like

AppServer.where(" not exists 
  ( select app, environment, server 
    from stagings
    where stagings.server = app_servers.server_id
    AND
    stagings.environment = app_servers.environment_id 
    AND 
    app_servers.app_id = stagings.app
  )"
)

So actually, in the end, I did not find a new and improved way using arel. But I did show some ways to allow making use of some rails helpers, and secondly it seems a good approach, if possible (even using views), to convert your datamodel to a more rails-friendly model. One good reason is that the rails way of creating data models is actually a pretty good way.