半复杂的Eloquent查询

Question

I am working on a eloquent query to compile a newsletter but I have hit a brick wall.

What I'm trying to do is create a UI where the user can select a publication and date. Ideally it would then compile a list of that publication's categories (where stories > 0) and stories belonging to it.

Here are my 3 models:

Story

public function user()
{
    return $this->belongsTo('User', 'user_id');
}

public function publication()
{
        return $this->belongsTo('Workbench\Dailies\Publication', 'publication_id');
}

public function category()
{
        return $this->belongsTo('Workbench\Dailies\Category', 'category_id');
}

Publication

public function story()
{
    return $this->belongsTo('Workbench\Dailies\Story');
}

public function stories()
{
    return $this->hasMany('Workbench\Dailies\Story', 'publication_id');
}

public function category()
{
    return $this->belongsTo('Workbench\Dailies\Category', 'publication_id');
}

Category

public function story()
{
        return $this->belongsTo('Workbench\Dailies\Story', 'category_id');
}

public function publications()
{
        return $this->belongsTo('Workbench\Dailies\Publication', 'publication_id');
}

public function stories()
{
        return $this->hasMany('Workbench\Dailies\Story', 'category_id');
}

Here is how my tables look:

Story

• content
• user_id
• publication_id
• category_id
• publish_date

Publication

• id
• name

Category

• id
• name
• publication_id

Here is what I currently have in my Repository.

public function compileStories($input)
{
    return Category::has('stories', '>', 0)
    ->with('publications')
    ->whereHas('stories', function ($query) use ($input)
        {
            $query->where('publish_date', $input['publish_date']);
            $query->where('publication_id', $input['publication_id']);
        });
}

Am I headed in the right direction here or is there any way to improve the code above? It is not currently functioning as expected.

Answer 1

There are a couple of things I see here that may help straighten you out.

First - Some of the models have strange relationships without knowing more about your whole application. The Story model does not need the publication relationship since it can be retrieved through the category relationship, unless you have need of it otherwise. The Category model does not need both a story and a stories relationship, again, unless there's more to the story I don't know. In your example, you should only need the hasMany relationship. The Publication model only needs the categories relationship.

Now, after some cleanup of the models, let's look at your query. Using the category model to return your results seems completely appropriate for your desired results. You can check for the publication without having to dive into your stories, though. I haven't tested it, but you may not need the use $input line since $input is in the larger scope. You're also missing a conditional check in your where statments in the whereHas clause. The query should be able to be simplified as follows:

Category::where('publication_id', '=', $input['publication_id'])
    ->whereHas('stories', function($query)
    {
        $query->where('publish_date', '=', $input['publish_date']);
    })
    ->get()