Query

Query

Below here is the example how you can use your logic as similar when you created SQL Query with fly-json-odm.

- Basic

• Select + Where

var data = nosql.set(data2)
  .select(['id','address'])
  .where('address','jakarta')
  .exec();
console.log(data);

similar in SQL Query

SELECT id, address 
FROM data2 
WHERE address = 'jakarta';

• Select + Where Between

var data = nosql.set(data1)
  .select(['user_id','name','age'])
  .where('age','>=','10')
  .where('age','<=','30')
  .exec();
console.log(data);

similar in SQL Query

SELECT user_id, name, age 
FROM data1 
WHERE age BETWEEN '10' AND '30';

• Select + Where + And

var data = nosql.set(data2)
  .select(['id','address'])
  .where('address','like','a')
  .where('address','like','ba')
  .exec();
console.log(data);

similar in SQL Query

SELECT id, address 
FROM data2 
WHERE address LIKE '%a%' 
AND address LIKE '%ba%';

• Select + Where + Or

var data = nosql.set(data2)
  .select(['id','address'])
  .begin()
  .where('address','like','u')
  .or()
  .where('address','===','solo')
  .end()
  .exec();
console.log(data);

similar in SQL Query

SELECT id, address 
FROM data2 
WHERE address LIKE '%u%' 
OR address = 'solo';

• Select + Where + Or + Order By

var data = nosql.set(data2)
  .select(['id','address'])
  .begin()
  .where('address','like','u')
  .or()
  .where('address','===','solo')
  .end()
  .orderBy('id',false)
  .exec();
console.log(data);

similar in SQL Query

SELECT id, address 
FROM data2 
WHERE address LIKE '%u%' 
OR address = 'solo' 
ORDER BY 'id' ASC;

• Select + Where + Or + Order By + Limit

var data = nosql.set(data2)
  .select(['id','address'])
  .begin()
  .where('address','like','u')
  .or()
  .where('address','===','solo')
  .end()
  .orderBy('id',false)
  .take(2)
  .exec();
console.log(data);

similar in SQL Query

SELECT id, address 
FROM data2 
WHERE address LIKE '%u%' 
OR address = 'solo' 
ORDER BY 'id' ASC 
LIMIT 2;

• Select + Where + Or + Order By + Limit + Offset

var data = nosql.set(data2)
  .select(['id','address'])
  .begin()
  .where('address','like','u')
  .or()
  .where('address','===','solo')
  .end()
  .orderBy('id',false)
  .skip(1)
  .take(2)
  .exec();
console.log(data);

similar in SQL Query

SELECT id, address 
FROM data2 
WHERE address LIKE '%u%' 
OR address = 'solo' 
ORDER BY 'id' ASC 
LIMIT 1,2;

Limit and Offset mostly use for pagination, but we have more simpler way to make a pagination

var data = nosql.set(data2)
  .select(['id','address'])
  .begin()
  .where('address','like','u')
  .or()
  .where('address','===','solo')
  .end()
  .orderBy('id',false)
  .paginate(1,2)
  .exec();
console.log(data);

---

- Group By or with SUM

At example above we are always use select(), but you are able to query without select() like this

var data = nosql.set(data4)
  .groupBy('brand')
  .orderBy('brand')
  .exec();
console.log(data);

similar in SQL Query

SELECT * FROM data4 
GROUP BY brand 
ORDER BY brand ASC;

• Select + Group By + Sum(stock) + Order By

var data = nosql.set(data4)
  .groupBy('brand',['stock'])
  .orderBy('brand')
  .select(['brand','stock','item_count','average_stock'])
  .exec();
console.log(data);

similar in SQL Query

SELECT brand, sum(stock) AS 'stock', item_count, average_stock 
FROM data4 
GROUP BY brand 
ORDER BY brand ASC;

Note:

• select() method is optional, so you are able to put select() on first or tail before exec().
• item_count and average_stock is added automatically when you are using groupBy with sumField.

---

- GroupDetail

This is for grouping data but the data detail will show as nested. This feature is not exists at mostly relation database engine, so I can not show you how the query is.

var data = nosql.set(data4).groupDetail('brand').exec();
console.log(data);

---

- Join Merge

• Merge two data table

var data = nosql.set(data1).join('profile',data2)
  .merge('user_id','id')
  .exec();
console.log(data);

• Merge multiple data table

var table = nosql.set(data1).join('profile',data2).merge('user_id','id').exec();
var data = nosql.set(table).join('bio',data3).merge('user_id','id').exec();
console.log(data);

---

- Join On

• Join on two data table

var data = nosql.set(data1).join('profile',data2).on('user_id','id').exec();
console.log(data);

• Join on multiple data table

var profile = nosql.set(data1).join('profile',data2).on('user_id','id').exec();
var data = nosql.set(profile).join('bio',data3).on('user_id','id').exec();
console.log(data);

• Join on multiple nested data table

var bio = nosql.set(data2).join('bio',data3).on('id','id').exec();
var data = nosql.set(data1).join('data',bio).on('user_id','id').exec();
console.log(data);

Note:

• JOIN in fly-json-odm will act like LEFT JOIN in SQL Query