Is there a way to select columns on two left joined tables use BaseQuery?

[zw963]

Following is a example.

SELECT universities.id, name, chong_wen_baos.university_remark
FROM universities left join chong_wen_baos on universities.id = chong_wen_baos.university_id;

Sure i expect to merge with others where conditions on two tables, conditionally, like this:

Add "where universities.name = 'foo'" if foo
Add "where chong_wen_baos.university_remark = 'bar'" if bar

so, use BaseQuery instead of raw SQL is preferred.

I checked the document, no see any related usage documented.

Thanks

[zw963]

I test myself, hardly, use Avram::QueryBuilder again, the generated SQL is correct, but failed when try convert it to Avram::Queryable in the last.

builder = Avram::QueryBuilder.new(table: :universities)
builder = builder.join(Avram::Join::Left.new(:universities, :chong_wen_baos))
builder = builder.select_direct(
  [
    "universities.id", "code", "name","chong_wen_baos.university_remark"
  ])

builder = builder.where(Avram::Where::Raw.new("chong_wen_baos.university_remark is not null")) if is_exists_description

if q.presence
    builder = builder.where(Avram::Where::Raw.new("(name &@~ ? OR chong_wen_baos.university_remark &@~ ?)", q, q))
end

query = UniversityQuery.new.left_join_chong_wen_baos
query.query = builder

With following error:

Unknown column: university_remark
deserializing University

Because i need use paginate, so, i have to convert it to Queryable.

[zw963]

The really key issue is, i have to query use OR on a column in the left joined table, but i don't know how to add it use Queryable.

builder = builder.where(Avram::Where::Raw.new("(name &@~ ? OR chong_wen_baos.university_remark &@~ ?)", q, q))

as you can see, i don't know how to build above OR SQL use Queryable because it use columns in two tables.

[jwoertink]

This is not really something supported in Avram. The issue with allowing for custom selects is that id : PrimaryKey is required, but if you don't select it, it would be nil (like with ActiveRecord). This breaks type-safety rules. So for this reason, you would want to use custom raw SQL to do this.

query = <<-SQL
SELECT one, two
FROM x
LEFT JOIN a
....
SQL

AppDatabase.query_all(query,args: [1, 2], as: Foo)

[zw963]

Hi, @jwoertink , in fact, i figure out how to solve my issue after fight with it one day, i don't want to select column in two table, i just don't know how to OR query on two table columns

following is the the expected answer.

user_chong_wen_bao_query = ChongWenBaoQuery.new.user_id(current_user.id)
query = UniversityQuery.new.preload_chong_wen_baos(user_chong_wen_bao_query)

    query = query.where("(name &@~ ?", q)
                .or(
                  &.where_chong_wen_baos(
                    ChongWenBaoQuery.new.where("chong_wen_baos.university_remark &@~ ?)", q),
                    auto_inner_join: false
                  ).left_join_chong_wen_baos
                )

As you can see, there is no document for usage of

.or(&.where_chong_wen_baos( ...))

Yes, use a join in the or( .... ), do you consider add this into document?