2

I am trying to figure how to create a "repository" file (for the symfony2 users) where i will put all my special behaviour queries.

I have a simple db shema with :

  • User (email)
  • RelationType (name)
  • UserXUserXRelation (fromUser, toUser, relation)

I want to retrieve the user with relation X on my user Y, so in sql it will looks like :

var sql = 'SELECT u.email, u.id
FROM user u
INNER JOIN UserXUser uxu ON uxu.toUser_id = u.id
WHERE uxu.relation_id = 1 AND uxu.fromUser_id = '+id

Where should i create this method ? I tried in the db/shema.js and app/models/user.js without success. I found here Using arbitrary mySQL Query with JugglingDB? that i should use the shema object to use "query", where and how could I use it ?

Does the callback will look like this :

function(err, data) {...}

Is there some best practice about code separation in that case ?

Additional question : is there a way to bind parameters the PHP PDO way with jugglingdb ?

Thanks for all.

Community
  • 1
  • 1
Jihel
  • 796
  • 1
  • 5
  • 13

1 Answers1

1

It is possible to execute any arbitrary query using jugglingdb and compoundjs. Providing you are using compoundjs, you can just use the compound.models.user.schema.adapter.query(). The compound object should be parsed into your user model, meaning you have access to many other methods. The way you would use this query in your model would be by creating a method inside the users model with the following code:

var sql = 'SELECT u.email, u.id
FROM user u
INNER JOIN UserXUser uxu ON uxu.toUser_id = u.id
WHERE uxu.relation_id = 1 AND uxu.fromUser_id = '+id
compound.models.user.schema.adapter.query(sql, function(err, data) {
    if(error) {
        console.log(error)
    } else {
        //Enjoy your data
    }
})

As this overrides the jugglingdb quote escaper, beware of sql injection, make sure your id variable is checked and sanitised.

Zachary
  • 36
  • 5