getto-repository-sequel

Repository helper for Sequel

require "getto/repository/sequel"

class Repository < Getto::Repository::Sequel
  def account_exists?(id)
    not db[:accounts].where(id: id).empty?
  end
end

require "sequel"

db = Sequep.connect config

repository = Repository.new(db)

repository.transaction do
  repository.account_exists?
end

misc: search helper

require "getto/repository/sequel/search"


search = Getto::Repository::Search::Sequel.new(
  limit: 1000,
  sort:  {
    column: :active,
    order:  true,
  },
  query: {
    "id.eq":     "1",

    "name.cont": "cont",

    "name.cont_as_kana": "かな",
    "name.cont_as_hira": "カナ",

    "name.cont_any": "カナ",

    "active.in": ["True"],

    "date.gteq": "2018-10-01",
    "date.lteq": "2018-10-31",
  },
)

where = search.where do |w|
  w.search "id.eq", &w.eq(::Sequel[:accounts][:id])

  w.search "name.cont", &w.cont(::Sequel[:accounts][:name])

  w.search "name.cont_as_kana", &w.cont_as_kana(::Sequel[:accounts][:name])
  w.search "name.cont_as_hira", &w.cont_as_hira(::Sequel[:accounts][:name])

  w.search "name.cont_any", &w.or([
    w.cont_hira_or_kana(::Sequel[:accounts][:name]),
    w.cont_hira_or_kana(::Sequel[:accounts][:kana]),
  ])

  w.search "active.in", &w.in(
    &w.is_not_null(
      ::Sequel[:account_actives][:account_id],
      "True"  => true,
      "False" => false,
    )
  )

  w.search "date.gteq", &w.gteq(::Sequel[:accounts][:created_at])
  w.search "date.lteq", &w.lteq(::Sequel[:accounts][:created_at])
end

order = search.order do |o|
  o.order :active, o.is_not_null(::Sequel[:account_actives][:account_id], 0, 1)

  o.order :id, ::Sequel[:accounts][:id]

  o.force ::Sequel[:accounts][:id]
end

pages = search.pages(
  db[:accounts]
    .left_join(:account_actives, account_id: :id)
    .where(where)
    .count
)
# => current page: e.g. count = 120, limit = 100 => pages = 2

db[:accounts]
  .left_join(:account_actives, account_id: :id)
  .where(where)
  .order(*order)
  .select(
    ::Sequel[:accounts][:id],
    ::Sequel[:account_actives][:account_id].as(:active),
    ::Sequel[:accounts][:name],
    ::Sequel[:accounts][:kana],
  )
  .all

It generates sql like below

SELECT
  `accounts`.`id`,
  `account_actives`.`account_id` as `active`,
  `accounts`.`name`,
  `accounts`.`kana`
FROM
  `accounts`
LEFT JOIN
  `account_actives`
    ON `account_actives`.`account_id` = `accounts`.`id`
WHERE
  (`accounts`.`id` = 1) AND
  (`accounts`.`name` LIKE '%cont%') AND
  (`accounts`.`name` LIKE '%かな%') AND
  (`accounts`.`name` LIKE '%カナ%') AND
  (
    (
      (`accounts`.`name` LIKE '%カナ%') OR
      (`accounts`.`name` LIKE '%かな%')
    ) OR
    (
      (`accounts`.`kana` LIKE '%カナ%') OR
      (`accounts`.`kana` LIKE '%かな%')
    )
  ) AND
  (`account_actives`.`account_id` IS NOT NULL)
ORDER BY
  if(`account_actives`.`account_id`, 0, 1) ASC,
  `accounts`.`id` ASC

Requirements
Usage
License

Requirements

developed on ruby: 2.5.1
Sequel

Usage

where clause

equals

where = search.where do |w|
  w.search "id.eq", &w.eq(::Sequel[:accounts][:id])
end

