0

I have query that gets data from multiple table. I am using pg-promise with node js. My old project database was MySql and I used node [mysql][1] whick allowed me to get nested result from query like this:

var nestingOptions = [
            { tableName : 'chat', key: 'chatID' },
            { tableName : 'chat_contacts', key: 'userID', hasForeignKeyToUpperTable: true },
            { tableName : 'users', key: 'userID', hasForeignKeyToUpperTable: false }
        ]

        connection.query({ sql: queryString, nestTables: true }, function (err, rows, fields) {

            var response = chatResponseFromQuery(err, rows, fields, nestingOptions);
            return(response.status, response);
        });

Query result was nested in tables like this:

chat [row data, chat_contacts[row_data]]

chatResponseFromQuery function is using my lib to decide if chat._id appears again in result, based on relation given in nestingOptions, so it can map users inside chat_contacts array and chat_contacts array inside chat array with same chat._id.

query example:

SELECT
chat._id chatID,
users._id userID,
users.uuid,
users.company_id,
users.role_id,
users.username,
users.name,
users.lastname,
users.email, 
users.phone,
users.push_token,
users.avatar_url,
users.cover_url,
users.device_type,
users.status,
users.last_seen,
users.created_at,
users.updated_at,
chat_contacts.contact_id userID,
chat_contacts.chat_id chatID
FROM company_one.chat
LEFT JOIN company_one.chat_contacts ON chat._id = chat_contacts.chat_id
LEFT JOIN company_one.users ON chat_contacts.contact_id = users._id
WHERE chat_contacts.chat_id IN(3,19)

So basically i need only nested tables so i can reuse my lib again to get properly nested json data (in other queries i have more tables, this is example with only one table).

sime
  • 7
  • 3

0 Answers0