synatic / noql Goto Github PK

select count(*) as theCount from collection

should add a last pipeline stage

{
$count: "theCount"
}

But need to double check when grouping by different fields

https://docs.mongodb.com/manual/reference/operator/aggregation/count/

select cast(a as Int) as s from collection

SELECT *,convert(id,'int') as idConv FROM customers

Only supported with aggregation, needs to be tested for

Please add support for CURRENT_DATE().

I currently get Error: Function:CURRENT_DATE not available.

My query:

SELECT COUNT(*) as count, MONTH(data.returnDate) as month 
FROM package 
WHERE YEAR(data.returnDate) = YEAR(CURRENT_DATE()) 
GROUP BY MONTH(data.returnDate)

It would be cool to see this library work for basic create-update-delete operations. No need to support anything crazy like subqueries, etc.

I'd be willing to take a stab at the PR for it if this isn't an easy feature to implement 😄

Example:

      select a.ID as aId,
             b.name as bName
      from   [something.tablea] a
      join   [something.tableb] b on a.ID = b.ID and b.IsDeleted = 'false'

This query (and ones more complicated, where a conditional in a join makes sense) will fail. This will throw the error in makeFilterCondition.js: invalid expression type for array sub select:bool, as it identifies the conditional in the join as a subselect. Simply adding 'bool' to the array on line 169 in the same file fixes this issue.

select (log10(3) * floor(a) + 1) as s from collection

Is there a way to achieve a NOT LIKE statement? Thanks!

const SQLParser = require('@synatic/noql');

Promise.resolve()
.then(async function () {

	const query = `select A.name, B.name, C.name
from \`user-books\` as UB, books as B, users as A, authors as C
where UB.book = B._id AND UB.user = A._id AND B.author = C._id
`;

	const parsedSQL = SQLParser.parseSQL(query);

	console.log(parsedSQL);

})
.catch(err => console.error(err));

Throws an error

TypeError: Cannot read properties of undefined (reading 'left')
    at shouldPrefixSide ($project\node_modules\@synatic\noql\lib\make\makeJoinForPipeline.js:236
:30)
    at makeJoinPart ($project\node_modules\@synatic\noql\lib\make\makeJoinForPipeline.js:104:24)
    at Object.makeJoinForPipeline ($project\node_modules\@synatic\noql\lib\make\makeJoinForPipel
ine.js:48:9)
    at Object.makeAggregatePipeline ($project\node_modules\@synatic\noql\lib\make\makeAggregateP
ipeline.js:234:52)
    at makeMongoAggregate ($project\node_modules\@synatic\noql\lib\make\index.js:26:47)
    at SQLParser.parseSQL ($project\node_modules\@synatic\noql\lib\SQLParser.js:69:16)
    at $project\index.js:11:30

select * from (select id,First Name,Last Name,arrayLength(Rentals,'id') as rentalCount from customers) as t

node_modules/@synatic/noql/node_modules/bson/lib/bson.mjs:142:28:
Unexpected reserved word 'await'. (142:28)

This error appears in my meteor app that uses node version 14.21.3

select id,First Name,Last Name,sumArray(Rentals,saleId) as totalIdRentals from customers

 select * from bob

returns:

{
    "limit": 100,
    "collection": "bob",
    "type": "query"
}

as expected.

However,

 select * from bob;

throws an error:

No FROM specified