Recursive query.

[stigmat4j]

I use sequelize-postgres npm for my nodejs project. The structure of my db

id: Sequelize.INTEGER,
sponsor: Sequelize.INTEGER,
depth: Sequelize.INTEGER,
path: Sequelize.TEXT,
parents: Sequelize.INTEGER,
cycle: Sequelize.INTEGER
Query for db

WITH RECURSIVE
nodes_cte(id, sponsor, depth, path, parents, cycle) AS (
SELECT tn.id, tn.sponsor,
1 AS depth,
'tn.id' AS path,
ARRAY[tn.id],
false
FROM referrals tn
WHERE tn.sponsor = 0
UNION ALL
SELECT c.id, c.sponsor,
p.depth + 1 AS depth,
(p.path || '->' || c.id),
p.parents || p.id,
c.id = ANY(p.parents)
FROM nodes_cte AS p,
referrals AS c
WHERE c.sponsor = p.id
AND NOT p.cycle
)
SELECT * FROM nodes_cte AS n;

If i make the query through Sequelize - i receive empty array. But when i do it through console i receive normal result.

Help me to solve my problem please.

[mickhansen]

http://sequelizejs.com/docs/latest/usage#raw-queries
Are you using the raw parameter?

[stigmat4j]

Yes, i using next code
sequelize.query(query, Referrals, {raw: true}).success(function (referrals) {
console.info(referrals)
})

and return is [], for direct query to db with console normal result returns.

[mickhansen]

You cant use 2nd parameter callee with raw afaik, so it sohuld be query(sql, null, {raw: true})

[mickhansen]

Hmm, maybe the issue is that it doesn't identify your query as a SELECT query (we match on the first keyword i believe)

[mickhansen]

sequelize.query(
  query,
  Referrals,
  {raw: true, type: Sequelize.QueryTypes.SELECT}
).success(function (referrals) {
  // ...
})

should work, perhaps with null instead of Referrals. Else please re-open