CODEWITHSUNDEEP
node.jssqlite
user1761207
user1761207

Reputation:

node-sqlite3 select columns from multiple tables

I'm using node-sqlite3 installed from npm. This question relates to that specific plugin. I am running the following via node:

node select.js

var sqlite3 = require('sqlite3').verbose();
var db = new sqlite3.Database('foods.db');

db.serialize(function() {

    db.each(
"select foods.name, food_types.name from foods, food_types where foods.type_id=food_types.id limit 10;"

        , function(err, row){
        if(err) {
            console.log(err);
        }
        console.log(row);
    });

})
db.close();

this code returns

{ name: 'Bakery' }
{ name: 'Bakery' }
{ name: 'Bakery' }
{ name: 'Bakery' }
{ name: 'Bakery' }
{ name: 'Bakery' }
{ name: 'Bakery' }
{ name: 'Bakery' }
{ name: 'Bakery' }
{ name: 'Bakery' }

However using sqlite3

select foods.name, food_types.name from foods, food_types where foods.type_id=food_types.id limit 10;

returns (with .headers on, .mode column,)

name        name      
----------  ----------
Bagels      Bakery    
Bagels, ra  Bakery    
Bavarian C  Bakery    
Bear Claws  Bakery    
Black and   Bakery    
Bread (wit  Bakery    
Butterfing  Bakery    
Carrot Cak  Bakery    
Chips Ahoy  Bakery    
Chocolate   Bakery

Am I using the node-sqlite3 npm module incorrectly, I would expect to see something like

{ name: 'Bakery', name: 'Bagels' }
{ name: 'Bakery',  name: 'Bagels, ra'}
{ name: 'Bakery', name: 'Bavarian C' }
{ name: 'Bakery', name: 'Bear Claws' }

etc...

Upvotes: 0

Views: 1751

Answers (1)

user142162
user142162

Reputation:

The SQLite module cannot give you back a results object with the same key twice; one value will have to override the other. You can get around this by assigning a different name to one of the columns using as in your SQL:

select foods.name as name_food, food_types.name as name_type from ...

The above will give you a result like the following:

{ name_type: 'Bakery', name_food: 'Bagels' }
{ name_type: 'Bakery', name_food: 'Bagels, ra'}
{ name_type: 'Bakery', name_food: 'Bavarian C' }
{ name_type: 'Bakery', name_food: 'Bear Claws' }
...

Upvotes: 1

Related Questions