Last Update: 2014-02-28 09:33:59 -0800

New Features

  • A one_through_one association type has been added. This is similar to the many_to_many association type in that it uses a join table, but it returns a single record instead of an array of records. This is designed for cases where the foreign key in the join table that references the current table has a unique constraint, or where you want to use an order to just pick the first matching record.

    Similarly, the many_through_many plugin now also offers a one_through_many association.

  • An association_join method has been added to model datasets, for setting up joins based on associations. This basically does the same join that eager_graph would do, but does not make the other changes that eager_graph makes.

    Unlike eager_graph (which uses LEFT OUTER JOINs by default), association_join uses INNER JOINs, but there are also association_*_join methods (e.g. association_left_join) for using different join types.

    Similar to eager_graph, you can use cascading of associations or multiple associations.

    Album.association_join(:artist, :tracks)
  • Dataset#eager_graph_with_options has been added for model datasets. It currently supports a :join_type option, for overriding the type of join to use on a per-call basis, as well as a :limit_strategy option. The API is similar to eager_graph, except that the associations to eagerly load are passed in as a single argument, and it takes an options hash.

    The :limit_strategy option works similarly to the :eager_limit_strategy option when eagerly loading. If set to true and the database supports window functions, it will join the current dataset to a subquery that uses a window function to correctly restrict the join to only those objects that fall within the association’s limit/offset.

    The :limit_strategy option is not on by default. It is possible for it to perform significantly worse than the default strategy (which uses array slicing in ruby). The :limit_strategy significantly changes the SQL used, and can change the results of the query if any filters/orders related to the association are used.

    It’s recommended you only use the :limit_strategy option if you are experiencing a bottleneck and you have benchmarked that it is faster and still produces the desired results.

    # SELECT artists.id, artists.name,
    #   first_10_albums.id AS first_10_albums_id,
    #   first_10_albums.name AS first_10_albums_name,
    #   first_10_albums.artist_id,
    #   first_10_albums.release_date
    # FROM artists
    #   SELECT id, name, artist_id, release_date
    #   FROM (
    #     SELECT *, row_number() OVER (PARTITION BY tracks.album_id)
    #       AS x_sequel_row_number_x
    #     FROM albums
    #   ) AS t1 WHERE (x_sequel_row_number_x <= 10)
    # ) AS first_10_albums ON (first_10_albums.artist_id = artists.id)
  • Dataset#full_text_search on PostgreSQL now supports :plain and :phrase options. :plain takes the search terms as a single string, and searches for rows where all terms are used. :phrase is similar to :plain, but also adds a substring search to ensure that the string given appears verbatim in the text.

  • A :graph_order association option has been added, for using a different order when using eager_graph. This is mostly designed for cases where :order should be qualified in other cases, but using a qualification breaks eager_graph because the correct qualifier is not known until runtime.

  • SQL::AliasedExpression#alias has been added as an alias for aliaz.

Other Improvements

  • Sequel will now automatically use an eager limit strategy for *_one associations that use an :order option. For associations that are truly one-to-one, an :order option is not needed, so it only makes sense to have an :order option if the association could theoretically return multiple results (in which case an eager limit strategy is helpful).

  • The queries that Sequel uses to filter by associations when those associations have conditions are now simpler and easier for the database to execute.

  • The queries that Sequel uses for dataset associations now handle cases where unqualified identifiers were used in the receiving dataset that would be made ambiguous by a join.

  • A limit strategy is now used when filtering by associations if the association has a limit and the database supports window functions. This allows Sequel to setup a correct filter in such cases.

    # SELECT *
    # FROM artists
    # WHERE (artists.id IN (
    #   SELECT albums.artist_id
    #   FROM albums
    #   WHERE ((albums.artist_id IS NOT NULL) AND (albums.id IN (
    #     SELECT id FROM (
    #       SELECT albums.id, row_number() OVER
    #         (PARTITION BY albums.artist_id ORDER BY release_date)
    #         AS x_sequel_row_number_x
    #       FROM albums
    #     ) AS t1
    #     WHERE (x_sequel_row_number_x <= 10)
    #   )) AND (albums.id = 1))))
  • A limit strategy is now used in the dataset_associations plugin if the association has a limit and the database supports window functions. This makes the resulting datasets return correct results.

    # SELECT *
    # FROM albums
    # WHERE ((albums.artist_id IN (
    #   SELECT artists.id FROM artists)
    # ) AND (albums.id IN (
    #   SELECT id FROM (
    #     SELECT albums.id, row_number() OVER
    #       (PARTITION BY albums.artist_id ORDER BY release_date)
    #       AS x_sequel_row_number_x
    #     FROM albums
    #   ) AS t1
    #   WHERE (x_sequel_row_number_x <= 10)
    # )))
    # ORDER BY release_date
  • You can now pass symbols with embedded qualifiers or aliases, as well as SQL::Identifier, SQL::QualifiedIdentifier, and SQL::AliasedExpression objects as the first argument to Dataset#graph.

  • The nested_attributes plugin now automatically handles presence validations on foreign keys when creating associated objects. It now sets the foreign key value (or a placeholder value) before validating such objects.

  • Offsets on *_one associations are now respected when using eager_graph.

  • eager graphing *_many associations with offsets no longer breaks if there are no associated results.

  • Database#register_array_type in the pg_array extension now works correctly if there is no existing scalar conversion proc for the type.

  • Unique, foreign key, and not null constraint violations are now recognized correctly on SQLite 3.8.2+.

  • The odbc adapter now returns fractional seconds in timestamps.

  • The obdc/mssql adapter now inputs timestamps with 3 decimal places.

Backwards Compatibility

  • The private Model.apply_window_function_eager_limit_strategy method has been removed.