Subquery

[stts-x]

Hi again. how to do subquery like this select from (select from....) left join (select * .....)

[PhilWaldmann]

For complex queries it's better to use raw sql like this:

client.connection.raw(sql, ['attr1', 'attr2'])
.then(..)

[stts-x]

I do not understand, can you explain in more detail? Or explain how get sql query string from store.Model('someModel').where(...)....., may be some toString method ? it's useful for debug

[PhilWaldmann]

To get the SQL string from OpenRecord take a look at the tests: https://github.com/PhilWaldmann/openrecord/blob/ddfc912d956211dbff5c4fc0032de3c80f7cb8d6/test/sql/__shared/joins-test.js#L81 (.toSql)

[PhilWaldmann]

I've just added the option to create raw joins via join('string with raw SQL') or .join(['string with raw sql and params ?', ['arg0', ...])

e.g.:

Model.join('JOIN (SELECT id FROM foo) as f ON f.id = ANY(model.reference_ids)').toSql(function(sql){
  sql.should.be.equal('select * from "users" JOIN posts ON posts.id = users.id');
  next();
})

Doing a SELECT FROM (SELECT...) will never be possible with OpenRecord, because querying will always start with a specific model. If you need such complex queries either use the client.connection.raw('your raw sql') or create a viewin your database.

Please let me know if you need further assistance.