使用sails-postgresql执行WITH RECURSIVE递归查询遇问题求解决方案

Absolutely! You can run WITH RECURSIVE queries using sails-postgresql 0.11.4—your issue is almost certainly related to SQL syntax quirks, not a limitation of the connector. Let’s break this down and get it working properly.

First, Fix the SQL Syntax Error #

Your original query has a critical syntax mistake: you added a semicolon right after defining the recursive CTE. PostgreSQL doesn’t allow a semicolon between the WITH clause and the main SELECT statement—this is what triggered your initial error. Even when you removed the semicolon, using implicit comma-based joins might have caused subtle parsing issues.

Here’s the corrected, valid version of your query:

this.query({
  text: `WITH RECURSIVE recursetree(name, parent) AS (
    -- Base case: start with all initial records
    SELECT name, parent FROM data_indicators
    UNION ALL
    -- Recursive case: join back to the CTE to traverse the tree
    SELECT t.name, t.parent 
    FROM data_indicators t
    JOIN recursetree rt ON rt.name = t.parent
  )
  -- Main query: fetch all results from the recursive CTE
  SELECT * FROM recursetree`
}, (err, res) => {
  if (err) {
    console.error('Recursive query failed:', err);
    return;
  }
  console.log('Recursive tree results:', res.rows);
});

Why This Works with sails-postgresql #

sails-postgresql is just a lightweight wrapper around the official pg Node.js library. It passes your raw SQL directly to the PostgreSQL server—so any valid PostgreSQL feature (including recursive CTEs, which have been supported since PostgreSQL 9.1) will work as long as your database server supports it. The connector doesn’t block or modify advanced SQL syntax.

Optimal Implementation Tips #

To make your recursive queries cleaner, safer, and more efficient, follow these best practices:

• Use parameterized queries for dynamic filters: If you need to start recursion from a specific node (instead of the entire table), use parameter placeholders to avoid SQL injection and keep your code maintainable:

  const startingNode = 'root_indicator';
  this.query({
    text: `WITH RECURSIVE recursetree(name, parent) AS (
      SELECT name, parent FROM data_indicators WHERE name = $1
      UNION ALL
      SELECT t.name, t.parent 
      FROM data_indicators t
      JOIN recursetree rt ON rt.name = t.parent
    )
    SELECT * FROM recursetree`,
    values: [startingNode] // Pass parameters here
  }, (err, res) => { /* handle results */ });
  

• Switch to async/await for readability: If your project uses modern Node.js (v8+), replace callbacks with async/await to avoid "callback hell":

  async function fetchRecursiveTree() {
    try {
      const result = await this.query({
        text: `WITH RECURSIVE recursetree(name, parent) AS (
          SELECT name, parent FROM data_indicators
          UNION ALL
          SELECT t.name, t.parent 
          FROM data_indicators t
          JOIN recursetree rt ON rt.name = t.parent
        )
        SELECT * FROM recursetree`
      });
      return result.rows;
    } catch (err) {
      console.error('Failed to fetch recursive tree:', err);
      throw err;
    }
  }
  

• Optimize performance with indexes: If your data_indicators table is large, add an index on the parent column (and ensure name is indexed, ideally as the primary key) to speed up the recursive join:

  CREATE INDEX idx_data_indicators_parent ON data_indicators(parent);
  

Final Note #

Once you fix the SQL syntax, your recursive query should run smoothly with sails-postgresql. The connector fully supports PostgreSQL’s advanced features—you just need to make sure your SQL is valid.

内容的提问来源于stack exchange，提问作者Jaume J.