node-sqlite3 select columns from multiple tables

Question

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...

user142162 · Accepted Answer · 2014-05-16 15:20:06Z

1

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' }
...

answered May 16, 2014 at 15:20

user142162

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

node-sqlite3 select columns from multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related