Find() with Nil When There Are No Records

find() with nil when there are no records

Yes, just do:

Challenge.find_by_id(10)

For Rails 4 and 5:

Challenge.find_by(id: 10)

Rails find returns nil even if there are records to be returned

The problem was encoding and when I added

value.encode('utf-8')

to each field for the CSV file I imported I got proper values into the database.

The encoding problem seems to affect the queries differently.

Active Record `find` returns nil when a valid record exists

So I found the answer. The problem was with the link in my test: expect(Article).to receive(:find).with(article.id.to_s). Without stating what I want Article.find(params[:id) to return, it returns nil. Hence no error message was received and `find_by(id: params[:id]) worked.

Find all records that have a non-nil field?

Rails3:

@nonorders = Customers.where("customers.date IS NOT NULL")

Rails2:

@nonorders = Customers.find(:all, :conditions => "customers.date IS NOT NULL")

Rails 3 - How to Not to Error if no record is found

@permission = Permission.find_by_id params[:user_id]

The idea is that if you are using the "first param is the id" version of find, you know exactly what you are looking for, and if it isn't there, thats a problem. If you use one of the more generic finder syntaxes (like find_by_field_name), the assumption is that if it isn't there, that is an acceptable situation so just return nil.

How can I make a SQL query that returns null if there is no record or return the value if there is?

I think I see what you're trying to do. The key is using joins (specifically OUTER joins) instead of trying to mash all the tables together and then find similarities. There are also LEFT, RIGHT and INNER flavors (read more about these here and here), depending on what you consider the "complete" or "master" data set - the starting point of your query.

Here's how I understand your relationships (let me know if I have this wrong):

record.variable --> variable.pk
variable.area --> area.pk
area.role --> user.role

In your case, you stated that you need all records from the variable table, so I would start with this:

SELECT v.*
FROM variable v;

Then, you might find all the AREA records related to a particular USER. Use an INNER join to find only records that exist on BOTH sides of the join:

SELECT a.*, u.*
FROM area a
INNER JOIN user u         -- Define the table to join
   ON a.role = u.role     -- Which columns contain keys to match on
WHERE u.userName = 'Leo';

The WHERE filter applies to the user table, but because we are ONLY asking for records from the area table that have a match with user, then that limits the results from the area table.

The next step is to join these two extracts together using another INNER join, again, to find the intersection - matches that exist on BOTH sides of the join(s):

SELECT v.*, a.*, u.*
FROM variable v           -- New starting point
INNER JOIN area a
   ON a.pk = v.area
INNER JOIN user u
   ON a.role = u.role
WHERE u.userName = 'Leo';

Now, we find all the records for a certain day by adding WHERE clauses:

SELECT v.*, a.*, u.*
FROM variable v
INNER JOIN area a
   ON a.pk = v.area
INNER JOIN user u
   ON a.role = u.role
WHERE u.userName = 'Leo'
AND v.round = 1           -- Add filters for "round"
AND v.day = '11-14-2018'; -- and "day" columns

Next, we use a LEFT join to give us all the records from the table on the "left" plus any matches we find on the "right" side (the "record" table) or NULL if no match is made:

SELECT v.name
   ,a.name as "areaName"
   ,CAST(r.value as TEXT) as "value"
   ,r.alarmed
   ,r.alarmType
FROM variable v
INNER JOIN area a
   ON v.area = a.pk
INNER JOIN user u
   ON a.role = u.role
LEFT JOIN record r        -- LEFT is important here
   ON v.pk = r.variable
WHERE u.userName = 'Leo'
AND v.round = 1
AND v.day = '11-14-2018'
ORDER BY v.area, v.position;

The result from INNER joins (variable + area + user) becomes the "left" side of this join, and the record becomes the "right" side. Using the LEFT join declares that we want ALL records from the left, whether they have a match on the right or not.

I don't have a dataset to test this with, so please excuse any errors I've made.

Hopefully, this illustrates how joins would be used to both eliminate rows and add data (columns) the result, without having to make individual queries or resort to sub-queries (using IN or EXISTS).

---

Related Topics