Node - Knex to return array of objects as a result of joined tables

Question

I am using knexjs and node and running postgres db with this tables: menu and menuItem having one-to-many relationship. I found a solution here knex: what is the appropriate way to create an array from results? however this returns an array of strings. What i need is to return an array of objects and an empty array if null that looks exactly as the example below:

[
  {
    id: 123,
    name: 'Sunday Menu',
    items: []
  }, 
  {
    id: 456,
    name: 'Monday Menu',
    items: [
      { 
         id: 987,
         name: 'Fried Chicken',
         pcs: 69
      },
      { 
         id: 876,
         name: 'Egg Soup',
         pcs: 50
      },
    ]
  }
]

My menu and menuItem table schema looks similar to this:

menu_table: {
  id,
  name,
  timestamps
}

menuItem_table: {
  id,
  menu_id,
  name,
  pcs,
  timestamps
}

Currently, my code is like this:

knex('menu').leftJoin('menuitem', 'menu.id', 'menuitem.menu_id')
.select(['menu.id as menuID', knex.raw('ARRAY_AGG(menuitem.name) as items')])
.groupBy('menu.id')

And here's the result:

[
  {
     "menuID": "20091fff-ca8b-42d6-9a57-9f6e1922d0fa",
     "items": [
         null
     ]
  },
  {
     "menuID": "2ddad4fa-7293-46c5-878f-cb2881be3107",
     "items": [
         "Fried Chicken",
         "Egg Soup",
         "Vegetable Dish"
     ]
  }
]

UPDATE: I found out how to do it using raw query how ever i can't translate it using knex. Here's my code:

SELECT menu.*, COALESCE(menuitem.items, '[]') AS items FROM menu LEFT JOIN LATERAL ( 
   SELECT json_agg(menuitem.*) AS items FROM menuitem WHERE menu.id = menuitem.menu_id 
) menuitem ON true

I currently tried this: `knex('menu').leftJoin('menuitem', 'menu.id', 'menuitem.menu_id').select(['menu.id as menuID', knex.raw('ARRAY_AGG(menuitem.name) as items')]).groupBy('menu.id')` and returning `items: ['Fried Chicken', 'Egg Soup']` — DevWannabe, Nov 03 '20 at 04:17
Add that to your question. It's one of the things that can make your question valid and not closed as a do-my-homework type question — slebetman, Nov 03 '20 at 04:17

DevWannabe · Answer 1 · 2020-11-03T09:25:16.687

0

I finally found a solution to my question. Since I was able to get my desired result thru raw query, i just translate it to knex. My final code is this:

const coalesce = knex.raw(`coalesce(menuitem.items, '[]') as items`)
const sub = knex('menuitem').select(knex.raw('json_agg(menuitem.*) as items')).whereRaw('menu.id = menuitem.menu_id')

return knex('menu').select(['menu.*', coalesce]).joinRaw('left join lateral ? menuitem on true', sub)

I'm gonna go with this code in the mean time until someone would give me the most accurate answer.

edited Nov 03 '20 at 09:25

answered Nov 03 '20 at 07:19

DevWannabe

87
1
7

One down side of that is that the rows are converted to json types before they are returned from database. So for example custom de serializations done by pg-types will not work. – Mikael Lepistö Nov 04 '20 at 14:51
Will take note of that. Thanks! – DevWannabe Nov 05 '20 at 03:29

score 0 · Answer 2 · answered Mar 10 '21 at 09:28

This work, im add attachments, ty DevWannabe :

const results = await knex.column({
  'id': 'str.id',
  'projectName': 'str.project_name',
})
.from('venturedoor.startups as str')
.leftJoin('venturedoor.attachments as att', 'str.id', 'att.startup_id')
// join array attachments
.select(['str.id', knex.raw('ARRAY_AGG(att.*) as attachments')])
.groupBy('str.id')

score 0 · Answer 3 · answered Dec 27 '21 at 05:37

0

knex.raw('coalesce(usr.userName,usr.email ,seat.username) as username')

We can use like that measn. If userName is empty so will call email if email also then will call name from seats.

answered Dec 27 '21 at 05:37

aadilraza339

103
3

Node - Knex to return array of objects as a result of joined tables

3 Answers3