mysql - Complex Sql Query to RoR ORM -

I have a very complex SQL query that I would like to convert to the ORR of ROR.

  Select c. * From CID = TC on Four Companie AS TC in C Inner Tags as * from (from select companies. * Companies where the city = "?" And country = "?") TiI on Companie Inner Tag = TC TID t where Name REGEXP '?'  

I have defined such models:

companies.rb

  class company < ActiveRecord :: Base # ... is some code that does not matter has_and_belongs_to_many: Tag # ... some other code end  

and tags. RB

  class tag & lt; ActiveRecord :: Base has_and_belongs_to_many: End of Company  

I need a function in the Companies Controller that searches for companies like above queries.

option:

first : Find_by_sql ()

Description: Allows you to enter any query on that.

Second : combination .where () and .joins () method

But be careful, if you call .joins () with a .where () with a zero return, you get an error Will be undefined way. The solution here will be the first test if .where () returns anything, then you can join another table. Possible methods of using

join:

joins (: tags) join an inner

joins (Join 'left Foo ...') enables you to use the left outer

connects (tags: formcompanenx :: tags) nested Joins if you have N association from N / P