How to write LEFT JOIN which include more than one condition use AND?

[zw963]

Following is a SQL example which i expected to generate.

university has_many chong_wen_baos
chong_wen_bao belongs_to university

SELECT universities.*, chong_wen_baos.id FROM universities LEFT JOIN chong_wen_baos ON universities.id = chong_wen_baos.university_id AND chong_wen_baos.user_id = 1;

As you can see, i use a AND condtion 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.

query = query
      .where_chong_wen_baos(ChongWenBao.new.user_id(current_user.id), auto_inner_join: false)
      .left_join_chong_wen_baos

It generate SQL like this: (AND replaced with WHERE)

SELECT universities.*, chong_wen_baos.id FROM universities LEFT JOIN chong_wen_baos ON universities.id = chong_wen_baos.university_id WHERE chong_wen_baos.user_id = 1;

this is not what i want, because it is not return the left table records which no right table record exists.

Thanks.

[jwoertink]

Currently this isn't supported. https://github.com/luckyframework/avram/blob/3a27ebb093d52585f6fd25f6ff55672888d3a6d7/src/avram/join.cr#L27-L31

We would have to add in a way to handle this. For now I would suggest just using raw SQL. I thought we had an issue for this already, but I guess we don't. You can open an issue for it if you would like.

[zw963]

Done. luckyframework/avram#1048

Hi, @jwoertink , could you please give a workaround(or a patch) which can work well with exists Avram::Queryable object? because i have many others complicated query, and use paginate, i don't want rewrite everything use raw sql.

Following is a example:

cwb_query = ChongWenBaoQuery.new
query = UniversityQuery.new.preload_chong_wen_baos(cwb_query).preload_city
query = query.code(code) if code

# I want `query` left outer join cwb_query here use above two condition.
# universities LEFT JOIN chong_wen_baos 
# ON universities.id = chong_wen_baos.university_id 
# AND chong_wen_baos.user_id = 1;
query = query.left_join(????)

# ...

pages, universities = paginate(query.id.desc_order.distinct, per_page: 50)

[zw963]

Let us discuss there. luckyframework/avram#1048