使用SQL在Mobile Apps Node.js后端中实现表联接 [英] Implementing table joins in the Mobile Apps Node.js backend using SQL

问题描述

由于Azure移动应用程序不提供在表之间创建关系的方法，因此我决定在Node.js后端中创建自定义API来从相关表中返回数据.想法是使用SQL在后端中实现联接，如

Since Azure Mobile Apps does not provide a way to create relationships between tables I decided to create a custom API in my Node.js backend to return data from related tables. The idea was to implement the joins in the backend using SQL, like explained at the Mobile Services Doc.

问题是我使用的是新的Mobile Apps，而不是旧的Mobile Services，因此上面的代码不再起作用.据我了解，架构从移动服务更改为移动应用程序，而Node.js SDK是一个快速的中间件软件包.因此，现在我们利用 azure-mobile-apps/src/data 模块来处理sql操作.

The problem is that I'm using the new Mobile Apps and not the old Mobile Services, so the above code does not work anymore. As I understand the architecture changed from Mobile Service to Mobile Apps and the Node.js SDK is an express middleware package. So now we leverage azure-mobile-apps/src/data module to handle sql operations.

因此，我现在必须执行以下操作以从Node后端内部的Custom API中的表中读取内容:

So I now I have to do something like this to read from a table in a Custom API inside the Node backend:

var queries = require('azure-mobile-apps/src/query');

module.exports = {

    "get": function (req, res, next) {

        var myTable = req.azureMobile.tables('TableName');
        var query =  queries.create('TableName');
        query.where({'id':req.query.userId});

        myTable.read(query).then(function(data){
            res.send({ some:  data });
        });
    }
};

但是由于不再公开SQL，所以我无法使用JOIN命令从相关表中返回数据.我将不得不使用循环和对数据库的许多请求，这违背了目的.

But since the SQL is not exposed anymore I cannot use the JOIN command to return data from related tables. I would have to to use loops and many request to the database, which defeats the purpose.

-是否有一种方法可以在新的Mobile Apps Node.js SDK上使用SQL在后端中实现联接?还是其他更好的方法?

-Is there a way to implement the joins in the backend using SQL on the new Mobile Apps Node.js SDK? Or any other better way?

非常感谢！

推荐答案

您可以生成join sql stmt并利用sqlQuery对象(可以包含sql stmt)，然后使用 https://github.com/Azure/azure-mobile-apps-node/blob/master/samples/custom-api-sql-stmt/api/completeall.js 适用于类似情况.

You can generate the join sql stmt and leverage a sqlQuery object with can contain sql stmt, then use data.execute function to directlu execute the sql stmt. You can refer to the sample https://github.com/Azure/azure-mobile-apps-node/blob/master/samples/custom-api-sql-stmt/api/completeall.js for the similar scenario.

与此同时，您可以在Azure SQL数据库中创建一个包含联接stmt的View表，以便在Easy API脚本中轻松使用并维护View SQL stmt.

Meanwhile, you can create a View table in Azure SQL database which contain the join stmt, for easy using in Easy APIs scripts and maintaining the View SQL stmt.

这篇关于使用SQL在Mobile Apps Node.js后端中实现表联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！