DQL Documentation

MartinB

Hello Dojo Community, where can I find more documentation on the DQL stuff?

I want to know how to tell the code to get all records which a given column string is like '%TEXT%'

 

Thanks in advance.

guitarhero23

Have you taken a look at this yet? https://developer.domo.com/docs/dataset-api-reference/dataset#Query%20a%20DataSet

 

You should be able to provide Domo a SQL statement to choose which records you get.

 

Example from that documentation:

POST https://api.domo.com/v1/datasets/query/execute/ce79d23f-ef7d-4318-9787-ebde54a8c5b4
Accept: application/json
Authorization: bearer <your-valid-oauth-access-token>

{"sql": "SELECT * FROM table"}

Based on that I'd expect something like below to work.

{"sql": "SELECT * FROM table WHERE column1 like '%Text%'"}

Or do you mean something else entirely?

guitarhero23

Have you taken a look at this yet? https://developer.domo.com/docs/dataset-api-reference/dataset#Query%20a%20DataSet

 

You should be able to provide Domo a SQL statement to choose which records you get.

 

Example from that documentation:

POST https://api.domo.com/v1/datasets/query/execute/ce79d23f-ef7d-4318-9787-ebde54a8c5b4
Accept: application/json
Authorization: bearer <your-valid-oauth-access-token>

{"sql": "SELECT * FROM table"}

Based on that I'd expect something like below to work.

{"sql": "SELECT * FROM table WHERE column1 like '%Text%'"}

Or do you mean something else entirely?

MartinB

Thanks. It can work.

 

I was expecting to recreate something like what was shown in the Advanced App Building course at DP19 with Cameron Williams:

 

var dql = {
  columns: [
    {
      exprType: "COLUMN",
      column: "PrimaryInspector"
    },
    {
      exprType: "FUNCTION",
      name: "SUM",
      arguments: [{
        exprType: "COLUMN",
        column: "TotalTime"
      }]
    }
  ],
  groupByColumns: [{
    exprType: "COLUMN",
    column: "PrimaryInspector"
  }],
  where: {
    exprType: "EQUALS",
    rightExpr: {
      exprType: "COLUMN",
      column: "SecondaryInspector"
    },
    leftExpr: {
      exprType: "STRING_VALUE",
      value: "NESTOR MEZA"
    }
  }
}

domo.post('/dql/v1/mwp', dql, { contentType: 'application/json' }).then((data) => {
  var rows = [];
  data.rows.forEach(row =>{
    var obj = {};
    data.columns.forEach((column,i) => {
      obj[column] = row[i];
    });
    rows.push(obj);
  })
  console.table(data);
})

But it's kindda fuzzy so I'll go with traditional SQL for now

guitarhero23

Oh interestings, I feel like their developer documentation is a little lacking, I've read through quite a bit but never came across doing it that way, maybe the documentation exists but I haven't seen it. 

MartinB

Hopefully some of the gurus sees this post and gives us a learning path on this topic.

 

Thanks anyways!