Copytime

Rails 兩個欄位合併後排序

Daniel · 2016-02-24 · 978 Visits · Edit

SQl 語句對 left join 來的兩個表裡的某一個欄位進行合併排序，兩個表裡都有 created_at 欄位，優先選擇其中一個表的這個欄位，如果沒有就用另一個表的欄位，在 SQL 裡可以用 case when .. then .. else .. end as column_name 的方式來合併兩個欄位作為一個輸出，合併後就可以 order by column_name 來排序了

``` ruby
	# distinct
    scope :pass_user, lambda {|user|
        select('documents.*, case when table_bs.created_at is not NULL then table_bs.created_at else table_as.received_at end as act_time')
        .joins('LEFT JOIN "table_cs" ON "table_cs"."doc_id" = "documents"."id" LEFT JOIN "table_as" ON "table_as"."id" = "table_cs"."table_a_id" LEFT JOIN "table_bs" ON "table_bs"."document_id" = "documents"."id"') # .joins(:table_as, :table_bs)   # this become INNER JOIN, but we need LEFT JOIN
        .merge(TableA.non_deleted)
        .merge(TableB.non_deleted)
        .where("table_as.recipient_id = :user_id
               or table_as.keeper_id = :user_id
               or table_bs.user_id = :user_id", user_id: user.id)
        .group("documents.id")
        .order("act_time desc")
    }

```

關於 distinct 和 group by 的性能差別:

http://stackoverflow.com/questions/7943957/huge-performance-difference-when-using-group-by-vs-distinct

> The two queries express the same question. Appearantly the query optimizer chooses two different execution plans. My guess would be that the distinct is executed like:
>
> * Copy all business_key values to a temporary table
* Sort the temporary table
* Scan the temporary table, returning each item that is different from the one before it
> 
> The group by could be executed like:
> 
> * Scan the full table, storing each value of business key in a hashtable
> * Return the keys of the hashtable
> * The first method optimizes for memory usage: it would still perform reasonably well when part of the temporary table has to be swapped out. The second method optimizes for speed, but potentially requires a large amount of memory if there are a lot of different keys.
> 
> Since you either have enough memory or few different keys, the second method outperforms the first. It's not unusual to see performance differences of 10x or even 100x between two execution plans.
>

總而言之，如果內存夠，用 group by 比較好。

[Read More]

---- END ----

Rails 兩個欄位合併後排序

Recent Posts