Is there any way using complex model scopes with hasManyDeepFromRelation?

[rela589n]

I've tried to use hasManyDeepFromRelation in conjunction with model scopes. But combining of them is not working in the right way.

This is what my relations looks like:

• Test has many TestComposite belongs to Test (many to many on self model - one test may include many other tests)
• Test has many Question (nativeQuestions)

What I need is to write Test::allQuestions() method. It looks like this:

public function allQuestions()
{
        return $this->hasManyDeepFromRelations($this->testComposites(), (new TestComposite)->questions());
}

To get all TestComposite instances I use method:

public function testComposites()
{
    return $this->hasMany(TestComposite::class, 'id_test');
}

TestComposite::questions is used to get all questions of the test from right side:

public function questions()
{
    return $this->hasManyDeepFromRelations(
        $this->includeTest(),
        (new Test)->nativeQuestions()
    )->random($this->questions_quantity);
}

Get the Test from the right side (TestComposite::includeTest):

public function includeTest()
{
    return $this->belongsTo(Test::class, 'id_include_test');
}

And finally scope to get N random questions (Question::scopeRandom):

public function scopeRandom($query, $limit)
{
    return $query->inRandomOrder()->limit($limit);
}

So, when I call TestComposite::questions everything works just fine, and generated sql is right:

select `questions`.*, `tests`.`id` as `laravel_through_key` from `questions` inner join `tests` on 
`tests`.`id` = `questions`.`test_id` where `tests`.`deleted_at` is null and `tests`.`id` = 11 and 
`questions`.`deleted_at` is null order by RAND() limit 2

But, as soon as I want call Test::allQuestions it gives me the unexpected sql shown below (I expected the unions of above sql):

select `questions`.*, `test_composite`.`id_test` as `laravel_through_key` from `questions` inner join 
`tests` on `tests`.`id` = `questions`.`test_id` inner join `test_composite` on 
`test_composite`.`id_include_test` = `tests`.`id` where `tests`.`deleted_at` is null and 
`test_composite`.`id_test` = 16 and `questions`.`deleted_at` is null

I would appreciate any help. If there is some workaround, please let me know.

[staudenmeir]

I'll look into it.

[staudenmeir]

It's not explicitly stated in the documentation, but hasManyDeepFromRelations() doesn't apply additional constraints from the original relationships.

You'll also need to apply the random scope to the allQuestions relationship.

[rela589n]

It is impossible, because questions_quantity is property of intermediate model...

[staudenmeir]

The scope can't actually work/doesn't make sense for the allQuestions relationship: With questions_quantity being an attribute of an intermediate model, every Test query would have multiple (different) limits.

[rela589n]

The thing is that Test may include questions from another Tests. Let's assume we have Test A (30 native questions), Test B (20 native questions) and Test C (10 native questions). But the teacher want Test C to include 15 native questions from Test B and 10 questions from Test A. The allQuestions should return query that get 10 questions from A, 15 from B and 10 of C. The test_compostite table is intermediate and contain id_test (id of original test), id_include_test (id of test to include) and questions_quantity (limit questions to include). It really would be great if something like hasManyRealDeepFromRelation were possible.

[staudenmeir]

If you provide a database dump with the tables and some sample data, I'll have a look.

[rela589n]

What my tables look like:

Sample database dump: dump

[staudenmeir]

I don't see a way to implement this as a single query, let alone a relationship.

[rela589n]

What about simple unions?

For example:

(select `questions`.*, `tests`.`id` as `laravel_through_key` from `questions` inner join `tests` on `tests`.`id` = `questions`.`test_id` where `tests`.`deleted_at` is null and `tests`.`id` = 2 and `questions`.`deleted_at` is null order by RAND() limit 5)
union
(select `questions`.*, `tests`.`id` as `laravel_through_key` from `questions` inner join `tests` on `tests`.`id` = `questions`.`test_id` where `tests`.`deleted_at` is null and `tests`.`id` = 4 and `questions`.`deleted_at` is null order by RAND() limit 999)

[staudenmeir]

Yes, but you need an additional query to get the test IDs. That's what makes this so complex.

[rela589n]

What workaround would you suggest for this situation? (If I want return builder from my method)

[staudenmeir]

You would need to get all included tests through test_composite and then build the UNION query dynamically with one subquery for the parent test and one subquery for each related test.