How to search through joined table aliases

xfalcon727 commented 5 years ago

I have one model that I establish a connection with through Amazon AWS RDS.

In my Events model, I have a scope as follows:

scope :scope_subquery, -> {
    table1 = Event.select("name, date").where("accepted > 0").group("name").to_sql
    table2 = Event.select("distinct on (name) *").where('accepted > 0').order('name, eventdate asc').to_sql
    table3 = Event.select("distinct on (name) name, eventname").where('accepted > 0').order('name, accepted desc').to_sql

    Event.select("tb2.createddates, tb2.photo_link, tb3.eventname, tb1.venues, tb2.accepted, tb2.rejected").from("(#{table1}) tb1 JOIN (#{table2}) tb2 on tb2.name = tb1.name JOIN (#{table3}) tb3 on tb1.name = tb3.name").where("tb1.venues >= 1")
  }

In my Event datatable, I have the following:

def view_columns
    # Declare strings in this format: ModelName.column_name
    # or in aliased_join_table.column_name format
    @view_columns ||= {
      name: { source: "Event.name", orderable: true, searchable: true },
      eventname: { source: "Event.eventname", searchable: true },
      accepted:  { source: "Event.accepted", searchable: false }
    }
end

def get_raw_records
    Event.scope_subquery
end

I get an error:

ActiveRecord::StatementInvalid (PG::UndefinedTable: ERROR: missing FROM-clause entry for table "event"

Am I suppose to includes and reference the alias tb1, tb2, and tb3?

For me to search the eventname, I would need to do the following:

Event.select("tb2.createddates, tb2.photo_link, tb3.eventname, tb1.venues, tb2.accepted, tb2.rejected").from("(#{table1}) tb1 JOIN (#{table2}) tb2 on tb2.name = tb1.name JOIN (#{table3}) tb3 on tb1.name = tb3.name").where("tb1.venues >= 1").where("tb3.eventname like '%Wedding%'")

where I added the query

.where("tb3.eventname like '%Wedding%'")

to the end.

This works, but I'm not sure how to reference the tb3 alias.

I can't reference tb3 in the source like this:

eventname: { source: "tb3.eventname", searchable: true }

Anyone know how I can solve this? I've been stuck on this for a couple of days already. Thanks!