-- { "id.eq": "1" }
WHERE
  (`accounts`.`id` = 1)

less than or equals to

where = search.where do |w|
  w.search "date.lteq", &w.lteq(::Sequel[:accounts][:created_at])
end

-- { "date.lteq": "2018-10-01" }
WHERE
  (`accounts`.`created_at` <= "2018-10-01")

greater than or equals to

where = search.where do |w|
  w.search "date.gteq", &w.gteq(::Sequel[:accounts][:created_at])
end

-- { "date.gteq": "2018-10-01" }
WHERE
  (`accounts`.`created_at` >= "2018-10-01")

contains

where = search.where do |w|
  w.search "name.cont", &w.cont(::Sequel[:accounts][:name])
end

-- { "name.cont": "cont" }
WHERE
  (`accounts`.`name` LIKE '%cont%')

contains as kana

where = search.where do |w|
  w.search "name.cont", &w.cont_as_kana(::Sequel[:accounts][:name])
end

-- { "name.cont": "かな" }
WHERE
  (`accounts`.`name` LIKE '%カナ%') -- convert 'かな' to 'カナ'

contains as hira

where = search.where do |w|
  w.search "name.cont", &w.cont_as_hira(::Sequel[:accounts][:name])
end

-- { "name.cont": "カナ" }
WHERE
  (`accounts`.`name` LIKE '%かな%') -- convert 'カナ' to 'かな'

contains as hira or kana

where = search.where do |w|
  w.search "name.cont", &w.cont_hira_or_kana(::Sequel[:accounts][:name])
end

-- { "name.cont": "カナ" }
WHERE
  (
    (`accounts`.`name` LIKE '%カナ%') OR
    (`accounts`.`name` LIKE '%かな%')
  )

-- { "name.cont": "かな" }
WHERE
  (
    (`accounts`.`name` LIKE '%カナ%') OR
    (`accounts`.`name` LIKE '%かな%')
  )

is not null

where = search.where do |w|
  w.search "active.is", &w.is_not_null(
    ::Sequel[:account_actives][:account_id],
    "True"  => true,
    "False" => false,
  )
end

-- { "active.is": "True" }
WHERE
  (`account_actives`.`account_id` IS NOT NULL)

-- { "active.is": "False" }
WHERE
  (`account_actives`.`account_id` IS NULL)

w.search "name.cont_any", &w.or([
  w.cont(::Sequel[:accounts][:name]),
  w.cont(::Sequel[:accounts][:kana]),
])

-- { "name.cont": "カナ" }
WHERE
  (
    (`accounts`.`name` LIKE '%カナ%') OR
    (`accounts`.`kana` LIKE '%カナ%')
  )

w.search "active.in", &w.in(
  &w.is_not_null(
    ::Sequel[:account_actives][:account_id],
    "True"  => true,
    "False" => false,
  )
)

-- { "active.in": ["True"] }
WHERE
  (
    (`account_actives`.`account_id` IS NOT NULL)
  )

-- { "active.in": ["True","False"] }
WHERE
  (
    (`account_actives`.`account_id` IS NOT NULL) OR
    (`account_actives`.`account_id` IS NULL)
  )

create where-clause by block

w.search("active.is"){|val|
  if val == "True"
    ::Sequel.~( ::Sequel[:account_actives][:account_id] => nil )
  end
}

-- { "active.is": "True" }
WHERE
  (`account_actives`.`account_id` IS NOT NULL)

-- { "active.is": "False" }
WHERE
  (1 = 1) -- if block returns nil, create no where-clauses

Install

Add this line to your application's Gemfile:

gem 'getto-repository-sequel'

And then execute:

$ bundle

Or install it yourself as:

$ gem install getto-repository-sequel

License

getto/repository/sequel is licensed under the MIT license.

getto-repository-sequel

Development

Runtime

getto-repository-sequel

Table of Contents

Requirements

Usage

where clause

Install

License