Hi,

I'm building an application where you can create your own lessons. A lesson has many slides and they in turn include many elements.

I want to get these from my database in one query. One thing to note is that I have a pivot table called slide_element which contains an id, slide_id and an element_id.

The following query works:

$lesson = \Lesson::where('id', '=', $id)->where('deleted', '=', 0)->with('slide.slideElement')->get();
        return $lesson[0];

This returns one lesson (with $id), all slides and the reference to the element_id (from the slide_element table). However, when I do the following query...:

$lesson = \Lesson::where('id', '=', $id)->where('deleted', '=', 0)->with('slide.slideElement.element')->get();
        return $lesson[0];

...I get the following error:

Illuminate \ Database \ QueryException SQLSTATE[42S22]: Column not found: 1054 Unknown column 'elements.slide_element_id' in 'where clause' (SQL: select * from elements where elements.slide_element_id in (1, 2, 3, 4))

Which makes sense, because it's trying to match the wrong IDs. I want to match the 'element_id' from 'slide_element' with the 'id' from 'elements'.

Can I somehow specify in my model(s) how and where to match IDs?

Thanks in advance!

Here are my Models:

class Lesson extends Eloquent {
    protected $fillable = ['module_id', 'title', 'description', 'deleted'];


    public function module()
    {
        return $this->belongsTo('Module');
    }

    public function slide()
    {
        return $this->hasMany('Slide');

    }

}

class Slide extends Eloquent {
    protected $fillable = ['lesson_id', 'deleted'];


    public function lesson()
    {
        return $this->belongsTo('Lesson');
    }

    public function element()
    {
        return $this->hasMany('Element');
    }

    public function slideElement()
    {
        return $this->hasMany('SlideElement');
    }

}

class SlideElement extends Eloquent {
    protected $fillable = ['slide_id', 'element_id'];
    protected $table = 'slide_element';
//    protected $primarykey = 'element_id';

    public function slide()
    {
        return $this->belongsTo('Slide');
    }

    public function element()
    {
        return $this->hasMany('Element');
    }

}

class Element extends Eloquent {
    protected $fillable = ['order', 'video_bool', 'textimg_bool', 'question_bool', 'video_ref', 'text_img', 'question_ref'];


    public function slide()
    {
        return $this->belongsTo('Slide');
    }

    public function slideElement()
    {
        return $this->belongsTo('SlideElement');
    }

}

[SOLVED] Had some great help over at the #laravel chat. It had to do with my relationships in my models.

Changed:

SlideElement -> element() to belongsTo('element')

That did the trick and my query works!