1

I'm trying to return a single column from a with relationship, using Laravel 5.1. I want to get all Categories, each with an array of related question IDs (not the full question objects).

$categories = Category::with(['questions'])->get(); gets me all categories with an array of question objects. This is not what I want. I only want question IDs.

Following this post, I've added a nested query to select:

    $categories = Category::with(['questions'=>function($query){
        $query->select('id');
    }])->get();

This returns all categories, as expected, but all "questions" arrays belonging to each category is empty.

I've also tried editing my model:

public function questions()
{
    return $this->hasMany('App\Question')->select('id');
}

Why is this?

Models:

Question Model:

public function category()
{
    return $this->belongsTo('App\Category');
}

Category Model:

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

Likewise, I'm using mysql query logger to log the actual sql.

$categories = Category::with(['questions'])->get(); gives me:

2016-04-14T04:54:04.777132Z  181 Prepare    select * from `categories`
2016-04-14T04:54:04.777230Z  181 Execute    select * from `categories`
2016-04-14T04:54:04.777566Z  181 Close stmt 
2016-04-14T04:54:04.780113Z  181 Prepare    select * from `questions` where `questions`.`category_id` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2016-04-14T04:54:04.780301Z  181 Execute    select * from `questions` where `questions`.`category_id` in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20')

And, the nested $query one tells me I'm selecting ID, as expected:

2016-04-14T04:54:28.762529Z  182 Prepare    select * from `categories`
2016-04-14T04:54:28.762663Z  182 Execute    select * from `categories`
2016-04-14T04:54:28.762997Z  182 Close stmt 
2016-04-14T04:54:28.765550Z  182 Prepare    select `id` from `questions` where `questions`.`category_id` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
2016-04-14T04:54:28.765708Z  182 Execute    select `id` from `questions` where `questions`.`category_id` in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '10', '11', '12', '13', '14', '15', '16', '17', '18', '19', '20')

Dying these out, btw, dd($categories); gives the same datastructures, but one has an empty collection of questions

Community
  • 1
  • 1
user3871
  • 12,432
  • 33
  • 128
  • 268

1 Answers1

1

Ah, the query logger helped...

To use the nested $query with select, you need to include the field it is using to join on. In this case, the SQL is joining on category_id.

So it should look like:

    $categories = Category::with(['questions'=>function($query){
        $query->select('id', 'category_id');
    }])->get();

Gets me (it would be cool to collect the question IDs into a neat Array though):

enter image description here

user3871
  • 12,432
  • 33
  • 128
  • 268