Hi, I want to translate the following query to a query inside Ash with my resource:
select buy_record.transaction_id as buy_transaction,
sell_record.transaction_id as sell_transaction,
buy_record.external_id,
buy_record.first_grantor_full_name as original_seller,
buy_record.first_grantee_full_name as buyer,
sell_record.first_grantor_full_name as seller,
sell_record.first_grantee_full_name as next_buyer,
coalesce(buy_record.recording_date, buy_record.instrument_date) as buy_date,
coalesce(sell_record.recording_date, sell_record.instrument_date) as sell_date
from raw.records as buy_record
left join raw.records as sell_record
on sell_record.external_id = buy_record.external_id and
sell_record.first_grantor_full_name = buy_record.first_grantee_full_name and
coalesce(sell_record.recording_date, sell_record.instrument_date) is not null and
coalesce(sell_record.recording_date, sell_record.instrument_date) >= coalesce(buy_record.recording_date, buy_record.instrument_date) and
not sell_record."deleted?"
where buy_record.first_grantee_full_name %> 'vinh truong' and
coalesce(buy_record.recording_date, buy_record.instrument_date) is not null and
not buy_record."deleted?" and
coalesce(buy_record.recording_date, buy_record.instrument_date) >= now() - interval '2' year
order by buy_record.recording_date
The important part is the left join one.
To do something similar to this, I added the following to my resource:
relationships do
alias Pacman.Raw.Record
has_one :sell_record, Record do
no_attributes? true
filter expr(
external_id == parent(external_id)
and first_grantor_full_name == parent(first_grantee_full_name)
and not is_nil(recording_date || instrument_date)
and (recording_date || instrument_date) >= (parent(recording_date) || parent(instrument_date))
and not deleted?
)
end
...
read :by_entity do
argument :entity_full_name, :string, allow_nil?: false
filter expr(fragment("(? %> ?)", first_grantee_full_name, ^arg(:entity_full_name))
and not is_nil(recording_date || instrument_date)
and (recording_date || instrument_date) >= ago(2, :year)
and not deleted?)
prepare build(sort: [:recording_date], load: [:sell_record])
end
Ash will split these into two queries, one loading the resource with the filter and then another one to load the relationship. That query is pretty big and get truncated in my terminal, so I can’t easily see what is doing, but what is returning is not correct.
My guess is that the way relationships in Ash works is somewhat different from what I expected and so the above code is not equivalent to the left join from the SQL query.
Do someone knows what I’m doing wrong here and how to make this work as expected?
need to figure that one out…
