Support write LEFT JOIN which include more than one condition use AND? #1048
Comments
|
Hi, Because I am eager to fix a bug that has been present in my project for a long time, so, i have to try solve this issue use a really dirty hack. please have a look. it's seem like work, the only downside is, i have to write join("....") manually before any following is PART OF complicated usage on my project (Please check the second to last line) cwb_query = ChongWenBaoQuery.new
query = UniversityQuery.new.preload_chong_wen_baos(cwb_query).preload_city
if q.presence
if q.matches? /^\d{4}$/
query = query.code(q)
else
query = query.where("(name &@~ ?", q)
.or(&.where_chong_wen_baos(
cwb_query.where("chong_wen_baos.university_remark &@~ ?)", q),
auto_inner_join: false
).left_join_chong_wen_baos)
end
end
query = query.is_985(true) if is_985
cwb_query = cwb_query.where do |wh|
wh.is_excluded(false).or(&.is_excluded.is_nil)
end
cwb_query = cwb_query.university_remark.is_not_nil if is_exists_remark
if is_marked ||
chong_2023 || chong_2022
wen_2023 || wen_2022
bao_2023 || bao_2022
cwb_query = cwb_query.is_marked(true) if is_marked
if cwb_union_set
cwb_query = cwb_query.where do |q|
q = q.none
q = chong_2023 ? q.or(&.chong_2023(true)) : q
q = chong_2022 ? q.or(&.chong_2022(true)) : q
q = wen_2023 ? q.or(&.wen_2023(true)) : q
q = wen_2022 ? q.or(&.wen_2022(true)) : q
q = bao_2023 ? q.or(&.bao_2023(true)) : q
q = bao_2022 ? q.or(&.bao_2022(true)) : q
q
end
else
cwb_query = cwb_query.chong_2023(true) if chong_2023
cwb_query = cwb_query.chong_2022(true) if chong_2022
cwb_query = cwb_query.wen_2023(true) if wen_2023
cwb_query = cwb_query.wen_2022(true) if wen_2022
cwb_query = cwb_query.bao_2023(true) if bao_2023
cwb_query = cwb_query.bao_2022(true) if bao_2022
end
end
# ...
query = query.join("left join chong_wen_baos on universities.id = chong_wen_baos.university_id AND chong_wen_baos.user_id = '#{current_user.id}'").where_chong_wen_baos(cwb_query, auto_inner_join: false)
pages, universities = paginate(query.id.desc_order.distinct, per_page: 50) |
|
I think you're on the right direction with #1049 but maybe this would work better similar to custom custom_order = Avram::OrderBy.new("EXTRACT(HOUR FROM created_at)", :asc)
UserQuery.new.order_by(custom_order)We can do custom joins with custom_join = Avram::Join::Left.new(from: :users, to: :messages, foreign_key: :receiver_id)
UserQuery.new.join(custom_join)So I think all we need at this point is maybe a custom_join = Avram::RawJoin::Left.new("chong_wen_baos on universities.id = chong_wen_baos.university_id AND chong_wen_baos.user_id = ?", current_user.id)
UniversityQuery.new.join(custom_join)or, maybe custom_join = Avram::Join::Left.new(from: :chong_wen_baos, to: :universities, foreign_key: :university_id)
custom_join.and("chong_wen_baos = ?", current_user.id)I'm not sure what the proper solution is here, but this is also not something I've ever needed. If you want to submit a PR with one of these options, we can give it a try. I would like to stay as close to using the |
Me too, BTW: i prefer always specify the join explicitly instead of join implicitly in the where_???, although this is a breaking change, but, more clearly, and make the implementation logic simpler. |
|
I don't think we need a breaking change for this update, but submit your PR and we will work through it. |
Agree, only a recommendation for the future. 😄 |
|
Hi, @jwoertink , i create a initial version of PR #1050, please have a look. It's seem like works on my laptop after a simple test, although, same issue as my previous hack still happen. for example: the last line of following code not work which cause a excluded_query = ChongWenBaoQuery.new.user_id(current_user.id).is_excluded(true)
query = UniversityQuery.new.preload_chong_wen_baos(excluded_query)
query = query.where_chong_wen_baos(excluded_query)I have to change from query = query.where_chong_wen_baos(excluded_query)Into query = query
.join("inner join chong_wen_baos on universities.id = chong_wen_baos.university_id")
.where_chong_wen_baos(excluded_query)To make sure the query is working. I'm curious why this happened because I just added some new code but without making any changes to the existing code related to Another concerns is, i am not running spec because i am out, no network to pull docker image for now, i will try add it later when this feature is start to working. |
|
I am running spec, see many spec failed about generated join automatically when use where_???. failed specI guess the reason cause this is, the new created
I have no idea about how to fix that. 😄 |
For discusstion, check #1901 and optional this
Following is a SQL example which i expected to generate.
university has_many chong_wen_baos
chong_wen_bao belongs_to university
As you can see, i use a AND condition with the LEFT JOIN ON, Above SQL output expected result, which includes all university, whatever whether there is record exists on the right table (the select chong_wen_baos.id is null if no right table record exists)
I don't know how to write SQL like above, current, I am using like this, but not work.
It generate SQL like this: (AND replaced with WHERE)
this is not what i want, because it is not return the left table records which no right table record exists.
I thought one of solution is, write join like the
wheremethod?Thanks.
The text was updated successfully, but these errors were encountered: