Select data from two tables sqlite and return it to JSON

575 views Asked by At

i have two tables :

1 - Users

IdUser FirstName LastName
1 name1 last1
2 name2 last2

2 - Books

IdBook NameBook IdUser
1 book1 1
2 book2 1
3 book3 2

I am using React-Native and react-native-sqlite-storage

listofBooks() {
    return new Promise(resolve => {
      var books= [];    
      this.initDB()
        .then(db => {
          db.transaction(tx => {
            tx.executeSql('SELECT * from Users INNER JOIN Books ON Users.IdUser= Books.IsUser',
            ).then(([tx, results]) => {
              var len = results.rows.length;
              if (len > 0) {
                for (let i = 0; i < len; i++) {
                  let row = results.rows.item(i);
                  const { IdUser, FirstName, LastName, NameBook } = row;
                  books.push({ IdUser, FirstName, LastName, NameBook  });
                }
                resolve(JSON.stringify(books));
              }
            });
          })
            .then(result => {
              this.closeDatabase(db);
            })
            .catch(err => {
              console.log(err);
            });
        })
        .catch(err => {
          console.log(err);
        });
    });
  }

the function return this json

[
  {
    "IdUser": 1,
    "FirstName": "name1",
    "LastName": "last1",
    "NameBook": "book1",
  },
  {
    "IdUser": 1,
    "FirstName": "name1",
    "LastName": "last1",
    "NameBook": "book2",
  },
  {
    "IdUser": 2,
    "FirstName": "name2",
    "LastName": "last2",
    "NameBook": "book3",
  }
]

but I would like my json be like that

[
  {
    "IdUser": 1,
    "FirstName": "name1",
    "LastName": "last1",
    "NameBook": ["book1", "book2"],
  },
  {
    "IdUser": 2,
    "FirstName": "name2",
    "LastName": "last2",
    "NameBook": "book3",
  }
]

is there a library for (react-native) that allows me to solve this problem easily ? thanks for help.

0

There are 0 answers