Checking sqlite datetime NULL with RoR

Question

Hi guys, RoR/SQL newbie here.

My datetime column 'deleted_at' are all uninitialized. Running this query returns an error:

SELECT * FROM variants v
 ON v.id = ovv0.variant_id INNER JOIN option_values_variants ovv1
 ON v.id = ovv1.variant_id INNER JOIN option_values_variants ovv2
 ON v.id = ovv2.variant_id INNER JOIN option_values_variants ovv3
 ON v.id = ovv3.variant_id INNER JOIN option_values_variants ovv4
 ON v.id = ovv4.variant_id
 WHERE v.deleted_at = NULL
 AND v.product_id = 1060500595

However, if I set my datetime values to 0, and set my query to v.deleted_at = 0, the correct variant is returned to me.

How do I check for uninitialized/NULL datetimes?

Answer 1

Your main error I believe is that ovv0 is not defined. State your inner join, then your clause.

SELECT * FROM variants v 
INNER JOIN option_values_variants ovv0 ON v.id = ovv0.variant_id
INNER JOIN option_values_variants ovv1 ON v.id = ovv1.variant_id
INNER JOIN option_values_variants ovv2 ON v.id = ovv2.variant_id
INNER JOIN option_values_variants ovv3 ON v.id = ovv3.variant_id
INNER JOIN option_values_variants ovv4 ON v.id = ovv4.variant_id
WHERE v.deleted_at = NULL AND v.product_id = 106050059;

I'd also like us to explore why you are using so many inner joins on the same table. What are you trying to accomplish?

EDIT:

For nulls, SQLite is tricky, and considers null to be a type. There is also no true datetime type either. You can verify the column type with the typeof function, which returns a string.

SELECT * FROM variants v 
...
WHERE typeof(v.deleted_at) = 'null' AND v.product_id = 106050059;

REEDIT:

There's also ifnull. That function replaces a null with whatever value you would like. So in your case, with the single command you can check natural 0's and nulls in one shot. If you'd rather not have natural 0's and just the nulls, use some other value that should not be a date (say -1) and evaluate against it.

SELECT * FROM variants v 
...
WHERE ifnull(v.deleted_at,0) = 0 AND v.product_id = 106050059;