Background
I have written a SQL query that works perfectly for my needs, and I am trying to add it to a queued job in Laravel. Instead of using Eloquent models I wanted to go with DB Query to ensure better performance as there will ultimately be many joins and conditions. I am having a problem with the where
clause when checking a date, but it works perfectly fine when running in SQL. I have stripped out additional pieces of this query to only include what is necessary for debugging this issue.
Code
The original SQL:
SELECT `messages`.`created_at`, `participants`.`last_read` FROM `messages` LEFT JOIN `participants` ON `messages`.`thread_id` = `participants`.`thread_id` AND `messages`.`user_id` != `participants`.`user_id` WHERE `messages`.`created_at` > `participants`.`last_read`;
When I run this directly in SQL, I see 2 results, which is expected with my current data.
created_at | last_read |
---|---|
2021-03-26 19:02:53 | 2021-03-23 19:31:30 |
2021-03-26 19:02:58 | 2021-03-23 19:31:30 |
This is how I have written it in Laravel:
$query = DB::table('messages') ->leftJoin('participants', function ($join) { $join->on('messages.thread_id', '=', 'participants.thread_id') ->on('messages.user_id', '!=', 'participants.user_id'); }) ->select('messages.created_at as message_date', 'participants.last_read as last_read') ->where('messages.created_at', '>', 'participants.last_read');
When I execute this, the results are empty. I dumped the final SQL from the DB Query builder to make sure it’s correct, and this is what it is:
select `messages`.`created_at` as `message_date`, `participants`.`last_read` as `last_read` from `messages` left join `participants` on `messages`.`thread_id` = `participants`.`thread_id` and `messages`.`user_id` != `participants`.`user_id` where `messages`.`created_at` > participants.last_read
And running that directly in SQL returns accurate results, as expected.
Context
For context, here is the data structure and some of the data I’m working with.
participants
id | thread_id | user_id | last_read | created_at | updated_at | deleted_at | last_notified |
---|---|---|---|---|---|---|---|
9 | 8 | 178 | 2021-03-23 23:31:53 | 2021-03-23 22:16:48 | 2021-03-23 23:31:53 | NULL | NULL |
messages
id | thread_id | user_id | body | created_at | updated_at | deleted_at |
---|---|---|---|---|---|---|
159 | 3 | 177 | adfad | 2021-03-26 19:02:53 | 2021-03-26 19:02:53 | NULL |
160 | 3 | 177 | dadddda | 2021-03-26 19:02:58 | 2021-03-26 19:02:58 | NULL |
The problem
It seems as though the DB query code is causing the columns with like names to be mixed up. Both tables have a column called created_at
, but I only need that column from the messages
table. My SELECT
only asks for that column, specifying the correct table. But something in this DB Query join is causing it to get mixed up.
Playing with different joins, and removing the where
clause, I realized that the dates aren’t correct always. For example, here is the result when I use leftJoin
{ "message_date": "2021-03-23 00:30:42", "last_read": "2021-03-26 00:22:48" }, { "message_date": "2021-03-23 00:31:25", "last_read": "2021-03-26 00:22:48" }
Notice, the message_date
and last_read
values are reverse of what they were when running the SQL directly. So this must be the problem.
I changed to rightJoin
, and the results are reversed:
{ "message_date": "2021-03-26 19:02:53", "last_read": "2021-03-23 19:31:30", }, { "message_date": "2021-03-26 19:02:58", "last_read": "2021-03-23 19:31:30", }
So that should work, right? I add the where
clause back in, but still the results are empty.
I am guessing there is something I need to do to tell the query builder to handle these columns correctly, as they seem to be getting mixed up during the where
and select
. But I can’t figure out how to clarify that. I have tried searching for others with this issue but I can’t seem to find anything relevant.
Already tried
I have already tried a few things with no change in results.
- Changing the order of the commands – like moving the
select()
to the beginning of the statement, things like this. - Using
whereDate
instead ofwhere
. (Note – for performance I’d rather avoid this, but wanted to try just in case). - Using
join
,joinLeft
, andjoinRight
. - Using
where
in theon
clause instead of twoon
s. Like this->leftJoin('participants', function ($join) { $join->on('messages.thread_id', '=', 'participants.thread_id') ->where('messages.user_id', '!=', 'participants.user_id'); })
Anyone have any guidance on things I can try? This should be such a simple task, and has turned into hours of trying to understand why it works in SQL and not Laravel’s DB Query Builder.
Advertisement
Answer
The where
function of the query builder will always assume the right hand side is a value and will use it in a prepared statement as a literal (in this case string). If you want to compare columns you need to use whereColumn
:
$query = DB::table('messages') ->leftJoin('participants', function ($join) { $join->on('messages.thread_id', '=', 'participants.thread_id') ->on('messages.user_id', '!=', 'participants.user_id'); }) ->select('messages.created_at as message_date', 'participants.last_read as last_read') ->whereColumn('messages.created_at', '>', 'participants.last_read')->get();
Additional where clauses can be found in the docs