'$from' rejects multiple source

[hidori]

Hi, I'm new to json-sql-builder2 :-)

I'm trying to write the flowing query by JSON

select
	*
from
	label, label_article_rel
where
	label.label_id = label_article_rel.label_id

I wrote the following JSON

const SqlBuilder = require('json-sql-builder2');
const builder= new SqlBuilder('MySQL');
const query = builder.$select({
    $from: ['label', 'label_article_rel'],
    $where: {
        'label.label_id': 'label_article_rel.label_id',
    }});

It's fails. the error is:

Using Helper '$from' must be type of 'Object, String', but got Type 'Array' with value '["label","label_article_rel"]'

[planetarydev]

please have a look at the doc for the join helper https://github.com/planetarydev/json-sql-builder2/tree/master/sql/helpers/queries/join.

You can do something like this:

let myQuery = builder.$select({
    $from: 'label',
    $join: {
         lar: {
             $inner: 'label_article_rel',
             $on: {
                    'label.label_id': { $eq: '~~lar.label_id' }  // use ~~ to told that lar.label_id is an identifier
             }
        }
});

This will build the following SQL query:

SELECT
    *
FROM
    label
INNER JOIN label_article_rel AS lar
    ON label.label_id = lar.label_id

Another aproach is a cross-tabel-query https://github.com/planetarydev/json-sql-builder2/tree/master/sql/helpers/queries/from#further-examples

let myQuery = builder.$select({
    $from: {
        label: 'l',
        label_articel_rel: 'lar'
    },
    $where: {
        'l.label_id': { $eq: '~~lar.label_id' }
    }
});

// or use from as object with true or 1
let myQuery = builder.$select({
    $from: {
        label: true, // 1
        label_articel_rel: true // 1
    },
    $where: {
        'label.label_id': { $eq: '~~label_articel_rel.label_id' }
    }
});

SELECT
    *
FROM
    label AS l,
    label_article_rel AS lar
WHERE
    l.label_id = lar.label_id

You could find may examples for each Helper and Operator. Browse throu the /sql directory of the repository.

I hope this will help.

[hidori]

Thank you for your advice!

I'm tring to re-write my query. The latest query is:

const SqlBuilder = require('json-sql-builder2');
const builder= new SqlBuilder('MySQL');
const query = builder.$select({
    $columns: {
        'label.*': 1,
    },
    $from: {
        'label' : true,
        'label_article_rel': true,
    },
    $where: {
        'label.label_id': '~~label_article_rel.label_id',
        'label_article_rel.article_id': 2,
    }});

The result is:

{
  "sql": "SELECT label.`*` FROM label, label_article_rel WHERE label.label_id = label_article_rel.label_id AND label_article_rel.article_id = ?",
  "values": [
    2
  ]
}

Close! So close! And I have one more question.
How can I write following SQL in JSON?

SELECT label.* 

not

SELECT label.`*` 

Regards.

[planetarydev]

You could remove the $columns Helper from your JSON-Query. That's it. SQLBuilder will create a columns-helper with '*' if it not exists.

const SqlBuilder = require('json-sql-builder2');
const builder= new SqlBuilder('MySQL');
const query = builder.$select({
    $from: {
        'label' : true,
        'label_article_rel': true,
    },
    $where: {
        'label.label_id': '~~label_article_rel.label_id',
        'label_article_rel.article_id': 2,
    }
});

[planetarydev]

oh, sorry. i saw you need the results only from table label... I will have a look.

[planetarydev]

you could force SQLBuilder to leave the columns untouched as defined as inline SQL using __: as prefix like:

const SqlBuilder = require('json-sql-builder2');
const builder= new SqlBuilder('MySQL');
const query = builder.$select({
    $columns: {
        '__:label.*': 1,
    },
    $from: {
        'label' : true,
        'label_article_rel': true,
    },
    $where: {
        'label.label_id': '~~label_article_rel.label_id',
        'label_article_rel.article_id': 2,
    }
});

Your attempt should always be a normal use-case, so I have checked the quote-function and extend your case. So now all identifiers * will be untouched from quoting. I've bumpt the version to 1.0.19 please update so you could write your query as previously expected.

[hidori]

Finally, We reached the goal!

I've tried v1.0.19

const SqlBuilder = require('json-sql-builder2');
const builder = new SqlBuilder('MySQL');
const query = builder.$select({
    $columns: {
        'label.*': true,
    },
    $from: {
        'label' : true,
        'label_article_rel': true,
    },
    $where: {
        'label.label_id': '~~label_article_rel.label_id',
        'label_article_rel.article_id': 2,
    }});

The result is:

{
  "sql": "SELECT label.* FROM label, label_article_rel WHERE label.label_id = label_article_rel.label_id AND label_article_rel.article_id = ?",
  "values": [
    2
  ]
}

Yes! It's a result I had to want.
Thank you for youw kindly adivices. I will continue to use this product the future ;-)