CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
Olly Bradshaw
Olly Bradshaw

Reputation: 83

Rails - Checking if an object has a property which starts with a specific character using .where

I have a database with users. They all have postcodes.

I am trying to use .where to grab all users which are within 100 days old and which have a postcode which begins with "NR".

I tried the below code but I get postgres syntax errors, probably because .where seems to accept database-specific syntax instead of straight-up ruby.

users = User.where('created_at < ? and postcode[0..1] = ?', 100.days.ago, "NR")

I cannot seem to find anything which would help me out online. I even tried to create an array and iteratively save through records which matched, but then rails couldn't assign the Users to an array either, so I canned it.

Could anybody here give me any guidance?

Upvotes: 1

Views: 184

Answers (3)

chad_
chad_

Reputation: 3819

Try:

users = User.where { |u| u.postcode.matches "NR%" && u.created_at < 100.days.ago }

Not sure where that comes from, so try (more lines though):

user_tbl = User.arel_table
u = User.where(user_tbl[:postcode].matches("NR%"))
        .where(user_tbl[:created_at].lt(100.days.ago))

Upvotes: 2

tadman
tadman

Reputation: 211610

When programming you always need to maintain an awareness of what context you're writing code for. Often you'll be working with HTML, JavaScript, Ruby and SQL simultaneously, each of which has their own syntax and conventions.

In this case you're working with Postgres SQL, so what you need is a Postgres function to do the work for you. Here's a literal translation:

User.where('created_at < ? and LEFT(postcode, 2) = ?', 100.days.ago, "NR")

Be sure to familiarize yourself with the Postgres string functions as there are many of them.

As R_O_R points out in another answer, using LIKE is an option here.

Upvotes: 1

Arup Rakshit
Arup Rakshit

Reputation: 118271

Write as :

users = User.where('created_at < ? AND postcode LIKE ?', 100.days.ago, "NR%")

Read PostgreSQL LIKE operator.

Upvotes: 1

Related Questions