CODEWITHSUNDEEP
mysql
GonnaGetGet
GonnaGetGet

Reputation: 254

Get aggregated query result

I have two tables: tbl_recipe and tbl_directions. Each "direction" has a foreign key, which is recipe's primary key.

At the end of the day, I want to be able to show a json result that looks like the following (other props omitted for brevity): 

[
  {
    "id": 1,
    "name": "Sandwich",
    ...
    "directions": {
      [
        {
          ...
          "recipe_id": 1,
          "directions": "Get bread"
        },
        {
          ...
          "recipe_id": 1,
          "directions": "Spread"
        }
      ]
    }
  },
  {
    "id": 2,
    "name": "Fried Eggs",
    ...
    "directions": {
      [
        {
          ...
          "recipe_id": 2,
          "directions": "Heat pan"
        },
        {
          ...
          "recipe_id": 2,
          "directions": "Crack eggs"
        }
      ]
    }
  }
]

One thing I can think of is doing "select * from tbl_recipe", then loop through results and query for directions of each result. I'm almost positive that there has got to be a better/proper way of doing this.

tbl_recipe

id: 1, name: Sandwich
id: 2, name: Fried eggs

tbl_directions

id:1, recipe_id:1, directions: Get bread
id:2, recipe_id:1, directions: Spread

id:3, recipe_id:2, directions: Heat pan
id:4, recipe_id:2, directions: Break eggs

Thanks!

Upvotes: 0

Views: 43

Answers (1)

Aarish Ramesh
Aarish Ramesh

Reputation: 7023

You got to query the result with a left join and do post processing on the result of duplicate rows like you mentioned.

select * from recipe r left join directions d on r.id = d.recipe_id

After executing the query and fetching the result in the post processing, try to maintain a map of recipe Vs list of directions like Map and add the direction corresponding to a recipe to the list

Also i would as well advise you to take a look at document datastores like MongoDB as your dataset seems very much closer to the use cases of a document store

Hope this helps!

Upvotes: 1

Related Questions