Knex中MySQL左外连接未返回正确数据，求排查解决

1. Fixing the Left Outer Join Null Result Problem #

Looking at the SQL query you provided, I immediately spotted the root cause of your issue: the condition metadata.deleted_at = null in your JOIN clause.

In SQL, NULL represents an unknown value, so you can't use the equality operator (=) to check for it. Instead, you need to use IS NULL to properly match rows where deleted_at is null. That's why your join isn't picking up the matching metadata record (metadata_id=1) — the condition is never evaluating to true.

Corrected SQL Query #

SELECT 
  `notifications`.`notification_id`, 
  `notifications`.`message`, 
  `notifications`.`mode`, 
  `metadata`.`metadata_id`, 
  `metadata`.`unit_conversion` 
from `notifications` 
LEFT OUTER JOIN `metadata` 
  ON (
    `metadata`.`device_id` = `notifications`.`device_id` 
    AND `metadata`.`channel` = `notifications`.`channel` 
    AND `metadata`.`deleted_at` IS NULL  -- Fixed here!
  ) 
WHERE `notifications`.`notification_id` = 1 

Corresponding Knex Syntax #

Here's how to write this corrected join in Knex:

knex('notifications')
  .leftOuterJoin('metadata', function() {
    this.on('metadata.device_id', '=', 'notifications.device_id')
        .andOn('metadata.channel', '=', 'notifications.channel')
        .andOn('metadata.deleted_at', 'is', null);  // Using 'is' instead of '='
  })
  .select([
    'notifications.notification_id',
    'notifications.message',
    'notifications.mode',
    'metadata.metadata_id',
    'metadata.unit_conversion'
  ])
  .where('notifications.notification_id', 1)
  .then(results => console.log(results))
  .catch(err => console.error(err));

This should now correctly return the matching metadata_id and unit_conversion values instead of null.

2. Handling Nested Query Conversion in Knex #

Since you mentioned having issues converting nested queries to Knex syntax, let's walk through a common example to illustrate how it works. Let's say you have a nested subquery that filters notifications before joining, like:

Example Raw SQL with Nested Query #

SELECT n.notification_id, n.message, m.metadata_id
FROM (
  SELECT * FROM notifications WHERE mode = 'email'
) n
LEFT JOIN metadata m ON n.device_id = m.device_id AND n.channel = m.channel

Equivalent Knex Code #

You can use Knex's subquery chaining or knex.raw() for flexibility:

// Option 1: Chain subqueries directly
const filteredNotifications = knex('notifications')
  .select('*')
  .where('mode', 'email');

knex(filteredNotifications.as('n'))
  .leftJoin('metadata as m', function() {
    this.on('n.device_id', '=', 'm.device_id')
        .andOn('n.channel', '=', 'm.channel');
  })
  .select('n.notification_id', 'n.message', 'm.metadata_id')
  .then(results => console.log(results));

// Option 2: Use knex.raw() for complex nested logic
knex.raw(`
  SELECT n.notification_id, n.message, m.metadata_id
  FROM (
    SELECT * FROM notifications WHERE mode = ?
  ) n
  LEFT JOIN metadata m ON n.device_id = m.device_id AND n.channel = m.channel
`, ['email'])
.then(results => console.log(results.rows));

The key is to use as() to alias your subquery, and Knex will generate the correct nested SQL structure. If you have a specific nested query in mind, feel free to share it and I can help tailor the solution!

内容的提问来源于stack exchange，提问作者jlyh