Try to order by on the right table of left join, but get error like for SELECT DISTINCT, ORDER BY expressions must appear in select list

[zw963]

Basically, i build a SQL like following:

Query SELECT DISTINCT universities.id, ... universites.updated_at FROM universities left join chong_wen_baos on universities.id = chong_wen_baos.university_id ORDER BY chong_wen_baos.updated_at DESC NULLS LAST LIMIT 50 OFFSET 0

I built the order_by SQL use code like this:

custom_order = Avram::OrderBy.new("chong_wen_baos.updated_at", direction: :asc, nulls: :nulls_last)
query = query.order_by(custom_order)

But, as you can see, I only built the order by clause myself, but, pg complain, i need add same column into SELECT.

That is, it have to built following SQL to works.

--- 1.sql       2024-06-30 19:09:46.458199781 +0800
+++ 2.sql       2024-06-30 19:09:53.366575884 +0800
@@ -1 +1 @@
-Query SELECT DISTINCT universities.id, ... universites.updated_at FROM universities left join chong_wen_baos on universities.id = chong_wen_baos.university_id ORDER BY chong_wen_baos.updated_at DESC NULLS LAST LIMIT 50 OFFSET 0
\ No newline at end of file
+Query SELECT DISTINCT chong_wen_baos.updated_at, universities.id, ... universites.updated_at FROM universities left join chong_wen_baos on universities.id = chong_wen_baos.university_id ORDER BY chong_wen_baos.updated_at DESC NULLS LAST LIMIT 50 OFFSET 0.
\ No newline at end of file

So, is there a way to built above SELECT DISTINCT chong_wen_baos.updated_at manually?

Thanks.

[jwoertink]

For this case, you would just use raw SQL so you can fine tune exactly what you need. As long as the returning columns match up with a model, you can still cast to the model instance you need.

query = <<-SQL
SELECT DISTINCT chong_wen_baos.updated_at, universities.id, ... universites.updated_at FROM universities left join chong_wen_baos on universities.id = chong_wen_baos.university_id ORDER BY chong_wen_baos.updated_at DESC NULLS LAST LIMIT 50 OFFSET 0.
SQL

AppDatabase.query_all(query, as: ChongWenBaos)

[zw963]

Hi, I use Avram::Queryable objects for many others queries, as i show in this comment, so, if use AppDatabase.query_all instead, all my exists work need to be rewrite, right?

[jwoertink]

all my exists work need to be rewrite, right?

No. You can use raw SQL for this query and use Avram::Queryable for the other ones. I always use both in my projects.

[zw963]

How to concat this raw query with exists Queryable,and return a queryable object which used for paginate？ thanks Jeremy Woertink ***@***.***> 于 2024年7月2日周二 22:32写道：

…

all my exists work need to be rewrite, right? No. You can use raw SQL for this query and use Avram::Queryable for the other ones. I always use both in my projects. — Reply to this email directly, view it on GitHub <#1904 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAEGCBRSFXONANXUNYDD3EDZKK2XNAVCNFSM6AAAAABKEAQD32VHI2DSMVQWIX3LMV43SRDJONRXK43TNFXW4Q3PNVWWK3TUHM4TSMZXGY3TK> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[jwoertink]

If you use the raw query, you'll have to use raw pagination. Add LIMIT and OFFSET to your query. You can build pagination pages manually too for use in HTML

query = <<-SQL
SELECT .....
SQL
query_with_pagination = query + " LIMIT 10 OFFSET 0"
results = AppDatabase.query_all(query_with_pagination, as: ChongWenBaos)

total_count_query = <<-SQL
SELECT COUNT(1) FROM (#{query}) foo
SQL
total_count = AppDatabase.scalar(total_count_query).as(Int64)

pages = Lucky::Paginator.new(
      page: 1,
      per_page: 10,
      item_count: total_count,
      full_path: context.request.resource)

[zw963]

If you use the raw query, you'll have to use raw pagination. Add LIMIT and OFFSET to your query. You can build pagination pages manually too for use in HTML

Okay, thanks, but, how to use raw query with exists Queryable query? like following:

    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

    query = query.batch_level(batch_level) if batch_level.presence

    cwb_query = cwb_query.university_remark.is_not_nil if is_exists_remark

    unless search_all
      if is_excluded
        cwb_query = cwb_query.is_excluded(true)
      else
        cwb_query = cwb_query.where do |wh|
          # is_excluded.is_nil 代表 left join 右表中, 对应的冲稳保数据不存在
          wh.is_excluded(false).or(&.is_excluded.is_nil)
        end

        cwb_query = handle_marked(cwb_query)
      end
    end

  query = query.where_chong_wen_baos(cwb_query, auto_inner_join: false)

My question is, how to write raw SQL here for reuse above Queryable form query? what i mean is, concat the above results of AppDatabase.query_all( ...) with my above query?