Method: Sequel::Dataset#join_table

Defined in:

lib/sequel/dataset/query.rb

#join_table(type, table, expr = nil, options = OPTS, &block) ⇒ Object

Returns a joined dataset. Not usually called directly, users should use the appropriate join method (e.g. join, left_join, natural_join, cross_join) which fills in the type argument.

Takes the following arguments:

type

The type of join to do (e.g. :inner)

table

table to join into the current dataset. Generally one of the following types:

String, Symbol

identifier used as table or view name

Dataset

a subselect is performed with an alias of tN for some value of N

SQL::Function

set returning function

SQL::AliasedExpression

already aliased expression. Uses given alias unless overridden by the :table_alias option.

expr

conditions used when joining, depends on type:

Hash, Array of pairs

Assumes key (1st arg) is column of joined table (unless already qualified), and value (2nd arg) is column of the last joined or primary table (or the :implicit_qualifier option). To specify multiple conditions on a single joined table column, you must use an array. Uses a JOIN with an ON clause.

Array

If all members of the array are symbols, considers them as columns and uses a JOIN with a USING clause. Most databases will remove duplicate columns from the result set if this is used.

nil

If a block is not given, doesn’t use ON or USING, so the JOIN should be a NATURAL or CROSS join. If a block is given, uses an ON clause based on the block, see below.

otherwise

Treats the argument as a filter expression, so strings are considered literal, symbols specify boolean columns, and Sequel expressions can be used. Uses a JOIN with an ON clause.

options

a hash of options, with the following keys supported:

:table_alias

Override the table alias used when joining. In general you shouldn’t use this option, you should provide the appropriate SQL::AliasedExpression as the table argument.

:implicit_qualifier

The name to use for qualifying implicit conditions. By default, the last joined or primary table is used.

:join_using

Force the using of JOIN USING, even if expr is not an array of symbols.

:reset_implicit_qualifier

Can set to false to ignore this join when future joins determine qualifier for implicit conditions.

:qualify

Can be set to false to not do any implicit qualification. Can be set to :deep to use the Qualifier AST Transformer, which will attempt to qualify subexpressions of the expression tree. Can be set to :symbol to only qualify symbols. Defaults to the value of default_join_table_qualification.

block

The block argument should only be given if a JOIN with an ON clause is used, in which case it yields the table alias/name for the table currently being joined, the table alias/name for the last joined (or first table), and an array of previous SQL::JoinClause. Unlike where, this block is not treated as a virtual row block.

Examples:

DB[:a].join_table(:cross, :b)
# SELECT * FROM a CROSS JOIN b

DB[:a].join_table(:inner, DB[:b], c: d)
# SELECT * FROM a INNER JOIN (SELECT * FROM b) AS t1 ON (t1.c = a.d)

DB[:a].join_table(:left, Sequel[:b].as(:c), [:d])
# SELECT * FROM a LEFT JOIN b AS c USING (d)

DB[:a].natural_join(:b).join_table(:inner, :c) do |ta, jta, js|
  (Sequel.qualify(ta, :d) > Sequel.qualify(jta, :e)) & {Sequel.qualify(ta, :f)=>DB.from(js.first.table).select(:g)}
end
# SELECT * FROM a NATURAL JOIN b INNER JOIN c
#   ON ((c.d > b.e) AND (c.f IN (SELECT g FROM b)))

# File 'lib/sequel/dataset/query.rb', line 550

def join_table(type, table, expr=nil, options=OPTS, &block)
  if hoist_cte?(table)
    s, ds = hoist_cte(table)
    return s.join_table(type, ds, expr, options, &block)
  end

  using_join = options[:join_using] || (expr.is_a?(Array) && !expr.empty? && expr.all?{|x| x.is_a?(Symbol)})
  if using_join && !supports_join_using?
    h = {}
    expr.each{|e| h[e] = e}
    return join_table(type, table, h, options)
  end

  table_alias = options[:table_alias]

  if table.is_a?(SQL::AliasedExpression)
    table_expr = if table_alias
      SQL::AliasedExpression.new(table.expression, table_alias, table.columns)
    else
      table
    end
    table = table_expr.expression
    table_name = table_alias = table_expr.alias
  elsif table.is_a?(Dataset)
    if table_alias.nil?
      table_alias_num = (@opts[:num_dataset_sources] || 0) + 1
      table_alias = dataset_alias(table_alias_num)
    end
    table_name = table_alias
    table_expr = SQL::AliasedExpression.new(table, table_alias)
  else
    table, implicit_table_alias = split_alias(table)
    table_alias ||= implicit_table_alias
    table_name = table_alias || table
    table_expr = table_alias ? SQL::AliasedExpression.new(table, table_alias) : table
  end

  join = if expr.nil? and !block
    SQL::JoinClause.new(type, table_expr)
  elsif using_join
    raise(Sequel::Error, "can't use a block if providing an array of symbols as expr") if block
    SQL::JoinUsingClause.new(expr, type, table_expr)
  else
    last_alias = options[:implicit_qualifier] || @opts[:last_joined_table] || first_source_alias
    qualify_type = options[:qualify]
    if Sequel.condition_specifier?(expr)
      expr = expr.map do |k, v|
        qualify_type = default_join_table_qualification if qualify_type.nil?
        case qualify_type
        when false
          nil # Do no qualification
        when :deep
          k = Sequel::Qualifier.new(table_name).transform(k)
          v = Sequel::Qualifier.new(last_alias).transform(v)
        else
          k = qualified_column_name(k, table_name) if k.is_a?(Symbol)
          v = qualified_column_name(v, last_alias) if v.is_a?(Symbol)
        end
        [k,v]
      end
      expr = SQL::BooleanExpression.from_value_pairs(expr)
    end
    if block
      expr2 = yield(table_name, last_alias, @opts[:join] || EMPTY_ARRAY)
      expr = expr ? SQL::BooleanExpression.new(:AND, expr, expr2) : expr2
    end
    SQL::JoinOnClause.new(expr, type, table_expr)
  end

  opts = {:join => ((@opts[:join] || EMPTY_ARRAY) + [join]).freeze}
  opts[:last_joined_table] = table_name unless options[:reset_implicit_qualifier] == false
  opts[:num_dataset_sources] = table_alias_num if table_alias_num
  clone(opts)
end