0

I am really new to Rails, but I had some experience with sql, so right now I am really struggling with doing a simple thing in rails syntax.

So, there are two tables:

class WorkshopMetadata < ActiveRecord::Base
  attr_accessible :uuid, :action
  belongs_to :workshop
end

class Workshop < ActiveRecord::Base
  has_many :workshop_metadatas
end

And the query I want to do is:

SELECT workshops.*
FROM workshops LEFT JOIN
(SELECT workshop_metadatas.workshop_id as id, workshop_metadatas.uuid 
        FROM workshop_metadatas WHERE uuid = 'smth') as metadatas
WHERE uuid IS NULL

I know that to do left join you have to use includes, but how do I include query, not the table? I am completely baffled by this.

Thank you!

P.S. And while we are at it, are there any good and comprehensive docs for rails? The one that are listing all the available arguments for includes method, for example.

opportunato
  • 417
  • 4
  • 14

1 Answers1

0

I'd recommend reading through the ActiveRecord query interface guide - it's a bit verbose, but it's got a lot of great pointers! For the exhaustive API reference I'd normally point you to http://api.rubyonrails.org/, but it's pretty bare for ActiveRecord::QueryMethods :( The guide is the best bet...

Let me rephrase your query to make sure I have it correct: You want to select all workshops that do not have a metadata row with a uuid of smth?

Thankfully, rails lets you drop down to query fragments, so you should be able to do it via:

Workshop.joins(
  'LEFT JOIN workshop_metadatas ON workshop_metadatas.workshop_id = workshops.id'
).where('uuid IS NULL')

(Isn't that expressing the same thing w/o the subquery? If not: you should be able to pass your subquery into the joins call)

Nevir
  • 7,951
  • 4
  • 41
  • 50
  • Yeah, that is pretty close to what i was looking for. But is there any way to do this subquery with rails native tools, without using plainsql? – opportunato Jan 28 '13 at 14:00
  • Take a look at http://stackoverflow.com/questions/5483407/subqueries-in-activerecord – Nevir Jan 28 '13 at 17:07
  • This answer shows exactly how to include subqueries: http://stackoverflow.com/a/21557060 – Eric H. Oct 01 '14 at 16:12