How to join a table, without implicitly adding the columns of the joined table in select clause?

[wuarmin]

Hello,
I'm struggling with the following query in a Sequel Model class:

module Models
  class Reservation < Sequel::Model(:reservations)
    one_to_many :prices
    one_to_many :details

    def number
      values[:number]
    end

    dataset_module do
      def all_filtered_by_numbers(numbers)
        select_append(Sequel[:bookings][:number].as(:booking_number))
          .join(:bookings, id: :booking_id)
          .where(
            Sequel[:bookings][:number] => numbers,
          ).qualify(:r).all
      end
    end
  end
end

The resulting query is:

SELECT *, "bookings"."number" AS "booking_number"
FROM "public"."reservations"
INNER JOIN "bookings" ON ("bookings"."id" = "public"."reservations"."booking_id")
WHERE ("bookings"."number" IN ('1', '2'))

The generated query does not work, because reservations and bookings have columns with same name. The SELECT * has to be a SELECT reservations.* I need following query:

SELECT "reservations".*, "bookings"."number" AS "booking_number"
FROM "public"."reservations"
INNER JOIN "bookings" ON ("bookings"."id" = "public"."reservations"."booking_id")
WHERE ("bookings"."number" IN ('1', '2'))

Thanks and best regards

[adam12]

You could modify the select manually or just use the table_select plugin.

[wuarmin]

Thank you for the fast response. table_select-plugin solved the issue. Can you show, how you can change the select *-behavior manually?

[janko]

It should be enough to call #qualify on the dataset:

DB[:items]           # SELECT * FROM items
  .where(foo: "bar") # SELECT * FROM items WHERE foo = "bar"
  .qualify           # SELECT items.* FROM items WHERE items.foo = "bar"

[wuarmin]

just using #qualify did not solve the issue.

[adam12]

Try qualifying first, before your select_append call.

[wuarmin]

Thanks. You are right. If select_append is called after qualify it works.

def all_filtered_by_numbers(numbers)
  qualify.select_append(Sequel[:bookings][:number].as(:booking_number))
    .join(:bookings, id: :booking_id)
    .where(
      Sequel[:bookings][:number] => numbers,
    ).all
end