conditional relationship / query based on table field

Level 33

Hello @orest

Maybe I'm not understanding correctly, but can't you use a single relationship and switch the return based on the parent_id value?

Something like:

public method recentThread() {

    if ( is_null($this->parent_id) ) {
        return $this->hasOneThrough(
            Thread::class,
            Category::class,
            'parent_id',
            'category_id'
        )->latest('updated_at');
    }

    return $this->hasOne(Thread::class)->latest('updated_at');
}

1 like

Level 13

hi @guybrush_threepwood

I've tried that but the problem is that

$this->parent_id

always returns null for some reason and thus it enters the the if statement

Level 33

Have you tried checking for null in the relationship method?

In your example you check for null in a scope, that has a completely different outcome.

1 like

Level 13

@guybrush_threepwood

I just tried it again and for the parent_categories the recentThread has the proper value but for the children_categories the recentThread is null because as i said it enters the if statement always.

Could you please explain me though why is it different in a relationship and in query scope ? Since in both cases $this refers to the query builder i guess ( or more specifically $this refers to the Categories table if i am not mistaken )

Level 33

When you apply a scope in order to filter your results you're just calling a static method on an empty eloquent model (thus, the $this->parent_id property is always going to be null).

Check out the examples in the documentation: https://laravel.com/docs/7.x/eloquent#query-scopes Notice how they only reference the scope parameters and not model properties (since that doens't make sense).

When you query your models first, and then call the relationship, the model's parent_id property will be set to the correct value since the models will be populated from the database.

Try adding the relationship code and then adding the follow code to your controller and pass the $categories variable to your view:

$categories = Category:all();

And then add to the view:

<ul>
@foreach($categories as $category)
    <li>Category name: {{ $category->name }} | Latest Thread: {{ $category->recentThread->title }}</li>
@endforeach
<ul>

PS: Replace $category->name with your actual column.

1 like

Level 13

@guybrush_threepwood

that works but i didn't consider that approach because i want to eager-load the relationship.

So ultimately i need to do

Category::with('recentThread')->get();

And in this case

$this->parent_id

returns always null.

1 like

Best Answer

Level 33

I understand.

This is how you would achieve that with RAW SQL:

SELECT c.id AS category_id, c.parent_id, name AS category_name,
IF(parent_id IS NOT NULL, t1.id, t2.id) AS thread_id,
IF(parent_id IS NOT NULL, t1.title, t2.title) AS thread_title
FROM categories c
LEFT JOIN threads t1 ON t1.id = (
  SELECT id
  FROM threads
  WHERE category_id=c.id
  ORDER BY updated_at DESC
  LIMIT 1
)
LEFT JOIN threads t2 ON t2.id = (
  SELECT id
  FROM threads
  WHERE category_id IN (
    SELECT id
    FROM categories
    WHERE parent_id = c.id
  )
  ORDER BY updated_at DESC
  LIMIT 1
)
ORDER BY c.id ASC;

Demo: http://sqlfiddle.com/#!9/5839cc/27

No idea how or if it's possible to achieve with the QueryBuilder. Surely not with Eloquent.

Good luck!

Level 13

@guybrush_threepwood thanks a lot!

1 like