Retrieving the data from MySql, Laravel

Question

I have two tables one is for Questions, And one is for Options. All the questions will be saved on Questions Table.

Question table:

id | questions                                       | created_on 
1  | Who is the world champianship on 2016 World cup?| 13-09-2016
2  | Who won the FiFA world cup?                     | 13-09-2019

Options table:

id | question_id | option_text
1  | 1           | Pakistan
2  | 1           | India
3  | 1           | Australia
4  | 1           | Srilanka
5  | 2           | Canada
6  | 2           | Russia
7  | 2           | Germany
8  | 2           | France

This is the table:

I expect the json response in following format:

[
{
    "question": "Who is the world champianship on 2016 World cup",
    "created_on": "2019-09-13",
    "question_id": 1,
    "options":[ 
    {"option_text": "Pakistan"},
    {"option_text": "India"},
    {"option_text": "Australia"},
    {"option_text": "Srilanka"}
    ]
},
{
    "question": "Who won the FiFA world cup?",
    "created_on": "2019-09-13",
    "question_id": 2,
    "options":[ 
    {"option_text": "Canada"},
    {"option_text": "Russia"},
    {"option_text": "Germany"},
    {"option_text": "France"}
    ]
}
]

But in my following SQL query

    $records = DB::table('tbl_questions')
        ->where('created_on', '=', date('Y-m-d'))
        ->leftJoin('tbl_options', 'tbl_options.question_id', '=', 'tbl_questions.id')->get();

Returns following response:

[
 {
     "question": "Who is the world champianship on 2016 World cup",
    "created_on": "2019-09-13",
    "question_id": 1,
     "option_text": "Pakistan"

 },
 {
   "question": "Who is the world champianship on 2016 World cup",
    "created_on": "2019-09-13",
    "question_id": 1,
    "option_text": "India"
 },
 {
 "question": "Who is the world champianship on 2016 World cup",
    "created_on": "2019-09-13",
    "question_id": 1,       
    "option_text": "Australia"
 },
 {
    "question": "Who is the world champianship on 2016 World cup",
    "created_on": "2019-09-13",
    "question_id": 1,
    "option_text": "Srilanka"
  },
  {
   "question": "Who won the FiFA world cup?",
    "created_on": "2019-09-13",
    "question_id": 2,
    "option_text": "Canada",
  },
  {
    "question": "Who won the FiFA world cup?",
    "created_on": "2019-09-13",
    "question_id": 2,
    "option_text": "Russia",
    },
    {
    "question": "Who won the FiFA world cup?",
    "created_on": "2019-09-13",
    "question_id": 2,
    "option_text": "Germany",
    },
    {
    "question": "Who won the FiFA world cup?",
    "created_on": "2019-09-13",
    "question_id": 2,
    "option_text": "France"
    }
 ]

Anybody can help me for the expected json response. Helps will be appreciated.

Answer 1

Please do try using Eloquent Relationships instead. Refer Documentation Link Here

Using this we can define certain relationships among models, there are a different types therein. In your case its one to many relation, as for one question there are many options First you will need to define the relation and in the model as shown below and In the controller you will get the result using following -

$result = Questions::with('options')->get()

This will be your Question Model:

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Questions extends Model
{

    public function options()
    {
        return $this->hasMany('App\Model\Options');
    }
}

Answer 2

Define your model and add options as a relationship.

Class Question extends Model
{
    public function Options()
    {
        return $this->hasMany(Option::class);
    }
}

In your controller do this.

public function questions()
{
    return Question::with('options')->where('created_on', '=', date('Y-m-d')->get();
}

Answer 3

Create one hasMany relationship in Question model. like this

public function options() { return $this->hasMany('App\Option'); }

and use with function in your query to retrieve option like this

$records = DB::table('tbl_questions')
    ->where('created_on', '=', date('Y-m-d'))
    ->with('options')
    ->get();