General approach to fetching related records?

[judgej]

This may be a problem with my understanding of eloquent more than this package, but I am looking for a way to fetch related records simply and elegantly.

Take this scenario: there are Articles which can be categorised. The Category is an hierarchical structure. Each category has a many-to-many relationship with the articles.

So I have a category "Colours". Under this are "Red", "Green" and "Blue". I want to select all Articles which have a "Colours" category - that is the "Colour" category or any category below it.

So I can get the category tree like this:

// Return all colour categories: Colour, Red, Green, Blue
Category::where('slug', 'colours')->descendantsAndSelf()->get();

Now I want to fetch all articles that have any of those categories. This is what I am getting stuck. An eager fetch will get all the articles:

// Return all colour categories: Colour, Red, Green, Blue
Category::where('slug', 'colours')->descendantsAndSelf()->with('articles')->get();

But this is now a collection of collections. For each Category returned, you need to look at $category->articles then loop merge those into the articles from each other group. This manipulation in PHP feels very wrong, when a database query should be able to pull this all back in one go.

What am I missing? Would it be better to just go direct to the database and write my query? Effectively, I need a unique list of articles that have at least one of a number of categories. It feels like a where in subselect but sub-selects are very inefficient in MySQL so I would like to avoid that.

Any tips?

[judgej]

I've tried a map, which replaces each category with a collection of articles. This is then a raw collection of a collection of articles - which is a step forward - but they still then need to be merged (and uniqued).

Category::where('slug', 'colours')
    ->descendantsAndSelf()
    ->with('articles')
    ->get()
    ->map(function($value) {return $value->articles;});

[judgej]

Hmm, this works for me, but feels a little cludgy (the category IDs are expanded to an in(...) statement. That does get around the inefficient sub-select problem, but if the number of categories are too great then the query string could be pretty long, maybe too long.

            // Get the category I want to fetch articles under.
            $base_category = Category::find($the_base_of_the_sub_tree);

            // The article_category table is the pivot table between articles and categories
            $result = Article::join('article_category', function($join) use($base_category) {
                $join->on('articles.id', '=', 'article_category.article_id')
                    ->whereIn(
                        'article_category.category_id',
                        // This expands to an array of category IDs.
                        $base_category->descendantsAndSelf()->lists('id')->toArray()
                    );
            })
                // Use groupBy to collect by unique articles, otherwise articles matching
                // multiple categories will be duplicated.
                ->groupBy('articles.id')
                // Get all the matching articles.
                ->get();

So yeah, dropped back to query builder and used descendantsAndSelf() to get a list of IDs to plug into the query. It should probably more strictly be getDescendantsAndSelf() but it looks like the lists() method on the query is doing an implicit get(), so either method has the same result.

Still feel that I'm missing something though.