rails way to write a complex query

Question

I have this query:

SELECT f.id, 
       Concat(f.name, ' ', REPLACE(f.parent_names, ',', ' ?'))        AS FullName, 
       u.name                                                         AS Unit, 
       u.id                                                           AS UnitId, 
       u.position                                                     AS UnitPosition, 
       city.name                                                      AS City, 
       hus.mobile1                                                    AS HusMobile, 
       wife.mobile1                                                   AS WifeMobile, 
       hus.first_name                                                 AS Spouse1, 
       wife.first_name                                                AS Spouse2, 
       f.phone                                                        AS HomePhone, 
       f.email                                                        AS Email, 
       Date_format(f.contact_initiation_date, '%d/%m/%Y')             AS InitDate, 
       f.contact_initiation_date                                      AS sql_date, 
       Date_format(f.status_change_date, '%d/%m/%Y')            AS StatususChangeDate, 
       stts.name                                                      AS 'Status', 
       (SELECT Group_concat(' ', t.name, '<', t.id, '>' ORDER BY t.position DESC 
               ) 
        FROM   taggings tgs 
               JOIN tags t 
                 ON tgs.tag_id = t.id 
        WHERE  tgs.taggable_type = 'family' 
               AND tgs.taggable_id = (SELECT DISTINCT taggable_id 
                                      FROM   taggings 
                                      WHERE  tag_id IN( 76, 72, 74 ) 
                                             AND taggable_id = f.id)) AS HandlingStatus, 
       Date_format(f.reconnection_date, '%d/%m/%Y')               AS ReconnectionDate, 
       f.reconnection_date                                   AS reconnection_sql_date, 
       Date_format(cmt.created_at, '%d/%m/%Y')                        AS CommentDate, 
       cmt.comment                                                    AS LastComment, 
       usr.name                                                       AS Comentator, 
       Format(e.income_total, 0)                                      AS Income, 
       Format(e.expense_total, 0)                                     AS Expense, 
       Format(e.debts_total, 0)                                       AS Debts 
FROM   families f 
       JOIN categories stts 
         ON f.family_status_cat_id = stts.id 
       JOIN units u 
         ON f.unit_id = u.id 
       JOIN categories city 
         ON f.main_city_cat_id = city.id 
       LEFT JOIN comments cmt 
              ON f.last_comment_id = cmt.id 
       LEFT JOIN contacts hus 
              ON hus.id = f.husband_id 
       LEFT JOIN contacts wife 
              ON wife.id = f.wife_id 
       LEFT JOIN users usr 
              ON usr.id = cmt.user_id 
       LEFT JOIN escort_requests e 
              ON e.family_id = f.id 
WHERE  ( 1 = 0 
          OR ( u.is_busy = 0 
               AND f.family_status_cat_id = 1421 ) 
          OR ( u.is_busy = 1 
               AND f.family_status_cat_id = 1421 ) 
          OR ( f.family_status_cat_id = 1423 ) 
          OR ( f.family_status_cat_id = 1424 ) ) 
HAVING ( 1 = 1 
         AND handlingstatus IS NOT NULL 
         AND fullname LIKE '%%' ) 
ORDER  BY fullname 
LIMIT  0, 100

How do I write it in Rails? Right now I build an array with a string, but is there a rails way to do it better? If there is no better way, just mention it.

The values in the line: WHERE tag_id IN( 76, 72, 74 ) are being created dynamically.

@Baboon What system are you thinking of where a left join is problematic? — David Aldridge, Jun 06 '13 at 15:31

score 1 · Accepted Answer · answered Jun 06 '13 at 15:32

1

I've been trying to integrate some complex SQL with Rails, and found no better way than to run the SQL directly. Anything that strays away from a very simple query can get very difficult to code (and understand) in activerecord.

answered Jun 06 '13 at 15:32

David Aldridge

51,479
8
68
96

save that query as a view and create a readonly active record model should do it – Marian Theisen Jun 06 '13 at 15:35
Not a bad plan, but can be problematic if values need to be included in the query but they can't be applied by querying the view -- "tgs.taggable_type = 'family'" being an example. Furthermore, if diagnosing errors across models, views and controllers was not quite driving you insane then encapsulating code at the db level could tip you over the edge. – David Aldridge Jun 06 '13 at 15:40
@DavidAldridge - Thanks for speaking out what I was thinking! – Noam B. Jun 06 '13 at 20:55

rails way to write a complex query

1 Answers1

Linked