CODEWITHSUNDEEP
javascriptsqldatabase
HeroJo
HeroJo

Reputation: 53

Left join with lowdb

I have two "tables"/arrays, user and order, in my JSON database. It looks like this :

{
  "user": [
    {
      "id": "1",
      "name": "test"
    },
    {
      "id": "2",
      "name": "test"
    },
    {
      "id": "3",
      "name": "test"
    }
  ],
  "order": [
    {
      "user_id": "1",
      "date_order": "2018-01-01",
      "end_order": "2018-01-05"
    },
    {
      "user_id": "2",
      "date_order": "2018-02-01",
      "end_order": "2018-02-05"
    }
  ]
}

I want to retrieve all the users who haven't an order associated to them. So in this case, only the user #3. Is there a specific function to do a left join ? I read the README but I can't figure out how to "join" two "tables". Thanks !

Upvotes: 5

Views: 942

Answers (2)

juancito
juancito

Reputation: 886

lowdb does not have a join functionality at this moment. user and order are treated as databases rather than tables.

You may create your orders inside your user db if you would like to make queries with lowdb syntax.

In the case you continue using the data structure of your question, you could do something like this:

const userIds = db('order').map('user_id');
const users = db('user').filter(user => !userIds.includes(user.id));

Note that in this case, you would be querying all your userIds first. This could lead to a performance issue if that number is big.

Upvotes: 1

Maheer Ali
Maheer Ali

Reputation: 36564

You can use filter() on user array and use find() on order array to check if there is any object with user_id equal to id of obj in user array.

const obj = { "user": [ { "id": "1", "name": "test" }, { "id": "2", "name": "test" }, { "id": "3", "name": "test" } ], "order": [ { "user_id": "1", "date_order": "2018-01-01", "end_order": "2018-01-05" }, { "user_id": "2", "date_order": "2018-02-01", "end_order": "2018-02-05" } ] }

const res = obj.user.filter(x => !obj.order.find(a => a.user_id === x.id));
console.log(res);

Upvotes: 2

Related Questions