What is `where` used for in ActiveRecord::ConnectionAdapters::IndexDefinition

rubyonrails-core
1

Hi! I’m trying to understand activerecord internals and wanted to know what is the where attribute defined in ActiveRecord::ConnectionAdapters::IndexDefinition used for?

Why do we need a where attribute for indexes?

For me the object looks like

 #<ActiveRecord::ConnectionAdapters::IndexDefinition:0x0000000111e406f8
  @columns=["user_id"],
  @comment=nil,
  @lengths={},
  @name="index_messages_on_user_id",
  @opclasses={},
  @orders={},
  @table="messages",
  @type=nil,
  @unique=false,
  @using=:btree,
  @where=nil>]

and where is nil, can’t figure out what it is used for.

Thanks !

2

This is used to represent the filter that many database engines allow you to use in order to create a “partial” index. Here’s a Postgres example, and the same works in Sqlite:

CREATE INDEX employee_phone_ix ON employees (phone)
  WHERE phone != '' AND phone IS NOT NULL;

Microsoft SQL Server is just about the same, and they call theirs a “filtered” index:

CREATE NONCLUSTERED INDEX employee_phone_ix ON employees (phone)
  WHERE phone != '' AND phone IS NOT NULL;
GO

Weirdly in Oracle and MySQL you can only create a filtered index based on a specific expression, for Oracle it’s something like:

CREATE INDEX employee_phone_ix ON employees (CASE WHEN phone = '' THEN NULL ELSE phone END);

and then your SELECT needs to have the CASE statement verbatim!

Here’s MySQL’s version, where you have to create a filter based on an expression, and place it in double parenthesis:

CREATE UNIQUE INDEX employee_phone_ix ON employees ((CASE WHEN phone = '' THEN NULL ELSE phone END));
1 Like