Relationships and where clauses?

Question

I have the following tables:

**galleries**

id
location
open_to_public

**pictures**
id
title
published

**gallery_picture**
gallery_id
picture_id

Here's my model for galleries:

class Galleries extends Eloquent {

protected $table = 'galleries';

public function pictures(){

    return $this->belongsToMany('pictures', 'gallery_picture', 'gallery_id', 'picture_id');

}

I'm trying to select a gallery (id, location) and get it's related pictures (id, title).

To start with I've tried this but it seems to return a huge amount of data, im not sure if I'm doing it correct?

$this->mGalleries = new Galleries;
return $this->mGalleries->pictures();

What I want to also do is add some constraints to the query, I understand I can do it like:

public function scopePublic()
{
        return $query->where('open_to_public','=',1);
}

Then: return $this->mGalleries->pictures()->public();

But I have yet to implement this correctly. Could someone point me in the right direction.

I want to get gallery.id, gallery.location and all of that galleries pictures where gallery.open_to_public = 1 and to only get pictures that published = 1.

Also I would like to get all of the galleries that are relevant to the above conditions and not just a single one.

alexrussell alexrussell · Accepted Answer · 2014-02-12T12:17:08

Without the specific error you're receiving it's hard to say exactly what's going on her,e but one thing that jumped out at me from your code samples is that you don't accept the $query as a parameter to your scope method. Try this code instead:

public function scopePublic($query)
{
        return $query->where('open_to_public','=',1);
}

Additionally to that it looks like you're calling your scoped function on the wrong object. From your description you need something like the following set up:

Gallery (model)
- scopePublic($query)
Picture (model)
- scopePublished($query)

Also, you can only get the pictures of a single gallery, not all galleries in one go. So you can't use Gallery::all()->pictures as you might expect but instead you have to do your own collection building.

Your final code will end up being something like the following:

// empty collection to store all our pictures
$pictures = new \Illuminate\Database\Eloquent\Collection;

// get all galleries open to the public
$galleries = Gallery::public()->get();

// for each gallery, get its pictures and add it to the collection
$galleries->each(function ($gallery) use ($pictures) {
    $pictures->merge($gallery->pictures()->published()->get());
});

However, there are a few different ways you could do this. You might find that preloading your relations helps with the database queries too (look up Eloquent's with(), and see if there's a way to pass your scope call in there somehow). Alternatively, if Eloquent's syntax is a little too verbose, you could try using the DB class and joins manually.

Also, sorry if that code is buggy, it's untested but should give you at least a grounding of how to go about solving this.

Relationships and where clauses?

1 Answers