Postgresql syntax for select a record (row) with two of three fields

Question

I have a table QualifyingEvents. I am attempting to search the table for records created on a particular date with their course_id and / or their sponsor_name. I'm following the examples in RailsGuides Active Record Query Interface: http://guides.rubyonrails.org/active_record_querying.html

I couldn't get a query of this form to work:

QualifyingEvent.find([:date = 'x', :course_id = 'x', :sponsor_name ='x'])

Nor this form:

QualifyingEvent.where(":course_id = ?", x )

However, this works - with the commas being interpreted as "and":

QualifyingEvent.find_by date: '2014', course_id: '96789', sponsor_name: 'Maggio and Sons'

Now, how do I get a record if I want a match on two of the three fields? I've tried using && and || with no luck. What is the correct syntax? Also, why does "find_by" work when "find" doesn't?

Thank you, in advance, for your help.

Note that `":course_id = ?"` and `"course_id = ?"` are different things. The leading colon is *Ruby* syntax, not *SQL* syntax. — mu is too short, Dec 23 '14 at 20:32
Learning Rails before Ruby is fine. But Ruby should immediately follow Rails then, before practicing on your own, in order to avoid confusing situations like this one and write more efficient code. — D-side, Dec 23 '14 at 23:19

score 1 · Accepted Answer · edited May 23 '17 at 10:26

To match just two fields, one way is:

QualifyingEvent.where(field1: field1_val, field2: field2_val).first

OR

QualifyingEvent.find_by(field1: field1_val, field2: field2_val)

That hash provided as an argument to where and find_by can be passed in different forms e.g.

"field1 = ? AND field2 = ?", field1_val, field2_val

OR

"field1 = :f1 AND field2 = :f2", {f1: field1_val, f2: field2_val}

OR

"field1 = #{field1_val} AND field2 = #{field2_val}"

That last one isn't advised though mainly because of the risk of SQL injection.

But if you meant matching any two of the three field values you gave, then you'll have to provide all three match-scenarios yourself and specify the "OR" between them.

QualifyingEvent.where("
    (date = :date AND course_id = :course_id)
    OR
    (date = :date AND sponsor_name = :sponsor_name)
    OR
    (course_id = :course_id AND sponsor_name = :sponsor_name)
  ",
  {date: date_val, course_id: course_val, sponsor_name: sponsor_val}
).first

Again, the above code can be written in different ways.

And find_by is for certain conditions that you can pass as a hash whereas find is for finding by ID or array of IDs. Shortcut for find_by(id: some_value) i.e.

find_by(id: 1) == find(1)

score 0 · Answer 2 · answered Dec 23 '14 at 20:04

Try something like this:

QualifyingEvent.where("course_id = ? and some_other_col = ? and some_more_col = ?", x,y,z)

Or

QualifyingEvent.where(:course_id => 'x', :some_other_col => 'y', :some_more_col => 'z')

Any one of them will fetch you the result.

Postgresql syntax for select a record (row) with two of three fields

2 Answers2