rails 编写复杂查询的方法 [英] rails way to write a complex 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 

我如何在 Rails 中编写它?现在我用字符串构建一个数组，但是有没有更好的方法?如果没有更好的方法，就提一下.

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.

行中的值: WHERE tag_id IN( 76, 72, 74 ) 正在动态创建.

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

推荐答案

我一直在尝试将一些复杂的 SQL 与 Rails 集成，但没有找到比直接运行 SQL 更好的方法.任何偏离非常简单查询的内容都可能很难在 activerecord 中编码(和理解).

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.

这篇关于rails 编写复杂查询的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！