MYSQL User Defined Variable Issue

Question

I am trying to get a count of the number of reviews between the last time a user logged in and now. I am using knex, but trying to get this to work in one statement without SET at the top in MYSQL first.

This is what I am looking for but it is not returning the correct COUNT. The statement doesn't seem to be using the @lastlogin variable in the WHERE BETWEEN statement but I can't figure out why.

SELECT Count(*), @lastlogin := (SELECT DATE(userLastLogin) FROM users WHERE users.UID = 50) AS login
FROM reviews
RIGHT JOIN schools on schools.SID = reviews.schoolID
WHERE reviews.dateCreated BETWEEN @lastlogin AND NOW()
AND reviews.active = 1;

If I use this it works.

SET @lastlogin = (SELECT DATE(userLastLogin) FROM users WHERE users.UID = 50);
SELECT Count(*)
FROM reviews
RIGHT JOIN schools on schools.SID = reviews.schoolID
WHERE reviews.dateCreated BETWEEN @lastlogin AND NOW()
AND reviews.active = 1

If I write a date explicitly in the BETWEEN it works.

Why doesn't it work with defining the variable in the SELECT?

If I was just using MYSQL it would be fine, but I need to have the variable set in the SELECT for Knex.js.

Answer 1

Why do you need a user variable? Maybe knex.js doesn't support either of these though...

SELECT Count(*)
FROM reviews
RIGHT JOIN schools on schools.SID = reviews.schoolID
WHERE reviews.dateCreated BETWEEN (SELECT DATE(userLastLogin) 
                                   FROM users 
                                   WHERE users.UID = 50) AND NOW()
  AND reviews.active = 1;

Or if you do need the user variable for some reason... Maybe..

SELECT Count(*), @LastLogin
FROM reviews
RIGHT JOIN schools on schools.SID = reviews.schoolID
CROSS JOIN (SELECT @LastLogin:=(SELECT DATE(userLastLogin) 
                                   FROM users 
                                   WHERE users.UID = 50))
WHERE reviews.dateCreated BETWEEN @LastLogin  AND NOW()
  AND reviews.active = 1;

To answer your question though is two fold

1. A user variable has to be initialized before it can be used. This could be accomplished in a cross join (Select @LastLogin:=Now())
2. Order of operations of SQL isn't top down. So in your case the where clause is executed before the select so the user variable hasn't been initialized yet. SQL order of operation in this case is FROM, Right Join, Where, Select... Select is the last operation being performed. So @LastLogin isn't in scope when the where clause is executed.