nested results from combo join / union

[Michael]

Anyone know of a way to get nested results with a union? Here's an exmple query:

In the long run what I'm truly wanting is a bit complicated ...

SELECT * FROM (

    SELECT appointmentDatetime, accountOwner, bankerId FROM `appointments` WHERE accountOwner = '5'

) AS `appointment`

LEFT JOIN `users` AS `accountOwner` ON (accountOwner = userId)

LEFT JOIN `users` AS `banker` ON (bankerId = userId)

UNION

SELECT * FROM (

    SELECT appointmentDate, accountOwner, bankerId FROM `appointments` WHERE bankerId = '5'

) AS `appointment`

 LEFT JOIN `users` AS `accountOwner` ON (accountOwner = userId)

LEFT JOIN `users` AS `banker` ON (bankerId = userId)

Here's what I want back:

[

  {

    banker: {

      firstName: "John",

      lastName: "Smith",

      userId: "7"

    },

    accountOwner: {

      firstName: "Marty",

      lastName: "Sullivan",

      userId: "5"

    },

    appointment: {

      appointmentDatetime: "2014-09-01 12:00:00",

      accountOwner: '5',

      bankerId = '7'

    }

  },

  {

    accountOwner: {

      firstName: "Fred",

      lastName: "Dunlop",

      userId: "11"

    },

    banker: {

      firstName: "Marty",

      lastName: "Sullivan",

      userId: "5"

    },

    appointment: {

      appointmentDatetime: "2014-08-13 15:00:00",

      accountOwner: '11',

      bankerId = '5'

    }

  }

]

[Ryan Lee]

Have you tried the nestTables option?

https://github.com/felixge/node-mysql#joins-with-overlapping-column-names

Ryan

--
You received this message because you are subscribed to the Google Groups "node-mysql" group.
To unsubscribe from this group and stop receiving emails from it, send an email to node-mysql+...@googlegroups.com.
To post to this group, send email to node-...@googlegroups.com.
Visit this group at http://groups.google.com/group/node-mysql.
For more options, visit https://groups.google.com/d/optout.

[Michael]

Yep, nested is on. Sorry, I didn't expand on how it doesn't work.

If I run the query that way, I get back:

[

  {

    "": {

      firstName: "John",

      lastName: "Smith",

      userId: "7",

      appointmentDatetime: "2014-09-01 12:00:00",

      accountOwner: '5',

      bankerId = '7'

    }

  },

  {

    "": {

      firstName: "Fred",

      lastName: "Dunlop",

      userId: "11",

      appointmentDatetime: "2014-08-13 15:00:00",

      accountOwner: '11',

      bankerId = '5'

    }

  }

]

So, it's flattening everything, but "nesting" it in an anonymous table - thus the empty key - make sense?

I can simply wrap the query with a SELECT * FROM (...) AS `appointments`, but it returns the same flattened response, but instead of an empty key you have "appointments". So, it looks like the union flattens it. That's what I'm trying to find my way around.