Simple Join Without manual SQL queries

[lastMove]

Hello, I need to make a simple Join. Example:

User 
{
"id":number,
"name":string
}
Comment
{
"id":number,
"user_Id":number,
"message":string
}

I need to make a little request like: "SELECT comment.message user.id FROM comment LEFT JOIN user". Actually I tried to do it with something like. The only way I find to make it is to itterate the first Request like:

req.db.models.comment.find({}).each(function(err, comment)
{
   comment.getUser...
});

and in the callback I use the accessor to add the needed data to an Array. But It's not very optimized. So My Question is How can i get the same result than:

req.db.driver.execQuery("SELECT comment.message, user.name FROM comment LEFT JOIN user ON comment.user_id = user.id");

Is there another way to do that? Thanks and regards, Sorry for my english, I am french.

[dxg]

Hi,

Unless I'm missing something, you should be able to achieve this using a hasOne association. Let me know if it works for you.

edit Actually it doesn't look like what you want. It will get all comments for one user, not a join of all users and all comments.

You may be able to build the query using db.driver.query (node-sql-query used by orm). See this test however I don't know if it's much better than writing raw SQL. It also doesn't seem to allow specifying LEFT.

I don't believe there is a way to do a join on two models directly.

[lastMove]

thank you very much. Since doing it like in the test isn't more generic, I will do it directly in SQL.